File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes setAutoCommit(false), then rollback causes database locked error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "setAutoCommit(false), then rollback causes database locked error" Watch "setAutoCommit(false), then rollback causes database locked error" New topic

setAutoCommit(false), then rollback causes database locked error

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 634
I need to rollback a sales transaction if the user cancels on creating
a customer. (The sales is created before the customer is created)
The code is big, and I can't post it here without big adjustment,
Please bear with me.

The logic is
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

We don't see where do you call the insertCustomer and addOrderDetails from. Aren't they by chance called from a background thread? It might be a threading issue then.

In any case, the insertCustomer contains a conn.commit() call. So the transaction is committed and cannot be rolled back later. What is worse, the commit is in a finally block, meaning it is called even when an error occurs - you most certainly don't want that. Leave the commit as the last statement in the try block.

Furthermore, I'd suggest a different approach. Collect all information from the user and keep them in some object(s). Let the user confirm the data before even starting to write them to the database. If he does confirm, write the data and commit it in one go. Don't let the user cancel the operation once it begun. Your operation (inserting a few records) seems to be so short that it doesn't make sense to allow cancelling it.

If you really need to allow user cancelling a long DB operation, you need to perform the operation in a background thread and signal to the thread (eg. by using the Thread.interrupt() method) to stop its work. The background thread would then perform all necessary cleanup, including the rollback. Bear in mind, though, that databases are optimized for commits, not rollbacks, and rolling back a large transaction tan take several times the time it took to create the transaction in the first place.
I agree. Here's the link:
subject: setAutoCommit(false), then rollback causes database locked error
It's not a secret anymore!