0

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.

  • 1
    Deadlocks are resolved instantly when detected - lock wait is not a deadlock, just one transaction waiting for some other one to free a locked row. The other transaction can freely continue (from the DB point of view) but it for some reason takes too long (app error or maybe by design a slow operation). – jkavalik May 26 '16 at 19:11
  • 1
    TRANSACTION 1031308, ACTIVE 837 sec - can you find out what this one is doing? It might be holding some of the locks. And TRANSACTION 1031367, ACTIVE 16 sec inserting - is it taking 16+ seconds to execute the inserts as suggested by the other transaction waiting for a lock on the same table? – jkavalik May 26 '16 at 19:14
  • 1
    Thanks for the info, @jkavalik I missed that last transaction log at the very end, after some investigation (this is going to sound ridiculous) I found that the lock was made by my DBMS( DBeaver), For all the tests I did I had a connection open and had to remove first some rows for the db, and this was creating a transaction that wasn't committed until i closed the connection. Thanks for the help! – jcalonso May 28 '16 at 3:36
  • happened to me some years ago with Firebird DB. After few hours the server almost stopped because redo logs grew too big. – jkavalik May 28 '16 at 5:35
  • Never use autocommit=0, it begs for that goof. – Rick James Jun 4 '16 at 16:35
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.

0

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.

  • Hi rathishDBA, thanks for the info, increasing the innodb_lock_wait_timeout was the first thing I tried to see if this was the issue, I set it, up to 300 seconds, tried both ways, (ini file and restart mysql and SET GLOBAL) nothing helped. If you see in the transactions log, there aren't active queries other than the one is waiting for table lock grant. Any other suggestion? – jcalonso May 26 '16 at 12:11
  • @jcalonso active queries are not "needed" - when inside a transaction, the locks from already finished queries persist until the transaction ends. – jkavalik May 26 '16 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.