MySQL数据库加锁实战总结

最新推荐文章于 2024-06-24 09:04:55 发布

Apple_Web

最新推荐文章于 2024-06-24 09:04:55 发布

阅读量4k

点赞数 5

分类专栏：数据库

本文链接：https://blog.csdn.net/belongtocode/article/details/111745146

版权

数据库专栏收录该内容

32 篇文章 0 订阅

订阅专栏

文章目录

基础

默认拥有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

而对于上面第五点的唯一主键索引，会锁住整行，因为聚簇索引存储了整行记录