MySQL锁机制三部曲：全局锁、表级锁、行级锁深度解析与实战

作者：DBA技术团队
适用版本：MySQL 5.7 / 8.0 / 8.4 LTS
存储引擎：InnoDB（默认和推荐）

引言：为什么需要锁？

在多用户并发访问数据库时，锁机制是保障**数据一致性（Consistency）和隔离性（Isolation）**的核心手段。MySQL提供了三个层次的锁，对应不同的并发场景：

-- 查看当前连接的默认存储引擎
SELECT @@default_storage_engine;
-- 确认是InnoDB，因为只有InnoDB支持行级锁

---

一、全局锁（Global Lock）

1.1 概念与作用

全局锁是粒度最大的锁，锁定整个MySQL实例，使数据库处于只读状态。此时：

• ✅ 允许SELECT查询
• ❌ 禁止INSERT/UPDATE/DELETE
• ❌ 禁止DDL操作（建表、改结构）
• ❌ 禁止事务提交

典型应用场景：全库逻辑备份（mysqldump）

1.2 SQL实战演示

场景A：加全局锁进行备份

-- ========== 会话A（运维会话）==========
-- 1. 加全局读锁（Flush Tables With Read Lock）
FLUSH TABLES WITH READ LOCK;

-- 2. 查看当前锁状态
SHOW PROCESSLIST;
-- 可以看到状态：Waiting for global read lock（被阻塞的会话）

-- 3. 查看锁定信息（MySQL 8.0）
SELECT * FROM performance_schema.metadata_locks 
WHERE OBJECT_TYPE = 'GLOBAL' AND LOCK_TYPE = 'SHARED';

-- 4. 执行备份命令（命令行）
-- $ mysqldump -uroot -p --all-databases > full_backup.sql

-- 5. 确认备份完成后释放锁
UNLOCK TABLES;

场景B：全局锁阻塞演示

-- ========== 会话A ==========
FLUSH TABLES WITH READ LOCK;
-- Query OK, 0 rows affected

-- ========== 会话B（业务会话）==========
-- 尝试插入数据
INSERT INTO test.users(name) VALUES('张三');
-- 状态：Waiting for global read lock（被阻塞）

-- ========== 会话C（监控会话）==========
-- 查看谁在等待全局锁
SELECT 
    r.object_schema, r.object_name, 
    r.thread_id AS waiting_thread,
    b.thread_id AS blocking_thread,
    r.owner_event_id AS waiting_event,
    b.owner_event_id AS blocking_event
FROM performance_schema.metadata_locks r
JOIN performance_schema.metadata_locks b ON r.object_schema = b.object_schema 
    AND r.object_name = b.object_name
WHERE r.lock_status = 'PENDING' 
    AND b.object_type = 'GLOBAL' 
    AND b.owner_thread_id != r.owner_thread_id;

-- 结果：显示会话B被会话A阻塞

1.3 全局锁的注意事项

-- 风险1：主库执行全局锁会导致业务停摆
-- 风险2：从库执行会导致主从复制延迟急剧增加

-- 现代替代方案：使用--single-transaction进行一致性备份（不锁库）
-- $ mysqldump -uroot -p --single-transaction --all-databases > backup.sql

-- 如果必须使用全局锁，建议设置超时（MySQL 8.0）
SET SESSION lock_wait_timeout = 10; -- 10秒超时

---

二、表级锁（Table Lock）

2.1 表级锁的分类

MySQL中有两种表级锁：

1. 表锁（Table Lock）：显式锁定，影响并发DML
2. 元数据锁（MDL, Metadata Lock）：隐式锁定，保护表结构

2.2 显式表锁实战

加锁与释放

-- ========== 会话A ==========
-- 1. 加表读锁（Table Read Lock）
-- 本会话和其他会话都可以读，但都不能写
LOCK TABLES orders READ;

-- 2. 加表写锁（Table Write Lock）
-- 仅本会话可读写，其他会话完全阻塞
LOCK TABLES orders WRITE; -- 会释放之前的READ锁

-- 3. 查看当前表锁情况
SHOW OPEN TABLES WHERE `Table` = 'orders' AND `Database` = 'test';
-- In_use列显示1表示被锁定

-- 4. 释放锁
UNLOCK TABLES;

锁冲突演示

-- 准备数据
CREATE TABLE IF NOT EXISTS inventory (
    id INT PRIMARY KEY,
    product_name VARCHAR(50),
    stock INT
) ENGINE=InnoDB;

INSERT INTO inventory VALUES (1, 'iPhone', 100), (2, 'MacBook', 50);

-- ========== 会话A：加写锁 ==========
LOCK TABLES inventory WRITE;
UPDATE inventory SET stock = stock - 1 WHERE id = 1;

-- ========== 会话B：尝试读 ==========
SELECT * FROM inventory WHERE id = 1;
-- 状态：Waiting for table lock（被阻塞）

-- ========== 会话C：尝试写 ==========
UPDATE inventory SET stock = stock - 1 WHERE id = 2;
-- 状态：Waiting for table lock（被阻塞）

-- 当会话A执行UNLOCK TABLES后，会话B和C才能继续

2.3 元数据锁（MDL）详解

MDL是自动隐式加锁的，无需显式操作，用于保护表结构不被并发修改破坏。

MDL的两种类型

MDL类型	触发场景	阻塞效果
MDL读锁	SELECT、DML语句	不阻塞其他SELECT，阻塞DDL
MDL写锁	ALTER TABLE、DROP TABLE	阻塞所有其他操作

实战： alter table导致的MDL阻塞

-- ========== 会话A：长事务 ==========
BEGIN;
SELECT * FROM users WHERE id = 1; -- 获取MDL读锁
-- 保持事务开启，不提交...

-- ========== 会话B：修改表结构 ==========
ALTER TABLE users ADD COLUMN age INT DEFAULT 0;
-- 状态：Waiting for table metadata lock
-- 原因：需要MDL写锁，但会话A持有MDL读锁

-- ========== 会话C：普通查询 ==========
SELECT * FROM users WHERE id = 2;
-- MySQL 8.0以前：可能被阻塞（MDL读锁排队在MDL写锁后）
-- MySQL 8.0：Online DDL优化，通常能执行

-- ========== 会话D：诊断MDL锁等待 ==========
-- 查看MDL锁等待链
SELECT 
    r.object_schema, r.object_name, 
    r.thread_id AS waiting_thread, 
    r.owner_event_id AS waiting_event,
    b.thread_id AS blocking_thread,
    b.owner_event_id AS blocking_event,
    r.lock_type AS waiting_lock,
    b.lock_type AS blocking_lock
FROM performance_schema.metadata_locks r
JOIN performance_schema.metadata_locks b ON r.object_schema = b.object_schema 
    AND r.object_name = b.object_name
WHERE r.lock_status = 'PENDING' 
    AND b.lock_status = 'GRANTED'
    AND b.owner_thread_id != r.owner_thread_id;

-- 查看具体SQL
SELECT THREAD_ID, SQL_TEXT 
FROM performance_schema.events_statements_current 
WHERE THREAD_ID IN (SELECT THREAD_ID FROM performance_schema.metadata_locks 
                    WHERE object_name = 'users' AND lock_status = 'PENDING');

-- 解决方案：终止长事务
KILL <blocking_thread_id>; -- 终止会话A

2.4 意向锁（Intention Lock）

意向锁是表级锁与行级锁的协调机制，由InnoDB自动维护。

-- ========== 意向锁演示 ==========
-- 会话A：对某行加排他锁（自动在表上加IX意向锁）
BEGIN;
SELECT * FROM inventory WHERE id = 1 FOR UPDATE;

-- 查看意向锁（MySQL 8.0）
SELECT 
    ENGINE_TRANSACTION_ID, OBJECT_NAME, 
    LOCK_TYPE, LOCK_MODE, LOCK_STATUS
FROM performance_schema.data_locks
WHERE OBJECT_TYPE = 'TABLE';
-- 结果：LOCK_MODE = IX（意向排他锁）

-- 会话B：尝试加表锁（被阻塞）
LOCK TABLES inventory READ;
-- 状态：Table lock wait timeout...
-- 原因：表上有IX锁，与表级S锁不兼容

意向锁兼容性：

• IS（意向共享锁） 与 表级S锁 兼容
• IX（意向排他锁） 与 表级S锁/X锁 都不兼容

---

三、行级锁（Row Lock）

行级锁是InnoDB的核心特性，只锁定被访问的具体行，并发度最高。

3.1 行锁的两种基本类型

共享锁（S锁，Shared Lock）

-- ========== 场景：读取并确保数据不被修改 ==========
-- 会话A：加共享锁（允许其他事务读，阻塞写）
BEGIN;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0也可使用：FOR SHARE

-- 会话B：可以加共享锁（兼容）
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- ✅成功

-- 会话C：尝试修改（需要X锁，被阻塞）
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 状态：Lock wait timeout exceeded

-- 会话D：查看行级锁等待
SELECT 
    r.object_schema, r.object_name, 
    r.thread_id AS waiting_thread,
    b.thread_id AS blocking_thread,
    r.lock_mode AS waiting_mode,
    b.lock_mode AS blocking_mode
FROM performance_schema.data_locks r
JOIN performance_schema.data_locks b 
    ON r.object_schema = b.object_schema 
    AND r.object_name = b.object_name
WHERE r.lock_status = 'WAITING' 
    AND b.lock_status = 'GRANTED';

排他锁（X锁，Exclusive Lock）

-- ========== 场景：修改数据（自动加X锁）==========
-- 会话A：加排他锁
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 1;
-- 自动对id=1的行加X锁

-- 或者显式加锁
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 会话B：任何锁请求都被阻塞
SELECT * FROM accounts WHERE id = 1 FOR SHARE; -- ❌等待

-- 会话C：快照读（Snapshort Read）可以执行，基于MVCC
SELECT * FROM accounts WHERE id = 1; -- ✅成功，读取undo log中的旧版本

3.2 行锁的算法实现

InnoDB实现了三种行锁算法：

1. 记录锁（Record Lock）

锁定索引记录本身。

-- 数据：id为主键，值为1, 3, 5, 7, 10
BEGIN;
SELECT * FROM accounts WHERE id = 5 FOR UPDATE;
-- 仅锁定id=5这一行（记录锁）

-- 验证：其他事务可操作id=3和id=7
-- 但不能操作id=5

2. 间隙锁（Gap Lock）

锁定索引记录之间的间隙，防止幻读。

-- ========== 间隙锁演示 ==========
-- 会话A：范围查询加间隙锁（REPEATABLE READ隔离级别下）
BEGIN;
SELECT * FROM accounts WHERE id > 3 AND id < 8 FOR UPDATE;
-- 锁定间隙：(3,5) 和 (5,7)

-- 会话B：尝试插入（被阻塞）
INSERT INTO accounts (id, user_id, balance) VALUES (4, 1004, 400);
-- ❌ Waiting for lock：id=4落在间隙(3,5)内

INSERT INTO accounts (id, user_id, balance) VALUES (6, 1006, 600);
-- ❌ Waiting for lock：id=6落在间隙(5,7)内

INSERT INTO accounts (id, user_id, balance) VALUES (2, 1002, 200);
-- ✅ 成功：id=2不在锁定范围

3. 临键锁（Next-Key Lock）

记录锁 + 间隙锁的组合，锁定范围左开右闭 (, ]，是InnoDB的默认锁算法。

-- ========== 临键锁演示 ==========
-- 数据：id ∈ {1, 3, 5, 7, 10}
BEGIN;
SELECT * FROM accounts WHERE id = 5 FOR UPDATE;
-- 临键锁锁定：(3, 5] （前一个间隙到当前记录）

-- 其他事务：
UPDATE accounts SET balance = 500 WHERE id = 5; -- ❌被阻塞（记录锁）
INSERT INTO accounts (id, user_id, balance) VALUES (4, 1004, 400); -- ❌被阻塞（间隙锁，4在(3,5)内）

-- 但如果：
INSERT INTO accounts (id, user_id, balance) VALUES (6, 1006, 600); -- ✅可能成功（看具体索引结构）

注意：如果是唯一索引的等值查询且命中记录，临键锁会退化为记录锁以提高并发性。

3.3 行锁的实践示例

示例1：银行转账（死锁风险）

-- 会话A：A转给B 100元
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 锁id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 锁id=2
COMMIT;

-- 会话B：B转给A 50元（同时进行）
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;  -- 锁id=2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- ❌死锁！等待id=1
-- MySQL会检测到死锁，回滚其中一个事务（通常是修改行数少的）

解决方案：按固定顺序访问资源

-- 都按id从小到大排序
BEGIN;
-- 先处理id小的
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

示例2：乐观锁（版本号控制）

-- 表结构添加version字段
ALTER TABLE accounts ADD COLUMN version INT DEFAULT 0;

-- 会话A：读取版本号
BEGIN;
SELECT balance, version FROM accounts WHERE id = 1;
-- 结果：balance=1000, version=1

-- 计算新余额并更新（带版本号检查）
UPDATE accounts 
SET balance = 900, version = version + 1 
WHERE id = 1 AND version = 1;
-- 如果影响行数=1，成功；=0，说明数据被其他事务修改

COMMIT;

示例3：无索引导致的锁升级（危险！）

-- name字段无索引
BEGIN;
SELECT * FROM accounts WHERE name = 'Alice' FOR UPDATE;
-- ❌危险！InnoDB无法通过索引定位，会扫描全表，对所有行加X锁！
-- 等同于表锁，并发归0

-- 查看实际加锁情况（MySQL 8.0）
SELECT 
    COUNT(*) as locked_rows,
    OBJECT_NAME,
    LOCK_MODE
FROM performance_schema.data_locks
WHERE ENGINE_TRANSACTION_ID = (SELECT trx_id FROM information_schema.innodb_trx 
                               WHERE trx_mysql_thread_id = CONNECTION_ID())
GROUP BY OBJECT_NAME, LOCK_MODE;
-- 结果：locked_rows可能等于全表总行数！

教训：务必确保WHERE条件使用索引！

---

四、三种锁的对比与选择

4.1 特性对比表

特性	全局锁	表级锁	行级锁
锁定范围	整个数据库实例	单个表	单行或间隙
并发度	极低（只读）	低	高
存储引擎	所有引擎	MyISAM/InnoDB等	仅InnoDB
手动控制	FTWRL/UNLOCK	LOCK TABLES/UNLOCK	自动/SELECT ... FOR UPDATE
典型场景	全库备份	批量修改、DDL对DML影响	高并发OLTP交易
死锁风险	无（单点）	低	高（需处理）
性能开销	极高	中等	低（内存中锁结构）

4.2 锁的升级路径

-- MySQL的锁会按需升级，但通常不建议：

-- 1. 行锁升级为表锁（当没有索引时）
-- 自动发生，危险！

-- 2. 意向锁协调
-- 自动发生，无害

-- 3. 手动调整锁策略（MyISAM场景，不推荐）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 降低锁粒度
-- 或
LOCK TABLES t1 WRITE, t2 READ; -- 手动控制表锁

---

五、锁监控与排查实战

5.1 查看当前锁状态（MySQL 8.0推荐）

-- 查看所有锁（包括持有和等待）
SELECT 
    dl.ENGINE_TRANSACTION_ID as trx_id,
    dl.OBJECT_SCHEMA, 
    dl.OBJECT_NAME as table_name,
    dl.INDEX_NAME,
    dl.LOCK_TYPE,  -- TABLE or RECORD
    dl.LOCK_MODE,  -- S, X, IS, IX, GAP, NEXT-KEY等
    dl.LOCK_STATUS, -- GRANTED or WAITING
    dl.LOCK_DATA,  -- 锁定的具体值（如主键值）
    t.trx_mysql_thread_id as thread_id,
    t.trx_query
FROM performance_schema.data_locks dl
JOIN information_schema.innodb_trx t 
    ON dl.ENGINE_TRANSACTION_ID = t.trx_id
ORDER BY dl.ENGINE_TRANSACTION_ID, dl.OBJECT_NAME;

5.2 查看锁等待链

-- 谁阻塞了谁？
SELECT 
    w.trx_id AS waiting_trx_id,
    w.trx_mysql_thread_id AS waiting_thread,
    w.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    TIMESTAMPDIFF(SECOND, w.trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.innodb_trx w
JOIN performance_schema.data_lock_waits lw 
    ON w.trx_id = lw.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b 
    ON b.trx_id = lw.BLOCKING_ENGINE_TRANSACTION_ID
ORDER BY wait_seconds DESC;

5.3 死锁分析

-- 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS\G
-- 关注：
-- - LATEST DETECTED DEADLOCK部分
-- - TRANSACTION部分显示持有的锁
-- - WAITING FOR部分显示等待的锁

-- 开启死锁日志持久化
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 死锁信息会记录到error log，便于事后分析

5.4 长事务监控

-- 查找持有锁时间最长的事务（危险！）
SELECT 
    trx_id,
    trx_mysql_thread_id,
    trx_state,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as trx_seconds,
    trx_tables_locked,
    trx_rows_locked,
    LEFT(trx_query, 100) as query_preview
FROM information_schema.innodb_trx
ORDER BY trx_seconds DESC
LIMIT 5;

-- 终止危险事务
KILL <trx_mysql_thread_id>;

---

六、最佳实践总结

✅ 应该做的

-- 1. 优先使用行级锁（确保InnoDB引擎和索引）
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    INDEX idx_user_id (user_id)  -- 关键：加索引！
) ENGINE=InnoDB;

-- 2. 小事务原则：快速提交，减少锁持有时间
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 不要在这里做复杂计算或调用外部API
COMMIT;

-- 3. 按固定顺序访问资源（避免死锁）
-- 所有事务都按id从小到大更新

-- 4. 使用乐观锁处理低冲突场景
UPDATE products SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 5;

❌ 不应该做的

-- 1. 不要在长事务中持有行锁
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 等待用户输入...（错误！）
COMMIT;

-- 2. 避免无索引的查询（会锁全表）
SELECT * FROM accounts WHERE create_time > '2023-01-01' FOR UPDATE;
-- 如果create_time无索引，将锁定全表！

-- 3. 谨慎使用显式表锁（除非MyISAM）
LOCK TABLES accounts WRITE;
-- 这会阻塞所有其他会话的访问，即使是简单的SELECT

-- 4. 避免在高并发时执行全局锁
FLUSH TABLES WITH READ LOCK; -- 生产环境慎用！

---

结语

理解MySQL的三层锁机制（全局锁-表级锁-行级锁）是数据库优化和高并发设计的基础：

• 全局锁：backup专用，生产环境尽量避免
• 表级锁：DDLL保护+显式批量操作，并发较低
• 行级锁：OLTP核心，高并发场景首选，需注意索引和死锁

掌握这些锁的特性和SQL表现，才能在实际开发中写出既安全又高效的代码。

---

相关系统变量调试：

-- 锁等待超时时间（默认50秒）
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 是否开启死锁检测（默认ON，不建议关闭）
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- 事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

---

希望这篇博客对您理解MySQL锁机制有所帮助！如有疑问，欢迎在评论区交流。 🚀