LOCK TABLES

导学

事务的行锁和表锁是 InnoDB 自动管理的，但有时候你需要显式控制锁的粒度——比如批量导出数据时防止其他会话修改、或者 MyISAM 表不支持事务时的并发控制。LOCK TABLES 和 UNLOCK TABLES 提供了显式的表级锁机制，让你手动控制哪些表可以被访问。

定义

LOCK TABLES：显式获取一个或多个表的锁，阻止其他会话对锁定表的特定操作，直到执行 UNLOCK TABLES 释放。支持读锁（READ）和写锁（WRITE）两种模式。

核心语法

-- 加锁
LOCK TABLES table_name [AS alias] lock_type [, ...];

-- 锁类型
-- READ：读锁，自己和其他会话都可以读，但都不能写
-- WRITE：写锁，只有自己可以读写，其他会话读写都被阻塞
-- READ LOCAL：MyISAM 专用，允许并发插入

-- 释放锁
UNLOCK TABLES;
锁类型当前会话其他会话读其他会话写
READ可读，不可写可读阻塞
WRITE可读写阻塞阻塞
READ LOCAL可读可读阻塞（MyISAM 允许并发插入）

SQL 示例

场景一：READ 锁（共享读）

演示数据准备：

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
) ENGINE=InnoDB;

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前 employees 表中的完整数据如下：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

执行语句（会话 A）：

-- 会话 A：对 employees 加读锁
LOCK TABLES employees READ;

-- 会话 A 可以读
SELECT * FROM employees;

会话 A 查询结果：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

会话 B 的行为：

-- 会话 B：可以读
SELECT * FROM employees;  -- 成功返回

-- 会话 B：写操作被阻塞
UPDATE employees SET score = 90 WHERE emp_id = 1;
-- 阻塞，直到会话 A 执行 UNLOCK TABLES

会话 A 释放锁：

UNLOCK TABLES;

结果解读：

  • READ 锁是共享锁，多个会话可以同时持有同一张表的 READ 锁
  • 持有 READ 锁的会话自己也不能写（防止锁升级导致的死锁）
  • 其他会话的写操作会被阻塞，直到所有 READ 锁释放

场景二：WRITE 锁（独占写）

当前数据状态：见上文 employees 表完整数据。

执行语句（会话 A）：

-- 会话 A：对 employees 加写锁
LOCK TABLES employees WRITE;

-- 会话 A 可以读写
UPDATE employees SET score = 95 WHERE emp_id = 1;
SELECT * FROM employees;

会话 A 查询结果：

emp_idemp_namedeptscore
1大翔技术部95
2白歌技术部NULL

会话 B 的行为：

-- 会话 B：读被阻塞
SELECT * FROM employees;
-- 阻塞，直到会话 A 执行 UNLOCK TABLES

-- 会话 B：写也被阻塞
INSERT INTO employees (emp_name, dept, score) VALUES ('孔蓝', '产品部', 88);
-- 阻塞

会话 A 释放锁：

UNLOCK TABLES;

结果解读：

  • WRITE 锁是排他锁，同一时间只有一个会话能持有某张表的 WRITE 锁
  • 持有 WRITE 锁的会话可以读写，其他会话的任何操作（读和写）都被阻塞
  • WRITE 锁优先级高于 READ 锁：如果多个会话等待，WRITE 锁请求通常优先获得

场景三：同时锁定多张表

当前数据状态：创建第二张表。

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(20)
) ENGINE=InnoDB;

INSERT INTO departments VALUES (1, '技术部'), (2, '产品部');

执行语句（会话 A）：

-- 同时锁定多张表
LOCK TABLES employees READ, departments WRITE;

-- 可以读 employees
SELECT * FROM employees;

-- 可以读写 departments
UPDATE departments SET dept_name = '研发部' WHERE dept_id = 1;

-- 错误：不能访问未锁定的表
SELECT * FROM logs;
-- ERROR 1100: Table 'logs' was not locked with LOCK TABLES

释放锁：

UNLOCK TABLES;

结果解读：

  • LOCK TABLES 可以一次锁定多张表，用逗号分隔
  • 重要：执行 LOCK TABLES 后，当前会话只能访问被锁定的表，访问其他表会报错
  • 这是为了防止锁范围不明确导致的并发问题
  • 如果需要在锁期间访问多张表，必须在 LOCK TABLES 语句中全部列出

场景四：LOCK TABLES 与事务的交互

执行语句（会话 A）：

-- 开启事务
START TRANSACTION;

-- 尝试加锁
LOCK TABLES employees WRITE;

结果解读：

在 MySQL 5.7 中：

  • LOCK TABLES 会隐式提交当前事务（如果 autocommit=0）
  • 如果已经持有事务锁（如 SELECT ... FOR UPDATE），再执行 LOCK TABLES 会导致未定义行为
  • 最佳实践：LOCK TABLES 和事务不要混用。InnoDB 的显式锁需求优先用 START TRANSACTION + SELECT ... FOR UPDATE/LOCK IN SHARE MODE
-- InnoDB 推荐做法（不用 LOCK TABLES）
START TRANSACTION;
SELECT * FROM employees WHERE emp_id = 1 FOR UPDATE;
-- 业务操作
UPDATE employees SET score = 90 WHERE emp_id = 1;
COMMIT;

场景五：MyISAM 的 READ LOCAL 锁

执行语句（MyISAM 表）：

-- 创建 MyISAM 表（仅演示，生产环境推荐 InnoDB）
CREATE TABLE logs_myisam (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message VARCHAR(100)
) ENGINE=MyISAM;

-- READ LOCAL 允许其他会话在表末尾并发插入
LOCK TABLES logs_myisam READ LOCAL;

-- 其他会话可以执行 INSERT（在表末尾）
-- 但不能执行 UPDATE/DELETE（会阻塞）

UNLOCK TABLES;

结果解读：

  • READ LOCAL 是 MyISAM 特有的优化，允许其他会话在表末尾并发插入
  • InnoDB 不支持 READ LOCAL，对 InnoDB 使用 READ LOCAL 等价于 READ
  • MyISAM 表没有事务支持，LOCK TABLES 是其主要的并发控制手段

常见误区

误区正解
"LOCK TABLES 是 InnoDB 的推荐锁机制"不是。InnoDB 有行级锁和事务，优先用 SELECT ... FOR UPDATE / LOCK IN SHARE MODE。LOCK TABLES 主要用于 MyISAM 或特殊批量操作。
"LOCK TABLES 后还能访问其他表"不能。执行 LOCK TABLES 后，当前会话只能访问被显式锁定的表，访问其他表报错 ERROR 1100。
"UNLOCK TABLES 会自动提交事务"反过来：LOCK TABLES 会隐式提交事务。UNLOCK TABLES 不会提交。
"READ 锁自己可以写"不能。持有 READ 锁的会话也不能写，这是为了防止锁升级死锁。
"WRITE 锁其他会话完全不能读"是的。WRITE 锁完全排他，其他会话读写都被阻塞。这与行级排他锁（X锁）不同——行锁不阻塞其他行的读。
"锁表后断开连接会自动释放"是的。连接断开时，所有 LOCK TABLES 锁自动释放。但依赖断连释放不可靠，应显式 UNLOCK TABLES。

面试考点

Q：LOCK TABLES 和事务锁的区别？

LOCK TABLES 是服务器层的表级锁，粒度粗，锁定整张表，与存储引擎无关；事务锁（如 SELECT ... FOR UPDATE）是 InnoDB 的行级锁，粒度细，只锁定匹配的行。InnoDB 优先用事务锁，LOCK TABLES 主要用于 MyISAM 或批量导出等特殊场景。两者不要混用，LOCK TABLES 会隐式提交事务。

Q：InnoDB 为什么不需要 LOCK TABLES？

InnoDB 支持事务和行级锁，START TRANSACTION + SELECT ... FOR UPDATE/LOCK IN SHARE MODE 可以实现更细粒度的并发控制，性能更好。LOCK TABLES 锁整张表，并发度低，且会隐式提交事务，破坏事务语义。

Q：READ 和 WRITE 锁的兼容性？

READ 锁之间兼容（多个会话可同时持有 READ）；WRITE 锁与任何锁都不兼容（包括 READ 和 WRITE）；WRITE 锁请求通常优先于 READ 锁请求（防止写饥饿）。

Q：什么场景还需要用 LOCK TABLES？

  1. MyISAM 表（不支持事务）；2. 批量导出数据时确保一致性快照；3. 某些 DDL 操作前防止并发写入；4. 跨表操作需要原子性但不想用事务时。InnoDB 生产环境极少使用。

Q：LOCK TABLES 和 FLUSH TABLES WITH READ LOCK 的区别？

LOCK TABLES 锁定指定表，当前会话只能访问被锁表；FLUSH TABLES WITH READ LOCK（FTWRL）锁定所有表，且会关闭所有打开的表句柄，常用于物理备份（如 xtrabackup 之前的逻辑备份）。FTWRL 的锁范围更大，影响更严重。

小结

  • LOCK TABLES 显式获取表级锁，READ 共享读，WRITE 独占读写
  • 锁定后当前会话只能访问被锁定的表，其他表访问报错
  • LOCK TABLES 会隐式提交事务，不要与 InnoDB 事务混用
  • InnoDB 优先用行级锁（FOR UPDATE / LOCK IN SHARE MODE），LOCK TABLES 主要用于 MyISAM 或特殊场景
  • 锁必须显式 UNLOCK TABLES 释放，断连自动释放但不推荐依赖

下一章引子：显式锁表是粗粒度的并发控制，而 InnoDB 的行级锁和 MVCC 机制让事务在更细的粒度上实现高并发——接下来深入事务隔离级别与 MVCC 的实现原理。