-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Roger Baklund wrote:
[snip]
> A connection pooling implementation without this isolation, where multiple
> users can share the same database connection at the "same time" is either
> broken or intended for a special purpose (some non-multi-statement
purpose).
> imnsho.
Correct, especially since transactions are scoped to connections in JDBC.
If you're not using EJBs (which will automagically return you the _same_
connection when you use a connection pool as long as you're not
suspending transactional contexts (ie. TX_REQUIRES_NEW or
TX_NOT_SUPPORTED), or at least a connection which is working in the
'correct' transaction, which in any case is one and the same with MySQL,
because it doesn't support XA, yet), then the 'correct' way to use a
connection pool from a web application is to do the following:
1. Start processing an HTTP Request (via doPost(), doGet() or service()
in servlet, or directly via a JSP (blech, hopefully you're encapsulating
your data access in some middle layer DAOs / Beans, etc))
2. Get a connection from the pool.
3. Do all of your database processing with that connection (optionally)
bracketting all of that with a transaction via your standard
connection.setAutoCommit(false), do work ... connection.commit() or
connection.rollback(). If multiple servlets/filters, etc. need access to
the connection, then stick it in a request-level variable via
ServletRequest.setAttribute() or a thread local.
4. Before finishing processing of the request, return the connection to
the pool, by calling close() on it.
Ideally, you build your application so step 3 is as short as possible,
thus allowing a few connections to service as many _web_ clients as
possible.
For all intents and purposes, the servlet/JSP will have it's _own_
connection to the database...It just only uses it for a short time, and
then when not using it, returns it to the pool.
Temporary tables are somewhat problematic in this model...You can either
make sure they are cleaned up via your application logic before closing
the connection, or you can use Connector/J 3.1.x, as the implementation
of javax.sql.ConnectionPoolDataSource in there will issue a
mysql_change_user() when you call getConnection(username, password),
which effectively 'resets' the server state of the connection, dropping
temp tables, resetting session-level variables, etc. (this allows you
with some application servers to actually have authenticated users that
differ per request, but still keep a pooled connection, as well).
Other than the temporary tables and session-level variables issues, it
is easiest to think of standard JDBC connection pools as giving you a
new connection every time you call .getConnection(). If you want to use
transactions, then all work has to occur on the same connection.
Regards,
-Mark
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 557 2388
www.mysql.com
Want to swim with the dolphins? (April 14-16, 2004)
http://www.mysql.com/uc2004/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQE//3zLtvXNTca6JD8RApIgAJ4xM+SSaDiehJzb16rh0dQfcn8b7QCgqXzK
RpXHCp1O9nWqt5P5XxXz+Pk=
=4Pfz
-----END PGP SIGNATURE-----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
