MySQL Lock Wait Time Out Exceeded upon Delete

innodblockingMySQLmysql-5.6timeout

I have a Delete Statement which fails with Below Exception

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

It waits for 50 Seconds for Lock and gives Up, I wanted to know which all SQL Statements can lock a table.

LOCK WAIT 6017 lock struct(s), heap size 376, 137376 row lock(s), undo log entries 22011
MySQL thread id 8242, OS thread handle 0x2b019e749700, query id 13811010 10.103.89.37 administrator updating
DELETE from CONFIGSTOREQASTAGINGREL.EVENT_DETAILS where UPDATE_DATE < 20191013 AND COMPONENT_NAME = 'health'
------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 194 page no 9414 n bits 17 index `GEN_CLUST_INDEX` of table `CONFIGSTOREQASTAGINGREL`.`EVENT_DETAILS` trx id 38176269 lock_mode X waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len=6; bufptr=0x2b01d5f29030; hex= 0000001ef5fb; asc       ;;
 1: len=6; bufptr=0x2b01d5f29036; hex= 0000024674c5; asc    Ft ;;
 2: len=7; bufptr=0x2b01d5f2903c; hex= af000004900110; asc        ;;
 3: len=30; bufptr=0x2b01d5f29043; hex= 333532613463313736333863613932393935336262653631646135306137; asc 352a4c17638ca929953bbe61da50a7; (total 32 bytes);
 4: len=30; bufptr=0x2b01d5f29063; hex= 316663346166393133353164633731393062363833393134386335326535; asc 1fc4af91351dc7190b6839148c52e5; (total 32 bytes);
 5: len=6; bufptr=0x2b01d5f29083; hex= 6865616c7468; asc health;;
 6: len=30; bufptr=0x2b01d5f29089; hex= 36436c74773144627644656948474472756e774d4f362020202020202020; asc 6Cltw1DbvDeiHGDrunwMO6        ; (total 32 bytes);
 7: len=4; bufptr=0x2b01d5f290a9; hex= 81341726; asc  4 &;;
 8: len=4; bufptr=0x2b01d5f290ad; hex= 800003aa; asc     ;;
 9: len=16; bufptr=0x2b01d5f290b1; hex= 4f494d2d534552564943452020202020; asc OIM-SERVICE     ;;
 10: len=4; bufptr=0x2b01d5f290c1; hex= 80000000; asc     ;;
 11: len=0; bufptr=0x2b01d5f290c5; hex= ; asc ;;
 12: len=30; bufptr=0x2b01d5f290c5; hex= 436f6e6e656374697669747920746f20536563757265204167656e742069; asc Connectivity to Secure Agent i; (total 168 bytes);
 13: len=4; bufptr=0x2b01d5f2916d; hex= 5da441f8; asc ] A ;;

I wanted to know what is Record Lock and when does that gets locked.I would very thankful if someone explains what is the meaning of above statements i got from SHOW INNODB ENGINE STATUS

Best Answer

Do you have INDEX(COMPONENT_NAME, UPDATE_DATE) with the columns in that order? (Please provide SHOW CREATE TABLE.)

What else was running at the same time? (SHOW PROCESSLIST before the DELETE times out.)

Let's see that other query; perhaps it can be optimized to run faster.

What is the value of autocommit? Are you using BEGIN...COMMIT?

Related Solutions

MySQL: Lock wait timeout exceeded

This is definitely a head-scratcher, but here's one possibility, especially if you're running MySQL 5.1 or earlier. MySQL Bug #37346 addresses an issue where there are locks at two different layers that are (at least partially) unaware of each other's existence... an uncommitted transaction is holding locks inside the storage engine (invisible to MySQL's core), while the ALTER TABLE is holding a different kind of lock, outside the storage engine (invisible to InnoDB).

In the bug report, when the ALTER TABLE is almost done, it bumps into the inner locks and ultimately times out with the message you see.

This is referenced as "fixed" in the release notes for 5.5.3 but I didn't find it mentioned in 5.1.x, which suggests to me that it might still exist there. It seems like a long shot but it did seem worth mentioning.

Until I found the above, my initial thoughts had to do with the table that is referenced by fk_foo_1... I was going to speculate that InnoDB might be trying something unnecessary related to validation of the foreign key constraint (unnecessary, since the constraint is about to disappear)... or that an uncommitted update or delete on the referenced table might be setting locks on the table you're trying to alter... an update or delete "over there," needing to be validated or trying to cascade "over here," while the ALTER TABLE is underway, and not waiting as it should for the lock held by the ALTER TABLE.

Mysql – Lock wait time out exceed restart transaction

Please look carefully at the queries that are locking

The SHOW ENGINE INNODB STATUS\G is incomplete

I cannot fully tell you anything more because you gave me three transactions that

execute the exact same query
lock the same page in the GEN_CLUST_INDEX

as shown from your display

---TRANSACTION 0 2491310, ACTIVE 29 sec, process no 6622, OS thread id 140388597384960 starting index read
Update tblID Set lastValue=4372  Where tableName='tblUnAst'

---TRANSACTION 0 2491307, ACTIVE 38 sec, process no 6622, OS thread id 140388597917440 starting index read
Update tblID Set lastValue=4372  Where tableName='tblUnAst'

---TRANSACTION 0 2491299, ACTIVE 46 sec, process no 6622, OS thread id 140388388042496 starting index read
Update tblID Set lastValue=4372  Where tableName='tblUnAst'

Notwithstanding, there are undo log entries coming from several transactions.

This situation is so reminiscent of three question from one person (RedBlueThing) that I helped troubleshoot. Here were those posts:

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

RedBlueThing solved it by changing his code, not his configuration.

In light of this, look back at the SHOW ENGINE INNODB STATUS\G output. From what you provided, I see many transactions and many undo log entries from multiple transactions. Apparently, this came from multiple transactions trying to run the exact same query.

RECOMMENDATION

You are using a very old version of the InnoDB Storage Engine. You should upgrade to the latest MySQL as you can set a higher number of read and write I/O threads The new InnoDB can also access up to 48 cores. If you do not upgrade, the very least you should do is set innodb_thread_concurrency to 0.

As @altmannmarcelo mentioned in the comment below, you can install the InnoDB Plugin. See my post MySQL - Installing InnoDB Plugin

Best Answer

Related Solutions

MySQL: Lock wait timeout exceeded

Mysql – Lock wait time out exceed restart transaction

RECOMMENDATION

Related Question