0

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

0

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?

| improve this answer | |

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.