InnoDB事務鎖之行鎖-insert唯一二級索引重復鍵加鎖案例

1、表結構

CREATE TABLE `t3` (  
  `id` int(11) NOT NULL,  
  `id2` int(11) DEFAULT NULL,  
  `id3` int(11) DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  UNIQUE KEY `id2` (`id2`,`id3`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、數據

mysql> select *from t3;  
+----+------+------+  
| id | id2  | id3  |  
+----+------+------+  
|  6 |    1 |    1 |  
|  8 |    1 |    2 |  
| 10 |    1 |    3 |  
|  7 |    4 |    4 |  
|  1 |    6 |    6 |  
|  4 |    7 |    7 |  
+----+------+------+  
6 rows in set (0.00 sec)  

3、插入數據

mysql> insert into t3 values(11,1,3);  
ERROR 1062 (23000): Duplicate entry '1-3' for key 'id2'  

4、show engine innodb status\G

---TRANSACTION 17751, ACTIVE 17 sec  
2 lock struct(s), heap size 320, 1 row lock(s)  
MySQL thread id 10, OS thread handle 0xa32ffb90, query id 193 localhost root init  
show engine innodb status  
TABLE LOCK table `yzs`.`t3` trx id 17751 lock mode IX  
RECORD LOCKS space id 30 page no 4 n bits 80 index `id2` of table `yzs`.`t3` trx id 17751 lock mode S  
Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0  
 0: len 4; hex 80000001; asc     ;;  
 1: len 4; hex 80000003; asc     ;;  
 2: len 4; hex 8000000a; asc     ;;  

5、加鎖原理圖

6、總結
唯一二級索引重復鍵檢查中，如果遇到重復key，那么會對重復的key加S類型的next-key鎖。注意：這里不論是什么隔離級別加的都是next-key鎖