0

I have a situation where 2 transactions create a mysql deadlock.

The following error is fired : Deadlock found when trying to get lock; try restarting transaction

If I'm correct, this error means that mysql deadlock timeout is expired, and mysql try to do something to removes this deadlock.

What isn't clear for me is what means try restarting transaction ? How a transaction can be "restarted" ?

What happens to the 2 locked transactions ? Are they both canceled (roll-backed) ? Or is it just one of them that is canceled so the lock can be released.

Thanks in advance

Quentin
  • 3,150
  • 4
  • 24
  • 34

1 Answers1

2

There is no deadlock timeout (though there are lock timeouts). If a deadlock is detected, no amount of time will resolve it, so MySQL reacts immediately.

MySQL will roll back one or more transactions until the deadlock is resolved.

From MySQL docs:

InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.

It is up to your application that is making the SQL call to retry the transaction.

MySQL has some recommendations in its documentation How to Cope with Deadlocks.

If you wish to try to avoid the deadlock and are having trouble understanding the cause of the deadlock, I recommend starting another question, and posting the complete affected queries and schema, and ideally the deadlock report from SHOW ENGINE INNODB STATUS.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • Ok so MySQL won't retry to restart on of the transaction by itself, I miss-understood the message : it's just an advice. Thanks for the timeout information, I made a confusion whith the following error : `ERROR 1205 (HY000): Lock wait timeout`. This topic helped : http://stackoverflow.com/questions/3808986/when-does-innodb-time-out-instead-of-reporting-deadlock – Quentin Aug 22 '14 at 14:18