MySQL-锁

AE86LW
174 阅读10分钟

MySQL-锁类型

锁类型粒度作用场景
表级锁整张表MyISAM、LOCK TABLES、DDL
意向锁（IS/IX）表级InnoDB 协调行锁冲突
行级锁（Record/Gap/Next-Key）单行或索引间隙InnoDB 并发读写、避免脏读、幻读
元数据锁（MDL）表级防止 DML 与 DDL 冲突

全局锁

常用做全表逻辑备份

实现方式
1. set global readonly = True
2. mysqldump -single-transcation
3. flush table with read lock

优先使用mysqldump -single-transcation支持事务MVCC,不阻塞写，可以正常更新数据 如果引擎不是innodb，考虑使用flush table with read lock或者替换底层存储引擎 绝不使用set global readonly = true，因为readonly没有异常处理机制会导致全库不可写。而flush这种方式有异常处理机制会自动回滚。readonly一般用作主备区分，备库开启readonly，只接受主库复制线程的数据，而不接受业务写入。

表级锁

表锁

lock tables T write 读锁，共享读锁 只有当前会话可以读写，其他会话不能写，只能读 lock tables T read 写锁，独占锁 只有当前会话可以读写，其他会话不能读写

MDL元数据锁

MDL锁获取隐式的，5.5版本加上的

MDL读锁：访问了表就会自动加上MDL读锁: select update insert delete等

MDL写锁：修改了表结构就会自动加上MDL写锁，alter drop

MDL只有读锁与读锁不互斥，读写，写读，写写都互斥

如何避免MDL锁阻塞：

避免长事务锁霸：在innodb_trx表找到长事务kill，不阻塞现有事务。业务代码中及时提交长事务 在业务低峰期修改表结构避免阻塞。 但某些热点表频繁更新，事务可能无法kill，使用percona工具pt-online-schema-change实现在线更新

  • 该工具不直接执行 ALTER TABLE，而是：

    • 创建一张影子表；
    • 对影子表执行变更；
    • 用触发器实时同步数据；
    • 最后原子替换表。

  • 期间对原表只持有极短的 MDL 写锁（瞬时），不会长时间阻塞业务请求。

pt-online-schema-change \
  --user=root \
  --password=yourpass \
  --host=127.0.0.1 \
  --port=3306 \
  --alter "ADD COLUMN status TINYINT DEFAULT 0" \
  D=mydb,t=orders \
  --execute

行锁（Record/Gap/Next-Key）

Record

查找条件走索引且是等值匹配的情况才会获取行锁 防止脏读和不可重复读

读锁|S锁|乐观锁
select * from T where T.id= 100 for share 
写锁|X锁|悲观锁
update T set T.xxx=xxx where T.id = 100
插入数据以后加写锁
insert into T(id) values(100)

Gap

查询条件走索引+查询不存在的值=纯Gap锁

id=100不存在，存在50 150
select * from T where T.id=100 for update

id=50,150不存在
select * from T where T.id between 50 and 150


上述两条SQL会锁住(50,150)的间隙，但不会有任何行锁
目的是防幻读，必须锁住间隙，innodb B+tree不能锁点，只能锁区间，
不可能为了每个可能的值单独维护锁信息，防止其他并发事务插入造成第二次查询的数据幻影

Next-Key 临键锁（Record + Gap）

查询条件走索引+ 值存在+范围查找 = 间隙锁 
锁住命中的索引值以及前置间隙，左开右闭区间
如果没有走索引那么退化为表锁
防止幻读

以下查询都会触发临键锁
-- 命中的记录及其前面的间隙
select * from users where age between  20 and 30;
SELECT * FROM users WHERE age > 20 FOR UPDATE;
SELECT * FROM users WHERE age <= 30 FOR UPDATE;
--锁住所有 `name` 以 A 开头的记录 + 相关间隙
SELECT * FROM users WHERE name LIKE 'A%' FOR UPDATE;
-- 锁住(prev, 10]、(10, 20]、(20, 30]
SELECT * FROM users WHERE age IN (10, 20, 30) FOR UPDATE;
-- 锁住 NULL 值所在记录及其前间隙
SELECT * FROM users WHERE deleted_at IS NULL FOR UPDATE;
-- 锁住(prev, 80]、(80, 100]
update user u set u.username="wangwu" where u.age between 80 and 100
-- 锁住(prev, 80]、(80, 100]
delete from user u where u.age between 80 and 100

临键锁解决锁定读读 (update，delete，insert, select for update) 的索引幻读问题, InnoDB 在所有需要扫描或定位索引的写操作或者锁定读操作上加临键锁 Gap Lock 的本质是锁定索引记录之间的物理空隙，利用索引列的有序且连续的特点防止插入幻影行。但如果过滤列是非索引列，那么它在存储层面是无序排列的，InnoDB无法感知字段值之间的有序关系和插入点位置，因此无法在非索引列上加 Gap Lock, 但innodb也会根据聚簇索引来加间隙锁，只不过会退化为全表扫描，锁住所有的gap与行，退化为表锁，进而消除幻读。

意向锁（IS/IX）

意向锁的目的是对锁粒度变小，采用分层解耦的思想，减少锁带来的性能开销

表级意向锁

  • IS（意向共享锁） ：我打算对表T做共享行锁（S-lock）。

  • IX（意向排它锁） ：我打算对表T做排它行锁（X-lock）。

意向锁解决的问题是加速已有行锁与想申请表级锁之间的锁冲突检测，由于后面申请的表锁不知道表T中有没有行锁，导致需要全表扫描查询是否有锁冲突。 因为行锁和表锁在不同的维度，innodb锁的哈希桶没有维护表中哪些行加了锁，所以只能通过全表遍历的方式来检测每一个哈希键（主键/索引值）的value（是一个锁队列lock queue，持有哪些事务的锁）到底有没有锁，效率很低。所以对普通的行锁，innodb额外引入了意向锁标识表中已有行锁来和后面事务想加的表锁快速做判断O(1)而不用全表扫描O(n)。

多粒度锁意向锁兼容性

已有 \ 请求IS (意向共享)IX (意向排它)S (行级共享)X (行级排它)
IS✓ 两者都只是声明“我会做读锁” 没有实际读或写，互不干扰。✓ 已有读意图，新请求写意图也可共存只是先声明“我会写”，不影响旧的读意图。✓ 读意图 + 读锁也兼容读意图不妨碍具体行上的共享读。✕ 读意图vs排它写锁冲突已有人要读，就不能让另一个事务独占写了。
IX✓ 已有写意图，新请求读意图也可共存都只是意图，不是真实行锁。✓ 写意图 + 写意图可共存都只是“打算写”，无实际写时段冲突。✕ 写意图vs读锁冲突已有人要写，同一表中又有人要读，会导致读到不稳定数据。✕ 写意图vs写锁冲突已有写意图时，不允许别人对同表任何行做真写锁。
S✓ 已有行级读锁 + 新请求意向共享读锁本身已锁行，意图锁只声明读，不冲突。✕ 已有行级读锁 + 写意图冲突有人正在读，不能让别人即将写。✓ 读锁 + 读锁兼容多个读可以并行。✕ 读锁 + 写锁冲突读时不能让别人写。
X✕ 已有行级排它锁 + 意向共享冲突排它锁已独占行，任何意图锁都要等。✕ 已有排它锁 + 写意图冲突排它锁已占全行，不允许别人再宣称要写。✕ 排它锁 + 读锁冲突排它已独占行，别人不能读也不能写。✕ 排它锁 + 写锁冲突只有自己能写，任何其他写锁都冲突。

插入意向锁

解决gap锁锁住整个区间，锁粒度太大，造成并发性能下降的问题，

在MySQL5.7版本没有插入意向锁机制之前，插入数据会有一个独占的gap(50,150)
存在50，150两条记录 id为索引
insert into T (id) values (100)

后面引入插入意向锁取代了gap锁，改为插入意向锁+行锁，使用“先意向，再加锁”的分层策略，避免并发场景插入gap区间的大范围阻塞。 插入意向锁基本

插入意向锁兼容性表格：

已持有的锁 (Held Lock)SQL 示例（持锁）SQL 示例（请求插入意向锁 I）是否兼容说明
意向共享锁 (IS)LOCK TABLES t READ;START TRANSACTION; INSERT INTO t(id) VALUES(5);✔表级读意图，不影响并发插入
意向独占锁 (IX)LOCK TABLES t WRITE;START TRANSACTION; INSERT INTO t(id) VALUES(6);✔表级写意图，不影响并发插入
行级共享锁 (S)START TRANSACTION; SELECT * FROM t WHERE id=4 LOCK IN SHARE MODE;INSERT INTO t(id) VALUES(5);✔锁住已存在记录，不影响插入间隙
行级独占锁 (X)START TRANSACTION; SELECT * FROM t WHERE id=4 FOR UPDATE;INSERT INTO t(id) VALUES(6);✔锁住已存在记录，不影响插入其他位置
普通间隙锁 (Gap Lock)START TRANSACTION; SELECT * FROM t WHERE id>4 AND id<7 LOCK IN SHARE MODE;INSERT INTO t(id) VALUES(5);✖间隙锁阻塞插入，防止幻读
插入意向锁 (I)START TRANSACTION; INSERT INTO t(id) VALUES(5);（未提交）INSERT INTO t(id) VALUES(6);✔多个插入意向锁可共存，提高并发性能
下一键锁 (Next-Key Lock)START TRANSACTION; SELECT * FROM t WHERE id>4 AND id<7 FOR UPDATE;INSERT INTO t(id) VALUES(6);✖锁住记录和前间隙，防止幻读

死锁

死锁是指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖

死锁在事务系统中不可避免

死锁处理：innodb在事务长时间获取不到锁的时候会开启死锁检测（有向图中检测环），如果检测到了，会将持有最少行级排他锁的事务回滚，被回滚的事务会收到 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction。

  • 可通过 SHOW ENGINE INNODB STATUS\G 查看最新死锁详情，并启用 innodb_print_all_deadlocks 来将所有死锁事件写入错误日志，以便线下诊断

死锁检测非常消耗CPU资源，极端场景下，热点行的更新可能会导致性能瓶颈,一般有两种处理方式。

1 关闭死锁检测

如果能保证程序绝对不会出现死锁，那么将死锁检测innodb_deadlock_detect = OFF关闭即可，完全依赖innodb_lock_wait_timeout参数进行兜底，但这个参数默认是50s，业务超时时间往往比这个时间要小很多，所以会出现大量超时，导致业务有损。

2 在数据库服务端控制并发度

如业务允许，可引入中间件或服务端限流，将热点行的更新请求排队处理，避免过多线程同时竞争同一行锁；对于无法自研的团队，也可考虑将热点逻辑拆分到多行（如“分片账户”）上，用随机选择来分散冲突。

死锁的产生有双重原因：

有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的。

如何最大程度避免死锁

  • 减少事务持有锁的时间

    避免长事务，拆分为可补偿的小事务

  • 按照固定顺序加锁

    所有事务中访问多张表或多行记录时，必须按相同的固定顺序（例如按主键、按表名字典序）来加锁。永远不要出现 A→B 和 B→A 两种顺序同时存在的情况，杜绝循环等待基础

  • 确保表索引合理，能充分利用索引

    避免全表扫描的大范围间隙锁或行锁

  • 降低隔离级别 隔离级别越高，加锁越多。对于不必强数据一致性的场景考虑降级为读提交

  • 并发度控制与排队

    引入中间件或服务端限流，将热点行的更新请求排队处理，避免过多线程同时竞争同一行锁

参考

《高性能MySQL-第四版》

《极客时间-MySQL45讲》

《MySQL技术内幕-InnoDB存储引擎-第二版》

avatar
文章
阅读
粉丝