InnoDB transaction Lock row lock-insert One or two-level index duplicate key lock case

1. Table structure

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. Data

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. Inserting data

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, LOCK schematic diagram

6. Summary
Only one or two-level index duplicate key check, if you encounter duplicate key, then the duplicate key plus s type of next-key lock. Note: No matter what the isolation level is, next-key locks are added.

InnoDB transaction Lock row lock-insert One or two-level index duplicate key lock case