-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Bill,
(I've decided to stop cross-posting to commons-user. If you want to forward come of my comments, you are welcome to do so). On 10/15/2009 5:07 PM, Bill Davidson wrote: > I'm a little confused about auto-commit. I don't want my transactions > auto-committed. Sometimes there's a need for a rollback of an entire > transaction. To my thinking, auto-commit implies that you don't have > true transactions. You can always issue the equivalent of a BEGIN by calling Connection.setAutoCommit(false) and then, later, Connection.commit() or Connection.rollback(). The DBCP pool's default mode (which matches the JDBC spec) is to keep all connections in autoCommit=true mode. When connections are returned to the pool, Connection.setAutoCommit(true) is called, which is documented to commit any in-progress transaction (which is why you'd better not be sloppy with your rollback code: calling Connection.close() in a pooled environment does /not/ roll-back your transaction: it commits it!!). > The transactions in the app are a lot more complex, and a transaction > can hold onto a connection for quite a while as a user is editing things. Uh, oh. Are you doing something like this: BEGIN SELECT UPDATE (wait for user to make a selection on a screen and re-submit a form) UPDATE COMMIT ?? If you're doing that, then you're probably making a big mistake. Are you storing connections in sessions or anything like that? Yuk. > The queries themselves have the same try-catch-finally situation but > there can be quite a few different calls on that connection for one > transaction. Not a problem. >>But what is the likelihood of "SELECT * FROM foo WHERE foo.id=?" >>appearing in a transaction? > > Very low. The scenario I was describing was: > > 1. Thread 1 returns a connection with an uncommitted transaction to the > pool. Mistake: see above. > 2. Thread 2 gets the same connection from the pool and does a query on it Another mistake: you can't be sure which connection gets handed-out b the pool. Or, maybe you're just describing a way to get yourself into the situation you're observing. > 3. The query from Thread 2, unrelated Thread 1, ends up in cursor limbo > because of the uncommitted transaction on the connection. Unless you have set autoCommit="false" in your DBCP configuration, this shouldn't happen. > I don't know that this is happening in the app, and it's going to be very > difficult to find it if it is. I don't even know that this scenario > could cause this cursor limbo. It's just a hypothesis which is hard to test. Definitely. A good DBA ought to be able to instrument the server and watch a connection create these cursors. If that's possible, then have your DBA do just that while you make a connection, maybe using a special user id so it's easier to track. Just set up a test bed for your webapp and then simply use it yourself until you catch a cursor being "lost", then try to back-track and figure out where it came from, and why it won't go away. I'm still interested in what happens if you disable prepared statement pooling. - -chris -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkrYjGEACgkQ9CaO5/Lv0PBqXQCgnPqv8/S1Z4n4jJ72TtwRdSuW J3MAn341zxwTxcm0w+mStlmem4ahdGLN =ulWR -----END PGP SIGNATURE----- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
