Mysql一分钟定位 Next-Key Lock，你需要几分钟 (2)

select * from information_schema.innodb_locks \G 查看锁的占用情况。

***************************[ 1. row ]*************************** lock_id | 457240:131:4:4 lock_trx_id | 457240 lock_mode | X,GAP lock_type | RECORD lock_table | `test`.`peoples` lock_index | idx_peoples_age lock_space | 131 lock_page | 4 lock_rec | 4 lock_data | 35, 7 ***************************[ 2. row ]*************************** lock_id | 457239:131:4:4 lock_trx_id | 457239 lock_mode | X lock_type | RECORD lock_table | `test`.`peoples` lock_index | idx_peoples_age lock_space | 131 lock_page | 4 lock_rec | 4 lock_data | 35, 7

innodb_locks 表包含了已经获取到的锁信息和请求锁的信息。lock_index字段表示锁走的索引，record锁都是基于索引完成。

根据上面事务457240状态是获取锁，lock_data | 35, 7，表示请求的数据。而事务457239占用了当前X锁。

锁等待

select * from information_schema.innodb_lock_waits 查看锁等待信息。

***************************[ 1. row ]*************************** requesting_trx_id | 457240 requested_lock_id | 457240:131:4:4 blocking_trx_id | 457239 blocking_lock_id | 457239:131:4:4

457240 事务需要获取131:4:4锁，457239 事务占用了131:4:4锁。

innodb 监视器
show engine innodb status

LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422032240994144, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 457240, ACTIVE 394 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1 MySQL thread id 5, OS thread handle 140556966967040, query id 105 172.17.0.1 root update insert into peoples(age) values(31) ------- TRX HAS BEEN WAITING 165 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 131 page no 4 n bits 72 index idx_peoples_age of table `test`.`peoples` trx id 457240 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000023; asc #;; 1: len 4; hex 00000007; asc ;; ------------------ ---TRANSACTION 457239, ACTIVE 407 sec 3 lock struct(s), heap size 1136, 5 row lock(s) MySQL thread id 8, OS thread handle 140556966696704, query id 104 172.17.0.1 root

MySQL thread id 5 正在准备上插入意向锁，插入意向锁本质上是加间隙锁，是为了保证最大并发插入，不相关的行插入不受到互斥。thread id 5 需要保证在插入前加上间隙锁，主要是防止并发插入带来的一致性问题。