In my production error logs I occasionally see:
SQLSTATE[HY000]: General error: 1205
Lock wait timeout exceeded; try
restarting transaction
I know which query is trying to access the database at that moment but is there a way to find out which query had the lock at that precise moment?
Best Answer
What gives this away is the word transaction. It is evident by the statement that the query was attempting to change at least one row in one or more InnoDB tables.
Since you know the query, all the tables being accessed are candidates for being the culprit.
From there, you should be able to run
SHOW ENGINE INNODB STATUS\G
You should be able to see the affected table(s)
You get all kinds of additional Locking and Mutex Information.
Here is a sample from one of my clients:
You should consider increasing the lock wait timeout value for InnoDB by setting the innodb_lock_wait_timeout, default is 50 sec
You can set it to higher value in
/etc/my.cnf
permanently with this lineand restart mysql. If you cannot restart mysql at this time, run this:
You could also just set it for the duration of your session
followed by your query