• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

setAutoCommit(false), then rollback causes database locked error

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic