mysql 唯一索引 锁表_MySQL InnoDB中唯一索引和非唯一索引时的加锁情况

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 LOCKSspace id 7429 page no 4 n bits 80

index idxof 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 LOCKSspace id 7429 page no 4 n bits 80

index idxof 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 LOCKSspace 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 idxof 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 idxof 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下没有