When is innodb_lock_wait_timeout ignored?

Question

I'm having a weird case of the classic deadlock exception:

Caused by: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction.

The code is identified and indeed, it's possible to get a deadlock. The thing is that I'm getting the exception immediately. I've set the innodb_lock_wait_timeout variable to 1 hour (3600). But still, the exception comes right away.

What exactly is happening?

Answer 1

You are hitting a deadlock the lock_wait_timeout variable is how long a query will wait to acquire locks before timing out - not the same thing.

From the manual: The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

You'd see this if you were hitting the timeout. You have a proper deadlock that needs fixing. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction