文章目录
基础
默认拥有MySQL基础,并且对MySQL事务有一定了解,比如:MVCC快照读,当前读(lock in share mode/for update),间隙锁等知识
索引组织表
先理解下索引组织表。
辅助索引
聚集索引
Innodb中的索引数据结构是 B+ 树,数据是有序排列的,从根节点到叶子节点一层层找到对应的数据。普通索引,也叫做辅助索引,叶子节点存放的是主键值。主键上的索引叫做聚集索引,表里的每一条记录都存放在主键的叶子节点上。当通过辅助索引select 查询数据的时候,会先在辅助索引中找到对应的主键值,然后用主键值在聚集索引中找到该条记录。举个例子,用name=Alice来查询的时候,会先找到对应的主键值是18 ,然后用18在下面的聚集索引中找到name=Alice的记录内容是 77 和 Alice。
表中每一行的数据,是组织存放在聚集索引中的,所以叫做索引组织表。
了解索引数据结构的目的是为了说明,行锁是加在索引上的。
初始数据
CREATE TABLE `t` (
`id` INT ( 11 ) NOT NULL,
`c` INT ( 11 ) DEFAULT NULL,
`d` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `c` ( `c` )
) ENGINE = INNODB;
INSERT INTO t
VALUES
( 0, 0, 0 ),
( 5, 5, 5 ),
( 10, 10, 10 ),
( 15, 15, 15 ),
( 20, 20, 20 ),
( 25, 25, 25 );
参考规则
来源于极客时间MySQL实战45讲,大家可以根据这个规则理解我下面的例子,下面大部分是我自己另外总结的
总结的加锁规则里面,包含了两个“原则”、两个"优化"和一个"bug"。
1.原则1:加锁的基本单位是next-key lock。 希望你还记得,next-key lock是前开后闭区间。
2.原则2:查找过程中访问到的对象才会加锁。
3.优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。
4.优化2:引上的等值查询,向右遍历时且后-个值不满足等值条件的时候,next-key lock退化为间隙锁。
5.一个bug:唯一引上的范围查询会访问到不满足条件的第一个值为止。
一、update/delete没有索引的记录
说明:delete/update操作结果其实是一样的,所以只专门讲解update即可,delete不再累赘描述
update 没有索引且存在的记录/update没有索引且不存在的记录/update全表
事务1 | 事务2 |
---|---|
BEGIN; | |
UPDATE t SET d=999 WHERE d=99 (update 没有索引且存在的记录)或者 UPDATE t SET d=999 WHERE d=0 (update没有索引且不存在的记录)或者 UPDATE t SET d=999 (update全表) | |
SELECT * FROM t WHERE id=0 LOCK IN SHARE MODE/FOR UPDATE (会阻塞当前读(for update/lock in share mode ))SELECT * FROM t (不会阻塞普通/快照读)UPDATE t SET c=222 WHERE id=0 (会阻塞update已经存在的记录)UPDATE t SET c=222 WHERE id=66 (不会阻塞update不存在的记录)DELETE FROM t WHERE id=0 (会阻塞delete已经存在的记录)DELETE FROM t WHERE id=66 (不会阻塞delete不存在的记录)INSERT INTO t(id, c, d) VALUES (111, 22, 10) (会阻塞insert) | |
-
会阻塞当前读(for update/lock in share mode ),不会阻塞普通/快照读
-
会阻塞update已经存在的记录,不会阻塞update不存在的记录
-
会阻塞delete已经存在的记录,不会阻塞delete不存在的记录
-
会阻塞insert任意记录
这个例子其实可以很好的说明,数据库加锁针对的是索引,而不是记录。比如上面d字段没有索引,不管你update记录是否存在,都会锁住全表!!!
二、update/delete有唯一索引的记录
说明:delete/update操作结果其实是一样的,所以只专门讲解update即可,delete不再累赘描述
前提:这里针对的是id这个主键唯一索引
1、update有唯一索引且不存在的记录
select普通读/快照读和当前读都不阻塞,update和delete也不阻塞,只会阻塞insert操作,具体如下:
1、比如:update的20-25之间的22,UPDATE t SET d=999 WHERE id=22
会阻塞insert前一个记录到后一个记录之间的间隙(20,25)—注意:这里针对是id在这个区间
2、比如:update25以后的记录,UPDATE t SET d=999 WHERE id=66
会阻塞(25,66],(66,+∞]—注意:这里针对是id在这个区间
2、update有唯一索引且存在的记录
主事务操作:UPDATE t SET d=999 WHERE id=15
其他事务只会阻塞当前行的相关操作:
- select当前读:
SELECT * FROM t WHERE id=15 LOCK IN SHARE MODE/FOR UPDATE
UPDATE t SET d=999 WHERE id=15
DELETE from t WHERE id=15
不阻塞insert操作(除了当前id=15以外的),不会加上间隙锁;不阻塞普通读/快照读
三、update/delete有普通索引的记录
说明:delete/update操作结果其实是一样的,所以只专门讲解update即可,delete不再累赘描述
前提:这里针对的是c字段这个普通索引,也就是可能重复,一个索引对应多条记录
1、update有普通索引且不存在的记录
这个和唯一索引的区别其实差不多
select普通读/快照读和当前读都不阻塞,update和delete也不阻塞,只会阻塞insert操作,具体如下:
1、如果update的20-25之间的22,UPDATE t SET d=999 WHERE c=22
只会阻塞insert前一个记录到后一个记录之间的间隙[20,25)—注意:这里针对是c字段在这个区间,和上面唯一索引不同的是包括了20
2、如果update25以后的记录,UPDATE t SET d=999 WHERE c=66
会阻塞(25,66],(66,+∞]—注意:这里针对是c字段在这个区间
2、update有普通索引且存在的记录
首先在原来的数据上再插入几条数据,只是为了说明普通索引可能会有一条索引对应多条记录的情况:比如索引25对应三条实体记录,分别是id为25,30,35
INSERT INTO t
VALUES
( 30, 25, 30 ),
( 35, 25, 35 );
主事务操作:UPDATE t SET d=999 WHERE c=25
其他事务会阻塞c字段索引25对应的所有记录(id=25/30/35)的相关操作:
- select当前读:
SELECT * FROM t WHERE id=25/30/35 LOCK IN SHARE MODE/FOR UPDATE
UPDATE t SET d=999 WHERE id=25/30/35
DELETE from t WHERE id=25/30/35
- 会阻塞insert所有索引c字段设置为(20,25]和(25,30)之间的插入,如:
INSERT INTO t VALUES ( 100, 25, 30 )
;INSERT INTO t VALUES ( 100, 22, 30 )
;INSERT INTO t VALUES ( 100, 27, 30 )
;
不阻塞普通读/快照读
四、select lock in share mode/for update没有索引的记录
1、select lock in share mode
select lock in share mode
不会阻塞select lock in share mode
操作,其他跟第一点的update/delete相同,也就是读锁不会阻塞读锁,只会阻塞写锁
-
会阻塞当前读中的for update,不会阻塞lock in share mode,不会阻塞普通/快照读
-
会阻塞update已经存在的记录,不会阻塞update不存在的记录
-
会阻塞delete已经存在的记录,不会阻塞delete不存在的记录
-
会阻塞insert
跟第一点不一样的就是不会阻塞select lock in share mode
2、select for update
select for update
阻塞的结果跟第一点的update/delete一模一样,因为本质也是一样的,全表加锁,并且写锁会阻塞读锁和写锁
-
会阻塞当前读(for update/lock in share mode ),不会阻塞普通/快照读
-
会阻塞update已经存在的记录,不会阻塞update不存在的记录
-
会阻塞delete已经存在的记录,不会阻塞delete不存在的记录
-
会阻塞insert
五、select lock in share mode/for update有唯一索引的记录
对于select for update
,结果跟update/delete一模一样,也就是跟第二点一样;
对于select lock in share mode
,跟第二点不一样的就是不会阻塞select lock in share mode
六、select lock in share mode/for update有普通索引的记录
对于select for update
,结果跟update/delete一模一样,也就是跟第三点一样;
对于select lock in share mode
,跟第三点不一样的就是不会阻塞select lock in share mode
特别的:
除了上面的规则外,有个特例,
SELECT id,c FROM t WHERE c=5 LOCK IN SHARE MODE
这个查询使用覆盖索引,并不需要访问主键索引(也就是不需要再通过主键索引去回表查询其他字段的值),所以主键索引上没有加任何锁,所以可以执行这个操作UPDATE t SET d=2232 WHERE c=5
而对于上面第五点的唯一主键索引,会锁住整行,因为聚簇索引存储了整行记录
七、insert操作
比如INSERT INTO t(id, c, d) VALUES (66, 10, 12)
,这里会阻塞id=66这一行,别的地方无法insert id=66这个操作;
由于c字段上有索引,所以也会阻塞c字段索引对应的update/delete操作,比如UPDATE t SET d=66 WHERE c=10
;不会阻塞c=10字段对应的insert,比如INSERT INTO t(id, c, d) VALUES (666, 10, 12)