These are good questions. We should put them in a FAQ. Anybody got a good location for a MySQL JDBC FAQ???

A key issue here is that Servlets are multithreaded, and may be working on multiple requests simultaneously. I believe there is not enough locking in the current drivers to guarantee their integrity if you share a JDBC Connection across threads. As a practical matter you probably would not wish to share a JDBC Connection across requests (thus across threads). Both of the modern drivers, MM and TWZ, map each JDBC Connection to a MySQL connection, and MySQL only handles one SQL request at a time on one connection. So to get prompt response to each HTTP request, you will want to assign each active request a Connection.

You will want more than one Connection. Integrity of the database itself though, is probably not of concern since MySQL does contain adequate internal locking.

Yes, your concern is right on target.

What people do, and what our site does, is to give each HTTP request its own JDBC Connection, and to allocate JDBC connections from a "pool" and return them to the pool. You should be able to benefit from a package such as the ConnectionPool package available from www.bitmechanic.com.

With pooling, the idea is to make some pretty reliable mechanism ensure that each HTTP request and thus each thread has its own Connection, yet Connections get reused so you don't leak connections and file descriptors. You can do this with application design discipline (and Java "catch" or "finally" clauses!) or a thread pooling package.

If you either use GSP (also from bitmechanic.com) or if your Servlet engine (Java Web Server) supports the 2.1 version of the Servlet API's, you might associate a JDBC Connection with each HTTP request, by making it an attribute of the Request with "setAttribute", and pass the connection around as you pass the Request around. This may make it easier to manage Connections.

Note that JDBC allows each Statement to have one ResultSet open, and each Connection can have multiple open Statements. Both the MM and TWZ drivers multiplex the MySQL connection to accomplish this. (Remember MySQL allows only one open result set on a connection at one time.) MM always greedily reads from the MySQL connection to always free it up for another request ASAP. TWZ doesn't get greedy unless it receives another request. A second request on the connection prods it to grab the remaining data from MySQL and buffer it internally. Both seem to work well in practice.