0

I came across MySQLTransactionRollbackException exception today and the error message is Deadlock found when trying to get lock; try restarting transaction. I analysed the code and found that there are many update/insert statements in the same transaction and the transaction is committed in the end. I found many PreparedStatement.executeQuery() calls in between. Does PreparedStatement.executeQuery() call obtains a lock? What could be the other possible reason for the deadlock? I tried googling but no luck. I read this and this but did not get my answer.

Any references to some standard documentation will be helpful. I am using MySQL(InnoDB).

Community
  • 1
  • 1
Yasin
  • 1,906
  • 1
  • 21
  • 37

1 Answers1

0

Connect to Database and use "rollback" command until there are no transactions. Then check your code you should have try catch block something like this:

try
{
   // sql queries
   // transaction commit command, commit() method on connection
}
catch(Exception) //SQLException etc.
{
    //transaction rollback command, rollback() method on connection
}

Also I'd suggest to setAutoCommit to false because. Check this and this

After the auto-commit mode is disabled, no SQL statements are committed until you call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit.

Robert
  • 19,800
  • 5
  • 55
  • 85
  • Yes, thats exactly done. I just want to know if `PreparedStatement.executeQuery()` obtains a lock? – Yasin May 28 '14 at 10:48
  • and if it holds the lock until the transaction is committed? – Yasin May 28 '14 at 10:50
  • do you have setAutoCommit(false)? – Robert May 28 '14 at 10:50
  • Yes `setAutoCommit(false)` – Yasin May 28 '14 at 10:51
  • 1
    then if execute query is between block of "beginTransaction" and commit then it will be holded until commit() command or if exception is thrown then it will be rollbacked. You should really check this link http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html – Robert May 28 '14 at 10:53