ER_LOCK_WAIT_TIMEOUT issue resolved , dead lock transaction mysql

 Hey guys,

Today, i faced an issue with transaction getting dead locked in mysql. i was unable to perform any action on the locked table. 

I created a transaction with autocommit : false and forgot to either rollbock or commit the transaction.

At the same time another transaction been created and tried to access the same table and final result is 

ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction

Solution:

--------------

SHOW PROCESSLIST 

See if your query is present in the processes and kill the <id> which causing the table lock.

KILL <processid>

Above command did not help me as process or query id is not present to overcome the problem. 

Dig in more to check the transactions list and try to kill the one that got dead locked.

First  , let's get some Engine status. i m using INNODB engine for Mysql.

SHOW ENGINE INNODB STATUS

This will result us with below sample output. 

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5367
OS WAIT ARRAY INFO: signal count 4706
RW-shared spins 0, rounds 2666, OS waits 728
RW-excl spins 0, rounds 1878, OS waits 44
RW-sx spins 34, rounds 217, OS waits 4
Spin rounds per wait: 2666.00 RW-shared, 1878.00 RW-excl, 6.38 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 418476
Purge done for trx's n:o < 418416 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421985361275640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421985361274720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------

In the list of transactions , if you any lock struct(s) more than 0 then you will get it with query ID and thread id 

ex: MySQL thread id 197, query id 771 localhost

Now we need to kill the thread thats causing the dead lock situation with KILL command. 

KILL 197  

If you want to kill the query then KILL QUERY 771  followed by KILL 197 

Thus, we can overcome to transaction dead lock and Er_lock_wait_time_timeout error. 

if you really want to Dig in more on this then below link will help you :

https://severalnines.com/database-blog/how-fix-lock-wait-timeout-exceeded-error-mysql