![]() Tnx 1 is holding and waiting for the same lock.Īfter some research, though I still cannot figure out the root cause, my perception is that the insertion in Tnx 1 acquires a gap lock which is somehow overlapping with the gap lock by the select. However, Tnx 2 is rolled back due to deadlock. I expected that Tnx 2 would wait until Tnx 1 released the lock, and then continue its own business. for update as a uniqueness check for a following sequence of insertions. RECORD LOCKS space id 24 page no 4 n bits 72 index search of table `test_tnx`.`test` trx id 1332 lock_mode X locks gap before rec insert intention waiting *** (2) WAITING FOR THIS LOCK TO BE GRANTED: Record lock, heap no 1 PHYSICAL RECORD: n_fields 1 compact format info bits 0Ġ: len 7072656d756d asc supremum RECORD LOCKS space id 24 page no 4 n bits 72 index search of table `test_tnx`. MySQL thread id 62, OS thread handle 123145394434048, query id 63 localhost root update TRANSACTION 1332, ACTIVE 29 sec insertingĤ lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1 Record lock, heap no 2 PHYSICAL RECORD: n_fields 2 compact format info bits 0Ġ: len 652d6c6f636b asc pre- lock *** ( 1) WAITING FOR THIS LOCK TO BE GRANTED: Select * from test where val= 'pre-lock' for update MySQL thread id 5, OS thread handle 123145394155520, query id 62 localhost root Sending data LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) TRANSACTION 1333, ACTIVE 18 sec starting index read The result of show engine innodb status: 1 ![]() Select * from test where val= 'pre-lock' for update ĮRROR 1213 (40001): Deadlock found when trying to get lock try restarting transaction ) ENGINE= InnoDB AUTO_INCREMENT= 4 DEFAULT CHARSET=utf8 `id` bigint( 11) NOT NULL AUTO_INCREMENT, The situation is quite easy to reproduce Mysql 5.7.17 (also tested on other versions in 5.5 or 5.6): 1 But the answers didn’t really explain why this happens. Similar questions have been asked on StackOverflow, e.g., this and this. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |