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

mysq