> I'd like auto commit on so any updates are automatically committed. > So. Am I using this in the wrong way?
Yes. You should not use autocommit mode. You'll get into nasty surprises as soon as you have dependencies between data rows. You'll get into all kinds of race conditions, and these are the ultimate in nastiness to debug even if networking delays aren't involved. Long version: You'll have to pick your evil. With autocommit, you will get rare, below-the-radar problems. Once you become aware of them, you'll have massive problems reproducing and fixing them. Without autocommit, you will get frequent but highly visible problems, easily diagnosed and fixed (but embarrassing if they manage to ship). The usual solution is to delegate transaction management to a container. That way, you don't risk race conditions and you don't risk forgetting to code a commit. The downside here is that choosing and configuring a container isn't trivial. It's worth it though; containerless transactions mean a lot of try-catch-finally code, resource leaks, and general unhappiness. Oh, and there's another catch, reported on http://stackoverflow.com/questions/4453782/why-set-autocommit-to-true: > Since the JDBC 3 spec, connections in "auto-commit" mode can't > have more than one Statement open. Thus, nested UPDATEs or even > SELECT within an outer SELECT loop will cause problems. I can't vouch for the correctness of this bit of information though.
