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类型.