MySql timeouts waiting for table lock, no apparent deadlocks 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 &lt; 1031258 undo n:o &lt; 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 &gt;= 1031309, sees &lt; 1031309


Any idea what could be causing this? Let me know if you need more info. Thanks in advance.
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.