表级锁

表级锁每次操作锁住整张表：粒度大、冲突概率最高、并发度最低。分为表锁、元数据锁（MDL）、意向锁三类。

一、介绍

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。
应用在 MyISAM、InnoDB、BDB 等存储引擎中。

主要分为以下三类：

#	类型	作用
1	表锁	手动 `LOCK TABLES`，分表共享读锁、表独占写锁
2	元数据锁（meta data lock，MDL）	系统自动加锁，避免 DML 与 DDL 冲突
3	意向锁	让表锁不必逐行检查行锁，减少表锁的检查

演示用表：

CREATE TABLE score(
    id      INT PRIMARY KEY,
    name    VARCHAR(20),
    math    INT,
    english INT,
    chinese INT
);

INSERT INTO score VALUES (1,'Tom',67,88,95), (2,'Rose',23,66,90), (3,'Jack',56,98,76);

二、表锁

对于表锁，分为两类：表共享读锁（read lock）、表独占写锁（write lock）。

操作	语法
加锁	`LOCK TABLES 表名... READ/WRITE`
释放锁	`UNLOCK TABLES` / 客户端断开连接

加锁后的行为

锁类型	加锁会话自身	其他会话
读锁 `READ`	可读，不能写	可读，写被阻塞
写锁 `WRITE`	可读可写	读、写都被阻塞

实测：读锁

-- 会话 A
LOCK TABLES score READ;
SELECT * FROM score;                       -- 自身可读 → 正常返回 3 行
UPDATE score SET math = 100 WHERE id = 2;  -- 自身不能写
-- ERROR 1099 (HY000): Table 'score' was locked with a READ lock and can't be updated

-- 会话 B
SELECT * FROM score;                       -- 可读 → 正常返回 3 行
UPDATE score SET math = 100 WHERE id = 2;  -- 写被阻塞
-- 一直等待；会话 A 执行 UNLOCK TABLES 后才成功
-- Query OK, 1 row affected (20.42 sec)

实测：写锁

-- 会话 A
LOCK TABLES score WRITE;
SELECT * FROM score;                        -- 自身可读 → 正常返回
UPDATE score SET chinese = 100 WHERE id = 1; -- 自身可写 → Query OK, 1 row affected

-- 会话 B
SELECT * FROM score;                        -- 读被阻塞（Ctrl+C 中止）
-- ERROR 1317 (70100): Query execution was interrupted
UPDATE score SET chinese = 100 WHERE id = 3;  -- 写同样被阻塞

读锁是共享的（别人还能读），写锁是排他的（别人读写都不行）；LOCK TABLES 会一次性只锁列出的表，未列出的表在本会话也不可访问。

三、元数据锁（MDL）

MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。
主要作用是维护表元数据的数据一致性：在表上有活动事务的时候，不可以对元数据进行写入操作 —— 即为了避免 DML 与 DDL 冲突，保证读写的正确性。
MySQL 5.5 中引入了 MDL：对一张表进行增删改查时加 MDL 读锁（共享）；对表结构进行变更操作时加 MDL 写锁（排他）。

对应 SQL	锁类型	说明
`LOCK TABLES xxx READ / WRITE`	`SHARED_READ_ONLY` / `SHARED_NO_READ_WRITE`
`SELECT`、`SELECT ... LOCK IN SHARE MODE`	`SHARED_READ`	与 `SHARED_READ`、`SHARED_WRITE` 兼容，与 `EXCLUSIVE` 互斥
`INSERT`、`UPDATE`、`DELETE`、`SELECT ... FOR UPDATE`	`SHARED_WRITE`	与 `SHARED_READ`、`SHARED_WRITE` 兼容，与 `EXCLUSIVE` 互斥
`ALTER TABLE ...`	`EXCLUSIVE`	与其他的 MDL 都互斥

查看元数据锁

SELECT object_type, object_schema, object_name, lock_type, lock_duration
FROM performance_schema.metadata_locks;

实测：事务未提交时 DDL 被阻塞

-- 会话 A：开启事务并查询（此时表上还没有 java 列）
BEGIN;
SELECT * FROM score;

-- 会话 B：修改表结构 → 阻塞，直到会话 A 提交
ALTER TABLE score ADD COLUMN java INT;
-- Query OK, 0 rows affected (1 min 18 sec)

会话 A 未提交时查看 metadata_locks：

+-------------+---------------+-------------+-------------+---------------+
| object_type | object_schema | object_name | lock_type   | lock_duration |
+-------------+---------------+-------------+-------------+---------------+
| TABLE       | db01          | score       | SHARED_READ | TRANSACTION   |
+-------------+---------------+-------------+-------------+---------------+

普通 SELECT 也会持有 SHARED_READ 的 MDL；只要事务不提交，ALTER TABLE 想拿 EXCLUSIVE 就得一直等 —— 长事务会拖住所有 DDL。

四、意向锁

为了避免 DML 在执行时，加的行锁与表锁的冲突，InnoDB 引入了意向锁，使得表锁不用检查每行数据是否加锁，用意向锁减少表锁的检查。

意向锁	由什么语句添加	与表锁的关系
意向共享锁（IS）	`SELECT ... LOCK IN SHARE MODE`	与表锁共享锁（`READ`）兼容，与表锁排它锁（`WRITE`）互斥
意向排他锁（IX）	`INSERT`、`UPDATE`、`DELETE`、`SELECT ... FOR UPDATE`	与表锁共享锁（`READ`）及排它锁（`WRITE`）都互斥

兼容性矩阵

当前锁 \ 请求锁	表共享读锁 `READ`	表独占写锁 `WRITE`
意向共享锁 IS	兼容	互斥
意向排他锁 IX	互斥	互斥

意向锁之间不会互斥。

查看意向锁及行锁的加锁情况

SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks;

实测：IS + 行级共享锁

-- 会话 A
BEGIN;
SELECT * FROM score WHERE id = 1 LOCK IN SHARE MODE;

+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db01          | score       | NULL       | TABLE     | IS            | NULL      |  ← 意向共享锁
| db01          | score       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |  ← 行级共享锁
+---------------+-------------+------------+-----------+---------------+-----------+

实测：IX + 行级排他锁

-- 会话 A
BEGIN;
UPDATE score SET math = 66 WHERE id = 1;

+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db01          | score       | NULL       | TABLE     | IX            | NULL      |  ← 意向排他锁
| db01          | score       | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |  ← 行级排他锁
+---------------+-------------+------------+-----------+---------------+-----------+

加了行锁，表上就会自动出现对应的意向锁；别的会话想 LOCK TABLES ... WRITE 时，看一眼表上的意向锁就知道「表里已经有行锁了」，不必逐行扫描。

速查

-- 表锁：加读锁 → 自身可读不可写；其他会话可读，写阻塞
LOCK TABLES score READ;
-- 表锁：加写锁 → 自身可读可写；其他会话读写都阻塞
LOCK TABLES score WRITE;
-- 释放表锁 → 会话断开也会自动释放
UNLOCK TABLES;

-- 查看元数据锁（MDL）→ 普通 SELECT 持 SHARED_READ，DDL 需要 EXCLUSIVE
SELECT object_type, object_schema, object_name, lock_type, lock_duration
FROM performance_schema.metadata_locks;

-- 查看意向锁与行锁 → TABLE 行是意向锁，RECORD 行是行锁
SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks;

-- 意向共享锁（IS）+ 行级共享锁（S,REC_NOT_GAP）
BEGIN;
SELECT * FROM score WHERE id = 1 LOCK IN SHARE MODE;

-- 意向排他锁（IX）+ 行级排他锁（X,REC_NOT_GAP）
BEGIN;
UPDATE score SET math = 66 WHERE id = 1;

场景	关键点
要锁住整张表做维护	`LOCK TABLES 表名 READ/WRITE`，用完 `UNLOCK TABLES`
读锁 vs 写锁	读锁共享（别人可读）、写锁排他（别人读写都阻塞）
DDL 被卡住	长事务持有的 MDL 读锁未释放；等它 `COMMIT` 后才拿到 `EXCLUSIVE`
只有行锁，表锁如何知道	行锁会连带自动加意向锁（IS / IX），供表锁快速判断
查看锁情况	MDL 看 `performance_schema.metadata_locks`；意向锁与行锁看 `performance_schema.data_locks`

表级锁

一、介绍

二、表锁

三、元数据锁（MDL）

四、意向锁

速查

相关