next-key locking
都忘记自己写的都是些什么东西了。。。X锁是写锁/排他锁，S锁是读锁/共享锁。
----------------------------------------------
在centos7 mysql 5.7.27里, 默认的事务隔离级别是repeatable read, 默认使用next-key locking.
我看到很多资料和文章都只解释了等值查询for update加X锁的情况, 那如果是大于或者大于等于查询呢?
以下是对next-key locking的一些理解, 我就不写太多概念的东西了, 难理解.
create table n(a int, b varchar(10), c int, primary key(a), key(c));
insert into n select 1, '1', 1;
insert into n select 10, '10', 10;
insert into n select 20, '20', 20;
insert into n select 30, '30', 30;
insert into n select 50, '50', 50;
autocommit是连接级别的一个变量, 默认是打开的:
先把它关掉:
set @@autocommit=0;
如下语句会锁住[30, +无穷大):
select c from n where c>30 for update;
如下语句会锁住[20, 30]和[30, +无穷大), 其实就是[20, +无穷大):
select c from n where c>=30 for update;
其实是锁定前一个索引值到30之间, 以及30到+无穷大之间, 也包括30本身.
这些锁定都是在c索引上, c索引是建立在c列上的一个允许重复的普通索引.
Update不存在的行数据
会使用next key locking.
表结构和数据如下:
CREATE TABLE sorderdetail( id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary Key', orderid int NOT NULL, productid int NOT NULL, description VARCHAR(255), KEY(orderid), KEY(productid) ) COMMENT '';
orderid和productid都是普通索引, 先开启事务A, 对于以下语句, 在repeatable read级别下会用S锁住进行next key locking:
begin;
update sorderdetail set description='333' where productid=100;
在其他窗口查看整个数据库锁的情况:
select OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS, `LOCK_DATA` from performance_schema.data_locks;
其中第一行是一个表级别的X意向锁，防止有其他事务想锁住整个表。第二行是在productid索引上加的区间锁(90, +无穷大)，注意此时是不包含索引的最大值90的，个人认为也算合理，因为语句摆明了是要修改productid为100的数据，锁住90算嘛事呀？
事实证明，两个事务之间加表级别的意向排他锁时不会互斥，对于索引超限排他行锁(也就是`X`+`supremum pseudo-record`)也不互斥：
但是`X,INSERT_INTENTION`+`supremum pseudo-record`就会和`X`+`supremum pseudo-record`锁互斥了，insert语句会加`X,INSERT_INTENTION`锁甚至会加`X,GAP,INSERT_INTENTION`锁。
此时如果事务B执行类似的语句:
begin;
update sorderdetail set description='333' where productid=101;
如此是可以执行成功的, 因为也只会在(90, +无穷大)加S锁.
接下来, 事务A执行如下语句:
insert into sorderdetail(orderid, productid, description) values(90,90," 90 test description");
将会卡住, 因为insert语句需要加X锁, 对于以上语句, 是需要对90这个productid索引加X锁, 但是由于上面事务B持有[90, +无穷大)的S锁以及X锁的排它性, 事务A此时在等事务B释放[90, +无穷大)的S锁, 所以无法执行.
事务A在等待的过程中, 事务B如果再执行如下语句:
insert into sorderdetail(orderid, productid, description) values(92,92," 92 test description");
类似的, 这条insert需要对92加X锁, 但由于事务A也持有了[90, +无穷大)的S锁以及S锁与X锁不兼容, 所以, 事务B在等事务A释放[90, +无穷大)上的S锁, 而事务A也在等事务B释放[90, +无穷大)的S锁, 形成死锁.
此时innodb引擎会检测到这里形成了死锁, 会自动把事务B回滚, 让事务A执行下去.
如果不是尝试插入100而是尝试插入70, 那么会用next key locking的区间就是[51, 75), 原理类似.
二级索锁和主键锁
现在有一个奇怪的问题:
事务A尝试update一条不存在的记录76, 按理说此时会用next key locking在[75, 80)上加S锁, 这么一来, 事务B应该是既无法insert 75, 也无法update 75的记录才对, 可是事实是, 事务B还是可以update 75, 但是insert会被要求等待锁. 为什么事务B可以update 75呢??? 稍后回来思考~~
注意这里的事务A加的S锁区间[75, 80)只存在于productid这个索引上, 而在主键索引上没有加任何锁. 事务B如果要update 75的话, 所加S锁在75上,所加X锁在id=22的主键索引上, id=22的主键索引上没有任何锁, 所以事务B可以执行这句update语句. 而insert? 是又需要在product索引上75加X锁的, 所以和事务A的S锁区间[75, 80)冲突, 无法执行.
以上.
