MySQL DELETE 删除语句加锁分析
@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
页:
[1]