MySQL InnoDB中唯一索引和非唯一索引时的加锁情况

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qingsong3333/article/details/78024931
MySQL InnoDB中唯一索引和非唯一索引时的加锁情况

MySQL的默认隔离级别为RR,这篇文章讨论一下唯一索引和非唯一索引时,使用lock read(consistent read不加锁,不讨论)时的不同的加锁策略。 前提条件是使用了RR隔离级别,并且使用了索引扫描

假设有以下表的定义和数据:
mysql> create table test1 (year int);
mysql> insert into test1 values(2010),(2007),(2005),(2012),(2000),(2017);
mysql> select * from test1;
+------+
| year |
+------+
| 2010 |
| 2007 |
| 2005 |
| 2012 |
| 2000 |
| 2017 |
+------+
6 rows in set (0.00 sec)

1. 非唯一索引

mysql> create index idx on test1(year);

Session 1:
mysql> start transaction;
mysql> select * from test1 where year between 2007 and 2010 for update;
+------+
| year |
+------+
| 2007 |
| 2010 |
+------+
2 rows in set (0.00 sec)

Session 2:

mysql> insert into test1 values(2004); =>Query OK,
mysql> delete from test1 where year=2005; =>Query OK,
mysql> insert into test1 values(2005); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2006); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2007); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2008); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2010); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2011); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from test1 where year=2012; => ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2012); =>Query OK,

加锁范围:(2005,2012], 也就是在2007,2010,2012上各加了一个Next key lock.
========
show engine innodb status\G 显示如下,可以看到有索引idx上有3个x锁(由于idx非clustered index,所以clustered index上也要加3行锁)

MySQL thread id 11, OS thread handle 139829643007744, query id 257 localhost root
TABLE LOCK table `sample`.`test1` trx id 156570 lock mode IX
RECORD LOCKS space id 7429 page no 4 n bits 80 index idx of table `sample`.`test1` trx id 156570 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007d7; asc ;;
1: len 6; hex 0000009a8a12; asc ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 0000009a8a11; asc ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007dc; asc ;;
1: len 6; hex 0000009a8a14; asc ;;

RECORD LOCKS space id 7429 page no 3 n bits 80 index GEN_CLUST_INDEX of table `sample`.`test1` trx id 156570 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000009a8a11; asc ;;
1: len 6; hex 000000026385; asc c ;;
2: len 7; hex de000001660110; asc f ;;
3: len 4; hex 800007da; asc ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000009a8a12; asc ;;
1: len 6; hex 000000026385; asc c ;;
2: len 7; hex de00000166011f; asc f ;;
3: len 4; hex 800007d7; asc ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000009a8a14; asc ;;
1: len 6; hex 000000026385; asc c ;;
2: len 7; hex de00000166013d; asc f =;;
3: len 4; hex 800007dc; asc ;;

=========

图示如下:



2. 非唯一索引,Unique search

Session 1:
mysql> start transaction;
mysql> select * from test1 where year =2010 for update;
+------+
| year |
+------+
| 2010 |
+------+
1 row in set (0.00 sec)

====
Session 2:
mysql> delete from test1 where year=2007 => Query OK
mysql> insert into test1 values(2007); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2009); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2010); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2011); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from test1 where year=2012; => Query OK

加锁范围:(2007,2012),也就是在2010上加了一个Next key lock,在2011后上加了一个gap锁

4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 11, OS thread handle 139829643007744, query id 262 localhost root
TABLE LOCK table `sample`.`test1` trx id 156571 lock mode IX
RECORD LOCKS space id 7429 page no 4 n bits 80 index idx of table `sample`.`test1` trx id 156571 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 0000009a8a11; asc ;;

RECORD LOCKS space id 7429 page no 3 n bits 80 index GEN_CLUST_INDEX of table `sample`.`test1` trx id 156571 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000009a8a11; asc ;;
1: len 6; hex 000000026385; asc c ;;
2: len 7; hex de000001660110; asc f ;;
3: len 4; hex 800007da; asc ;;

RECORD LOCKS space id 7429 page no 4 n bits 80 index idx of table `sample`.`test1` trx id 156571 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007dc; asc ;;
1: len 6; hex 0000009a8a14; asc ;;

========示意如下:



3. 唯一索引

mysql> drop index idx on test1;
mysql> create unique index idx on test1(year);

Session 1:

mysql> start transaction;
mysql> select * from test1 where year between 2007 and 2010 for update;
+------+
| year |
+------+
| 2007 |
| 2010 |
+------+
2 rows in set (0.00 sec)

Session 2:
mysql> delete from test1 where year=2005; => Query OK
mysql> insert into test1 values(2006); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2007); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2008); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2010); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2011); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2012); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from test1 where year=2012; =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2013); =>Query OK

加锁范围:(2005,2012]和情形1一样,不再重复

4. 唯一索引,Unique search

Session 1:
mysql> start transaction;
mysql> select * from test1 where year=2010 for update;
+------+
| year |
+------+
| 2010 |
+------+
1 row in set (0.00 sec)

Session 2:
mysql> insert into test1 values(2009); =>Query OK
mysql> insert into test1 values(2010); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test1 values(2011); =>Query OK

加锁范围:仅仅是2010。

TABLE LOCK table `sample`.`test1` trx id 156595 lock mode IX
RECORD LOCKS space id 7429 page no 4 n bits 80 index idx of table `sample`.`test1` trx id 156595 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 0000009a8a11; asc ;;

RECORD LOCKS space id 7429 page no 3 n bits 80 index GEN_CLUST_INDEX of table `sample`.`test1` trx id 156595 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000009a8a11; asc ;;
1: len 6; hex 000000026385; asc c ;;
2: len 7; hex de000001660110; asc f ;;
3: len 4; hex 800007da; asc ;;

最后这一点和DB2是不同的,只有一条记录的情况下,若是唯一索引,DB2里也只要在2010上加锁,但2007~2010之间的数是不允许插入的。

总结一下就是,只有一种特殊情况:就是唯一索引且只有一条记录的情况下,不加Gap锁,其他情况都要加Gap锁。(MySQL官方文档的原文是 Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.)

注1:在show engine innodb status\G输出中:
如果是单纯的record lock,显示的是:locks rec but not gap
如果是单纯的gap lock, 显示的是:locks gap before rec
如果是gap+record,也就是next key lock,显示的是:lock_mode X <空>

注2:RR隔离级别才有gap lock,Read Commited下没有

没有更多推荐了,返回首页