MySQL锁机制（一文读懂）

最新推荐文章于 2024-11-12 11:41:10 发布

云想念丶

最新推荐文章于 2024-11-12 11:41:10 发布

阅读量1.8k

点赞数 56

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_44985305/article/details/139244839

版权

MySQL Lock

背景
锁的类型
Innodb下锁的实现方式
Innodb行锁的实现算法
死锁
- 死锁的解决方案

背景

数据库在并发场景下，就会需要对某些内部共享资源进行加锁，来满足数据的一致性和完整性需求。这也是数据库与文件系统的最大区别。针对 MySQL.Innodb引擎来说，共享资源并不是指行数据，也包括缓冲区内的数据页列表、数据页缓存等。

锁的类型

Innodb锁的类型分为两种：共享锁（S Lock）和排他锁（X Lock）。
共享锁：允许多个事务同时读一行数据。可以多个事务同时持有，互不阻塞
排他锁：允许一个事务更新一行数据。当有事务持有某个资源的排他锁时，其他事务尝试获取S锁/X锁都会阻塞
下面是 S 和 X 锁的兼容性图表

但在高并发情况下，两种行锁带来的粒度太大。所以 Innodb 引擎提供了另外两种更细粒度锁：意向共享锁和意向排他锁。
在当前读的情况下，都是锁的行数据，也就是行锁。但 Innodb 的内存数据类型在指向行数据之前，还有表、页、行，最下级才是行级粒度，所以偏向锁就是加在表、页上。
当一个事务已经获取了行的共享锁（SL），此时另外一个事务想要获取该行的 XL。那么就会先获取当前行所属的当前表、页偏向排他锁，然后再对行记录上 X 锁，如果任何一部分获取不成功，都要等更粗粒度的锁获取完成。

Innodb下锁的实现方式

行锁（Record Lock）

Innodb 下对某一行记录上锁

表锁（Table Lock）

Innodb 优化器在认为加表锁比加行锁更有效时就会选择加表锁

表自增锁

Innodb 的表会提供一个自动自增的选择，会绑定一个自增器。在表选择了自增时，事务插入数据会先对这个自增器进行加锁，但在获取好数据之后就会自动释放，而非等到事务结束。
这种虽然已经进行了部分优化，但是还是无法解决一个事务 bulk insert的操作，因为一行行记录要一次次加锁，获取锁和释放锁都会带来巨大的开销。
在 MySQL 5.1.22 版本开始，Innodb 引擎提供了一个内存级别的互斥量来优化大批量场景下的插入自增，可以使用 innodb_autoinc_lock_mode 来设置自增锁的获取模式。详细描述如下：
值选项

自增锁配置描述

Innodb行锁的实现算法

Record Lock

都是选择锁在索引上，如果Innodb的表没有建立任何索引，那么就会选择隐藏的列主键来进行锁定

Gap Lock

中文名为间隙锁，意思是按照范围进行锁定，是当前表中索引列存在的数据之间的范围

Next-Key Lock

下一键锁，是 Gap Lock 和 Record Lock 的结合，在 RR 隔离级别下，Innodb查询时会使用该类型锁。Next-Key Lock会对范围+行记录上锁，而 Gap Lock 只会对范围上锁。举个例子：假如一个索引有 10，11， 13，20 这四个值，那么Next-Key Lock 的锁定范围为：

（-∞,10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

但是，针对不同的索引列，Next-Key Lock 的效果却大不相同。针对

唯一索引&非范围查询

表结构：create table t (a INT PRIMARY KEY)
当索引为唯一索引时，并且查询为非范围查询时，Next-Key Lock 就会降级为 Record Lock，因为唯一索引已经自带了唯一性约束校验，不需要再额外加一层 Next-Key 的唯一性约束校验（在非范围查询条件下，不会加 Gap Lock，就剩下了一个 Record Lock 和Next-Key Lock 自带的唯一性约束校验）
操作Demo
这个时候事务A和B都能执行成功

辅助索引（非聚簇索引）& 非范围查询

表结构：create table t (a INT, b INT, PRIMARY KEY(a), KEY (b))

insert into t select 1,1;
insert into t select 3,1;
insert into t select 5,3;
insert into t select 7,6;
insert into t select 10,8;

如果是一个表中既有主键索引，又有非主键索引。

SELECT * FROM t WHERE b = 3 FOR UPDATE;

因为这个表既存在聚簇索引，又存在非聚簇索引，所以要分开加锁。

聚簇索引 a 列值为 5 的 Record 锁
非聚簇索引下 b 列的 Next-Key Lock 和下一个键值的 Gap Lock
- (1,3]
- (3,6)

死锁

死锁是两个或两个以上事务发生资源的争抢，造成互相等待对方释放资源的现象。

死锁的解决方案

锁超时

解决死锁的最简单方案就是锁超时，可以设置一个锁等待的最长时间，Innodb 参数 innodb_lock_wait_timeout支持设置锁等待的超时时间，如果事务等待锁超过这个时间，就会抛出异常。但这种机制也会导致一个问题，因为这种场景完全是按照 FIFO 的算法来实现，如果后置事务比较大，此时 undoLog 也会比较多，会造成额外的性能消耗。

死锁检测机制

Innodb 提供了一种死锁检测机制。首先会尝试维护一个wait for gragh（等待图标），在事务尝试进行等待锁资源时，会去检测是否存在两个事务在互相等待对方获取的资源场景，如果存在，就会选择回滚事务较小的（修改/插入的行数较少等评判因素）。

死锁问题的排查思路

查询引擎状态信息

SHOW ENGINE INNODB STATUS
该命令可以查看当前活跃的事务（包含等待锁资源），以及最近一次死锁的记录。

查询事务表

在 InnoDB 1.0 版本之后，INFORMATION_SCHEMA 库下添加了 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。通过这三张表，可以更完整地监控到当前事务状态，并分析可能潜在的死锁问题。
INNODB_TRX 表说明：

INNODB_LOCKS 表说明：
innodb-locks
INNODB_LOCK_WAITS 表说明：