MySQL Innodb NEXT-KEY加锁

                               MySQL Innodb NEXT-KEY加锁

       mysql innodb的next-key的加锁机制,导致innodb的并发性变得相当比较差,而在RR的隔离
级别下,如果不使用next-key加锁机制又会出现幻读的情况,所以为了避免幻读的情况
发生,还是使用了这种加锁方式.

创建一个表和索引,默认为RR的隔离级别
mysql> create table t6 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t6 add key ix_a(a);

插入一些记录
mysql> select * from t6;
+------+
| a    |
+------+
|    4 |
|    6 |
|    7 |
|    8 |
|   12 |
+------+
5 rows in set (0.01 sec)

会话1

 session1>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

 session1>select * from t6 where a>10 for update;
+------+
| a    |
+------+
|   12 |
+------+
1 row in set (0.00 sec)

使用了next-key锁,而且操作为'>',因此锁定的记录范围为(8,10),(10,无穷大);

session2>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

session2>insert into t6 values(7);
Query OK, 1 row affected (0.00 sec)

7不在这个范围以内,可以插入进去

session2>insert into t6 values(9);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session2>insert into t6 values(11);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session2>insert into t6 values(100);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

而9,11,100都是落入这个被锁定的范围以内,因此都无法被插入进去

我们现在在看一种情况'='操作

 session1>select * from t6 where a=10 for update;
Empty set (0.01 sec)

为了防止幻读,innodb锁定的值范围为(8,10),(10,小于12)

session2>insert into t6 values(7);
Query OK, 1 row affected (0.00 sec)

7不在这个范围以内,可以插入进去

session2>insert into t6 values(8);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session2>insert into t6 values(11);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

8,11被认为是这个范转以内的,因此无法被插入

session2>insert into t6 values(12);
Query OK, 1 row affected (0.00 sec)

session2>insert into t6 values(100);
Query OK, 1 row affected (0.00 sec)

而12,100已经超出了被锁定的范转以外,因此可以被插入.

当我们在a=8的情况下insert会失败,而delete和update操作可以成功

session2>insert into t6 values(8);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session2>update t6 set a=a+100 where a=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session2>rollback;

session2>delete from t6 where a=8;
Query OK, 1 row affected (0.00 sec)

当DML 操作为delete和update时,可以删除成功.因为next key是为了防止幻读,而delete,update操作不会
新增记录,而insert和update可能会增加或是改变记录,因为

因上从以上实验我们可以看出表(假设列有索引)加锁方式和以下因素有关:隔离级别,操作符类型,DML类型.