MySql timeouts waiting for table lock, no apparent deadlocks

Question

I started having the following error on two different applications that write to the same database and table:

Lock wait timeout exceeded; try restarting transaction

At the beginning I thought it was caused by some slow queries creating deadlocks, but I haven't been able to find any evidence of that.

This is the output of the transactions section of show engine innodb status;

------------
TRANSACTIONS
------------
Trx id counter 1031368
Purge done for trx's n:o < 1031258 undo n:o < 0 state: running but idle
History list length 907
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1031348, not started
MySQL thread id 120, OS thread handle 0x7f5e4c148700, query id 1068 localhost db2_prod cleaning up
---TRANSACTION 0, not started
MySQL thread id 53, OS thread handle 0x7f5e3a209700, query id 1136 localhost root init
show engine innodb status
---TRANSACTION 1031286, not started
MySQL thread id 63, OS thread handle 0x7f5e4c0b5700, query id 509 localhost db1_prod cleaning up
---TRANSACTION 1031367, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 24
MySQL thread id 121, OS thread handle 0x7f5e3a26b700, query id 1127 localhost db1_prod update
insert into `my_table` (`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`) values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?,
------- TRX HAS BEEN WAITING 16 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 234 page no 1310 n bits 384 index `PRIMARY` of table `db1`.`my_table` trx id 1031367 lock mode S locks rec but not gap waiting
Record lock, heap no 230 PHYSICAL RECORD: n_fields 9; compact format; info bits 32
 0: len 4; hex 0000125b; asc    [;;
 1: len 4; hex 00002a4c; asc   *L;;
 2: len 4; hex 00000eeb; asc     ;;
 3: len 6; hex 0000000fbc8c; asc       ;;
 4: len 7; hex 390000014c1434; asc 9   L 4;;
 5: len 8; hex 0000000000001c40; asc        @;;
 6: len 5; hex 999968ce0c; asc   h  ;;
 7: len 4; hex 00000000; asc     ;;
 8: SQL NULL;

------------------
---TRANSACTION 1031308, ACTIVE 837 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 60, OS thread handle 0x7f5e3a23a700, query id 1059 localhost 127.0.0.1 db1_prod cleaning up
Trx read view will not see trx with id >= 1031309, sees < 1031309

Any idea what could be causing this? Let me know if you need more info. Thanks in advance.

Answer 1

If a transaction has been running 837 seconds, I suggest that its is poorly designed. I don't like a transaction to last even 5 seconds, much less the default innodb_lock_wait_timeout=50.

Can't wait for user - A transaction should not include any user interaction. Waiting for a user might mean waiting for him/her to take a phone call. If you need something locked for some kind of user 'session', BEGIN...COMMIT is not the way to do it.

autocommit=0 is bad - Another possible mistake is autocommit=0 then forgetting to COMMIT. For that reason, I recommend never using that setting. Instead, be explicit about including BEGIN and let it be a reminder that you need a COMMIT.

If you would like to discuss this further, let's see your whole 'transaction', either at a high level, or with specific SQL statements.

Answer 2

I faced the similar issue, some days ago, I tried the following methods to solve that issue.

Increase the value of innodb_lock_wait_timeout, you can do this by using one of the following two methods,

Set it in my.cnf file:

[mysqld]
innodb_lock_wait_timeout=120

This method required server restart.

Set it during run time dynamically:

SET GLOBAL innodb_lock_wait_timeout = 120; 

Kill the transaction which causing this locking.

You can view that locking transaction by running the following command:

SHOW FULL PROCESSLIST \G

To kill a query:

Kill [id] eg: kill 27

Note: Sometimes the locking query state displayed as sleep state.

The work around is you can view the status of the innodb by using the following command:

show engine innodb status \G

Transaction locks occurs when two threads trying to access a same row (innoDB) or table (MyISAM). In our environment, most of the time, transaction locks occurs due to one query updating a row and another query trying to read from that row.

Killing the transaction is not a good idea, you should try to fix the issue. For example, if some update query takes longer time, you can check for table indexes or trying to rewrite the query in a way, it should execute in shorter time.

From your query, the locking occurs when a query accessing the table my_table for longer time and another query is waiting for previous query to complete its task.

By setting innodb_lock_time_out and tuning the specific query, you can solve this issue. I hope this answer will help you as a starting point.