MySQL DELETE 删除语句加锁分析

shuijingping

@1 show engine innodb status　　

　　

　　
---TRANSACTION 146981, ACTIVE 12 sec starting index read
　　
mysql tables in use 1, locked 1
　　
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
　　
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2804 localhost msandbox updating
　　
delete from t where c1=8
　　
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
　　
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X waiting
　　
------------------
　　
TABLE LOCK table `test`.`t` trx id 146981 lock mode IX
　　
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X waiting
　　
---TRANSACTION 146980, ACTIVE 16 sec
　　
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
　　
MySQL thread id 355, OS thread handle 0x7f61ab145700, query id 2802 localhost msandbox cleaning up
　　
TABLE LOCK table `test`.`t` trx id 146980 lock mode IX
　　
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146980 lock_mode X
　　
RECORD LOCKS space id 54 page no 3 n bits 80 index `PRIMARY` of table `test`.`t` trx id 146980 lock_mode X locks rec but not gap
　　
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146980 lock_mode X locks gap before rec
　　

　　
@2 show engine innodb status
　　

　　
---TRANSACTION 146981, ACTIVE 50 sec
　　
3 lock struct(s), heap size 360, 1 row lock(s)
　　
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2804 localhost msandbox cleaning up
　　
TABLE LOCK table `test`.`t` trx id 146981 lock mode IX
　　
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X
　　
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X locks gap before rec