摘要本文系统梳理了MySQL数据库锁机制的核心知识体系从全局锁、表级锁到行锁三个粒度展开。目录前置基础MySQL 共享锁 (S 锁) vs 独占锁 (X 锁) 完整区别Mysql中的存储引擎一、全局锁全库锁1. 作用范围2. 使用场景3. 语法实操二、表级锁锁定整张数据表表锁1. 表共享读锁 LOCK TABLE 表名 READ2. 表排他写锁 LOCK TABLE 表名 WRITE元数据锁MDL 两种模式共享 / 排他兼容规则1. MDL 共享锁读 MDL2. MDL 排他锁写 MDLMDL 等待队列优先级意向锁为什么需要意向锁三、行锁InnoDB 行锁分类前置基础锁的定义在并发访问时解决数据的一致性有效性问题。MySQL 共享锁 (S 锁) vs 独占锁 (X 锁) 完整区别共享锁 S (Shared Lock)又称读锁多个事务可以同时加 S 锁大家一起读数据互不干扰 加锁语句SELECT ... LOCK IN SHARE MODE;独占锁 X (Exclusive Lock)又称写锁同一时间只能有 1 个事务持有 X 锁只要有人拿了 X 锁其他事务不管读、写都要等 触发场景UPDATE / DELETE / INSERT / SELECT ... FOR UPDATE;共享锁 S读读兼容读写互斥多人可读但谁都不能改独占锁 X写写互斥读写互斥只有持有者能操作其他人全部等待。Mysql中的存储引擎MyISAM 引擎只支持表锁不支持行锁并发差已淘汰。InnoDB 引擎手动显式表锁LOCK TABLES xxx READ / WRITE隐式触发表锁无索引更新、DDL 语句、分区操作配套表级辅助锁意向锁 IS/IX配合行锁使用不是真正表锁MyISAM 只支持表锁不支持行锁并发差现在基本淘汰InnoDB 支持行锁、表锁、意向锁、全局锁支持事务、MVCC线上主流引擎锁的粒度从小到大行锁 表锁 全局锁粒度越小并发越高实现越复杂。一、全局锁全库锁1. 作用范围锁定整个 MySQL 实例所有数据库锁定期间所有库读写阻塞。2. 使用场景全库逻辑备份mysqldump防止备份过程数据变更导致快照不一致。3. 语法实操加全局读锁所有会话只能读不能写 / 改FLUSH TABLES WITH READ LOCK; -- 加全局锁 UNLOCK TABLES; -- 释放全局锁效果当前会话可读可查其他会话只能 SELECT 查询INSERT/UPDATE/DELETE/DDL 全部阻塞等待释放锁释放断开当前数据库连接锁自动释放。二、表级锁锁定整张数据表分三类表锁元数据锁意向锁表锁1. 表共享读锁 LOCK TABLE 表名 READLOCK TABLE student READ;当前持有锁会话只能 SELECT不能 INSERT/UPDATE/DELETE写操作直接报错其他会话可以正常 SELECT所有写操作阻塞等待锁释放释放UNLOCK TABLES;/ 断开连接2. 表排他写锁 LOCK TABLE 表名 WRITELOCK TABLE student WRITE;当前持有锁会话可读、可写、可 DDL完全不受限其他会话读、写全部阻塞排队释放同上元数据锁MDL 全称 Metadata Lock元数据 表结构信息表名、字段、索引、约束、存储引擎等。 当一个事务访问某张表时MySQL 会自动给这张表加一把MDL 元数据锁作用保证事务执行期间表结构不能被别人修改 / 删除防止 SQL 执行一半表结构变了引发数据错乱。MDL 两种模式共享 / 排他兼容规则1. MDL 共享锁读 MDL所有普通 DML 语句自动获取SELECT / INSERT / UPDATE / DELETE兼容规则多个事务可以同时持有共享 MDL互不阻塞限制其他会话不能执行 DDLDDL 要排他 MDL互斥阻塞2. MDL 排他锁写 MDL所有修改表结构的 DDL 语句获取ALTER TABLE、DROP TABLE、RENAME、ADD INDEX、TRUNCATE兼容规则和任何 MDL 锁都互斥只要有一个事务持有共享 MDLDDL 就会阻塞排队 只要 DDL 拿到排他 MDL后面所有查询 / 更新全部阻塞。MDL 等待队列优先级MDL 共享锁DML 查询 / 更新之间天然兼容没有其他等待队列时事务 A 持有共享 MDL事务 B 直接拿到共享 MDL正常执行互不阻塞。MDL 排他锁ALTER/DROP和所有 MDL 锁互斥等待队列里一旦存在等待 MDL-X 的 DDL后续所有 DML 申请共享 MDL 全部阻塞DDL 请求优先级更高华为云社区。场景 1只有事务 A 持有共享 MDL无任何 DDL 排队事务 A开启事务查询表持有共享 MDL不提交事务 B执行普通 SELECT/UPDATE申请共享 MDL结果可以正常获取不阻塞原因共享 MDL 与共享 MDL 兼容不需要等待两个事务并发执行。 这个场景下不会出现雪崩业务正常读写。场景 2事务 A 持有共享 MDL → 事务 B 执行 ALTER 排队等 MDL-X → 再来事务 C 申请共享 MDL线上雪崩场景执行顺序ABEGIN; SELECT * FROM t; 持有共享 MDL长时间不 commitBALTER TABLE t ADD COLUMN xxx; 申请 MDL 排他锁与 A 的共享 MDL 互斥进入等待队列首位CSELECT / UPDATE申请共享 MDL结果事务 C拿不到共享 MDL直接阻塞底层原理MySQL MDL 等待队列遵循写锁优先机制 队列里已经排了一个等待 MDL-XDDL的请求后续所有新的共享 MDL 请求必须排队等前面的 DDL 先拿到锁 哪怕共享锁之间本身兼容只要队列存在待执行的排他锁请求后面全部堵住最终连接打满、业务雪崩稀土掘金。意向锁意向锁是InnoDB 表级标记锁配合行锁一起自动生成不需要手动写 SQL。 作用快速判断「这张表里有没有行锁」避免遍历全表所有行锁提升锁冲突判断速度。两种意向锁IS 意向共享锁事务准备给某行加S 行共享锁SELECT ... LOCK IN SHARE MODE先给表上加 IS。读锁IX 意向排他锁事务准备给某行加X 行排他锁UPDATE / DELETE / SELECT ... FOR UPDATE先给表上加 IX。(写锁)IS 和 IX 互相完全兼容多个事务可以同时持有 IS、IX;IS 能和表读锁 S 共存IX 和表读 / 写锁都互斥为什么需要意向锁假设没有意向锁 当你执行LOCK TABLE t WRITE想加整张表排他锁时数据库必须遍历表每一行检查是否存在行锁百万行表性能极差。有了意向锁 只看表一级是否存在 IS / IX 标记表存在 IS/IX → 说明有事务持有行锁不能加表锁直接阻塞表无任何意向锁 → 表内无行锁可以直接加表锁。cho一句话意向锁 行锁和表锁之间的快速判断标记。三、行锁只有InnoDB 引擎支持行锁MyISAM 只有表锁行锁基于索引生效更新条件命中索引 → 只锁匹配的行更新条件无索引 / 索引失效 → 扫描全表所有行加锁等价锁表行锁只在事务内生效事务提交 / 回滚才释放。InnoDB 行锁分类按锁模式划分S/X读写锁1. 共享行锁 S读锁加锁语句SELECT ... LOCK IN SHARE MODE;规则多个事务可同时持有同一行 S 锁读读兼容、读写互斥限制持有 S 锁的事务不能修改该行数据其他事务无法给该行加 X 锁2. 排他行锁 X写锁自动加锁UPDATE / DELETE / INSERT手动悲观锁SELECT ... FOR UPDATE;规则一行同一时间只能 1 个事务持有 X 锁读写全互斥别人不管读 / 写都阻塞S/X 兼容表表格已有锁申请 S 共享锁申请 X 排他锁S✅ 兼容❌ 阻塞X❌ 阻塞❌ 阻塞按锁算法划分RR 可重复读隔离级别独有RC 无间隙锁1. Record Lock 记录锁只锁定索引上的单行记录不锁间隙。 触发条件唯一索引等值查询精准匹配到数据示例WHERE id 1id 主键仅锁住 id1 这一行。2. Gap Lock 间隙锁锁定两条索引记录中间的空白区间不锁实际存在的数据行。 作用防止幻读阻止其他事务在间隙内插入新数据。 示例表 id 有 1、5WHERE id 5锁住间隙(1,5)无法插入 id2/3/4。3. Next-Key Lock 临键锁默认行锁算法 Record 记录锁 Gap 间隙锁锁定「当前记录 左侧间隙」范围查询、普通索引等值查询默认走临键锁唯一索引等值精准命中数据时会自动降级为 Record 锁消除间隙锁定。RC读已提交隔离级别关闭 Gap 间隙锁、Next-Key 锁只有 Record 记录锁锁冲突更少但存在幻读。InnoDB 的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类。重点 行锁本质是锁「索引」不是锁物理数据行InnoDB 是索引组织表所有数据都依附 B 树索引存储InnoDB不会直接锁定磁盘上的物理记录而是给这条记录对应的索引条目加锁如果你的UPDATE/DELETE条件没有索引 / 索引失效MySQL 找不到精准索引项只能全表扫描所有索引给全部索引加行锁等价于锁住整张表行锁退化表锁。
MYSQL中的锁
摘要本文系统梳理了MySQL数据库锁机制的核心知识体系从全局锁、表级锁到行锁三个粒度展开。目录前置基础MySQL 共享锁 (S 锁) vs 独占锁 (X 锁) 完整区别Mysql中的存储引擎一、全局锁全库锁1. 作用范围2. 使用场景3. 语法实操二、表级锁锁定整张数据表表锁1. 表共享读锁 LOCK TABLE 表名 READ2. 表排他写锁 LOCK TABLE 表名 WRITE元数据锁MDL 两种模式共享 / 排他兼容规则1. MDL 共享锁读 MDL2. MDL 排他锁写 MDLMDL 等待队列优先级意向锁为什么需要意向锁三、行锁InnoDB 行锁分类前置基础锁的定义在并发访问时解决数据的一致性有效性问题。MySQL 共享锁 (S 锁) vs 独占锁 (X 锁) 完整区别共享锁 S (Shared Lock)又称读锁多个事务可以同时加 S 锁大家一起读数据互不干扰 加锁语句SELECT ... LOCK IN SHARE MODE;独占锁 X (Exclusive Lock)又称写锁同一时间只能有 1 个事务持有 X 锁只要有人拿了 X 锁其他事务不管读、写都要等 触发场景UPDATE / DELETE / INSERT / SELECT ... FOR UPDATE;共享锁 S读读兼容读写互斥多人可读但谁都不能改独占锁 X写写互斥读写互斥只有持有者能操作其他人全部等待。Mysql中的存储引擎MyISAM 引擎只支持表锁不支持行锁并发差已淘汰。InnoDB 引擎手动显式表锁LOCK TABLES xxx READ / WRITE隐式触发表锁无索引更新、DDL 语句、分区操作配套表级辅助锁意向锁 IS/IX配合行锁使用不是真正表锁MyISAM 只支持表锁不支持行锁并发差现在基本淘汰InnoDB 支持行锁、表锁、意向锁、全局锁支持事务、MVCC线上主流引擎锁的粒度从小到大行锁 表锁 全局锁粒度越小并发越高实现越复杂。一、全局锁全库锁1. 作用范围锁定整个 MySQL 实例所有数据库锁定期间所有库读写阻塞。2. 使用场景全库逻辑备份mysqldump防止备份过程数据变更导致快照不一致。3. 语法实操加全局读锁所有会话只能读不能写 / 改FLUSH TABLES WITH READ LOCK; -- 加全局锁 UNLOCK TABLES; -- 释放全局锁效果当前会话可读可查其他会话只能 SELECT 查询INSERT/UPDATE/DELETE/DDL 全部阻塞等待释放锁释放断开当前数据库连接锁自动释放。二、表级锁锁定整张数据表分三类表锁元数据锁意向锁表锁1. 表共享读锁 LOCK TABLE 表名 READLOCK TABLE student READ;当前持有锁会话只能 SELECT不能 INSERT/UPDATE/DELETE写操作直接报错其他会话可以正常 SELECT所有写操作阻塞等待锁释放释放UNLOCK TABLES;/ 断开连接2. 表排他写锁 LOCK TABLE 表名 WRITELOCK TABLE student WRITE;当前持有锁会话可读、可写、可 DDL完全不受限其他会话读、写全部阻塞排队释放同上元数据锁MDL 全称 Metadata Lock元数据 表结构信息表名、字段、索引、约束、存储引擎等。 当一个事务访问某张表时MySQL 会自动给这张表加一把MDL 元数据锁作用保证事务执行期间表结构不能被别人修改 / 删除防止 SQL 执行一半表结构变了引发数据错乱。MDL 两种模式共享 / 排他兼容规则1. MDL 共享锁读 MDL所有普通 DML 语句自动获取SELECT / INSERT / UPDATE / DELETE兼容规则多个事务可以同时持有共享 MDL互不阻塞限制其他会话不能执行 DDLDDL 要排他 MDL互斥阻塞2. MDL 排他锁写 MDL所有修改表结构的 DDL 语句获取ALTER TABLE、DROP TABLE、RENAME、ADD INDEX、TRUNCATE兼容规则和任何 MDL 锁都互斥只要有一个事务持有共享 MDLDDL 就会阻塞排队 只要 DDL 拿到排他 MDL后面所有查询 / 更新全部阻塞。MDL 等待队列优先级MDL 共享锁DML 查询 / 更新之间天然兼容没有其他等待队列时事务 A 持有共享 MDL事务 B 直接拿到共享 MDL正常执行互不阻塞。MDL 排他锁ALTER/DROP和所有 MDL 锁互斥等待队列里一旦存在等待 MDL-X 的 DDL后续所有 DML 申请共享 MDL 全部阻塞DDL 请求优先级更高华为云社区。场景 1只有事务 A 持有共享 MDL无任何 DDL 排队事务 A开启事务查询表持有共享 MDL不提交事务 B执行普通 SELECT/UPDATE申请共享 MDL结果可以正常获取不阻塞原因共享 MDL 与共享 MDL 兼容不需要等待两个事务并发执行。 这个场景下不会出现雪崩业务正常读写。场景 2事务 A 持有共享 MDL → 事务 B 执行 ALTER 排队等 MDL-X → 再来事务 C 申请共享 MDL线上雪崩场景执行顺序ABEGIN; SELECT * FROM t; 持有共享 MDL长时间不 commitBALTER TABLE t ADD COLUMN xxx; 申请 MDL 排他锁与 A 的共享 MDL 互斥进入等待队列首位CSELECT / UPDATE申请共享 MDL结果事务 C拿不到共享 MDL直接阻塞底层原理MySQL MDL 等待队列遵循写锁优先机制 队列里已经排了一个等待 MDL-XDDL的请求后续所有新的共享 MDL 请求必须排队等前面的 DDL 先拿到锁 哪怕共享锁之间本身兼容只要队列存在待执行的排他锁请求后面全部堵住最终连接打满、业务雪崩稀土掘金。意向锁意向锁是InnoDB 表级标记锁配合行锁一起自动生成不需要手动写 SQL。 作用快速判断「这张表里有没有行锁」避免遍历全表所有行锁提升锁冲突判断速度。两种意向锁IS 意向共享锁事务准备给某行加S 行共享锁SELECT ... LOCK IN SHARE MODE先给表上加 IS。读锁IX 意向排他锁事务准备给某行加X 行排他锁UPDATE / DELETE / SELECT ... FOR UPDATE先给表上加 IX。(写锁)IS 和 IX 互相完全兼容多个事务可以同时持有 IS、IX;IS 能和表读锁 S 共存IX 和表读 / 写锁都互斥为什么需要意向锁假设没有意向锁 当你执行LOCK TABLE t WRITE想加整张表排他锁时数据库必须遍历表每一行检查是否存在行锁百万行表性能极差。有了意向锁 只看表一级是否存在 IS / IX 标记表存在 IS/IX → 说明有事务持有行锁不能加表锁直接阻塞表无任何意向锁 → 表内无行锁可以直接加表锁。cho一句话意向锁 行锁和表锁之间的快速判断标记。三、行锁只有InnoDB 引擎支持行锁MyISAM 只有表锁行锁基于索引生效更新条件命中索引 → 只锁匹配的行更新条件无索引 / 索引失效 → 扫描全表所有行加锁等价锁表行锁只在事务内生效事务提交 / 回滚才释放。InnoDB 行锁分类按锁模式划分S/X读写锁1. 共享行锁 S读锁加锁语句SELECT ... LOCK IN SHARE MODE;规则多个事务可同时持有同一行 S 锁读读兼容、读写互斥限制持有 S 锁的事务不能修改该行数据其他事务无法给该行加 X 锁2. 排他行锁 X写锁自动加锁UPDATE / DELETE / INSERT手动悲观锁SELECT ... FOR UPDATE;规则一行同一时间只能 1 个事务持有 X 锁读写全互斥别人不管读 / 写都阻塞S/X 兼容表表格已有锁申请 S 共享锁申请 X 排他锁S✅ 兼容❌ 阻塞X❌ 阻塞❌ 阻塞按锁算法划分RR 可重复读隔离级别独有RC 无间隙锁1. Record Lock 记录锁只锁定索引上的单行记录不锁间隙。 触发条件唯一索引等值查询精准匹配到数据示例WHERE id 1id 主键仅锁住 id1 这一行。2. Gap Lock 间隙锁锁定两条索引记录中间的空白区间不锁实际存在的数据行。 作用防止幻读阻止其他事务在间隙内插入新数据。 示例表 id 有 1、5WHERE id 5锁住间隙(1,5)无法插入 id2/3/4。3. Next-Key Lock 临键锁默认行锁算法 Record 记录锁 Gap 间隙锁锁定「当前记录 左侧间隙」范围查询、普通索引等值查询默认走临键锁唯一索引等值精准命中数据时会自动降级为 Record 锁消除间隙锁定。RC读已提交隔离级别关闭 Gap 间隙锁、Next-Key 锁只有 Record 记录锁锁冲突更少但存在幻读。InnoDB 的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类。重点 行锁本质是锁「索引」不是锁物理数据行InnoDB 是索引组织表所有数据都依附 B 树索引存储InnoDB不会直接锁定磁盘上的物理记录而是给这条记录对应的索引条目加锁如果你的UPDATE/DELETE条件没有索引 / 索引失效MySQL 找不到精准索引项只能全表扫描所有索引给全部索引加行锁等价于锁住整张表行锁退化表锁。
