您好,登錄后才能下訂單哦!
1、表結(jié)構(gòu)
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、數(shù)據(jù)
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、插入數(shù)據(jù)
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、總結(jié)
唯一二級(jí)索引重復(fù)鍵檢查中,如果遇到重復(fù)key,那么會(huì)對(duì)重復(fù)的key加S類型的next-key鎖。注意:這里不論是什么隔離級(jí)別加的都是next-key鎖
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。