PostgreSQL / 系统课时
认识表级锁
认识表级锁
本课解决“一个操作为什么会让整张表上的其他操作等待”的问题。你将认识 PostgreSQL 常见表级锁模式、判断主要冲突关系,并用可复现示例观察锁等待与超时。
学习目标
- 说明表级锁与行级锁的职责差异
- 识别常见锁模式及其主要冲突
- 使用
LOCK TABLE验证阻塞行为
表级锁解决什么问题
PostgreSQL 的多数 SQL 会自动获取表级锁。这里的“表级”表示锁对象是表,不等于所有操作都会被禁止;只有申请了冲突锁模式的事务才会等待。
普通 SELECT 通常获取 ACCESS SHARE,INSERT、UPDATE、DELETE 获取 ROW EXCLUSIVE。修改表结构、TRUNCATE 等操作常需要更强的 ACCESS EXCLUSIVE。
行锁负责保护具体数据行,表锁负责协调表结构变化、批量维护和不同类型操作之间的关系。一次 UPDATE 往往同时持有表级 ROW EXCLUSIVE 和目标行上的行锁。
常见锁模式与冲突
| 锁模式 | 常见来源 | 关键冲突 |
|---|---|---|
ACCESS SHARE |
普通 SELECT |
仅与 ACCESS EXCLUSIVE 冲突 |
ROW SHARE |
SELECT ... FOR UPDATE |
与 EXCLUSIVE、ACCESS EXCLUSIVE 冲突 |
ROW EXCLUSIVE |
INSERT、UPDATE、DELETE |
与 SHARE 及更强的多个模式冲突 |
SHARE |
显式锁表、部分维护操作 | 会阻塞普通写操作 |
ACCESS EXCLUSIVE |
ALTER TABLE、TRUNCATE、DROP TABLE |
与所有表级锁模式冲突 |
判断阻塞时,不要只看“读”或“写”,而要比较双方实际持有和申请的锁模式。ACCESS EXCLUSIVE 最强,连普通查询也会被阻塞。
复现表锁阻塞
下面脚本需要本机可使用 psql,并能连接环境变量 PGDATABASE 指定的数据库。脚本让第一个会话持有 SHARE 锁 5 秒,第二个会话尝试更新并在 2 秒后超时。
#!/usr/bin/env bash
set -euo pipefail
export PGDATABASE="${PGDATABASE:-postgres}"
psql -v ON_ERROR_STOP=1 <<'SQL'
DROP TABLE IF EXISTS inventory_lock_demo;
CREATE TABLE inventory_lock_demo (
id integer PRIMARY KEY,
stock integer NOT NULL
);
INSERT INTO inventory_lock_demo (id, stock) VALUES (1, 10);
SQL
psql -v ON_ERROR_STOP=1 <<'SQL' &
BEGIN;
LOCK TABLE inventory_lock_demo IN SHARE MODE;
SELECT pg_sleep(5);
COMMIT;
SQL
holder_pid=$!
sleep 1
set +e
psql -v ON_ERROR_STOP=1 <<'SQL'
SET lock_timeout = '2s';
UPDATE inventory_lock_demo
SET stock = stock - 1
WHERE id = 1;
SQL
set -e
wait "$holder_pid"
psql -v ON_ERROR_STOP=1 <<'SQL'
UPDATE inventory_lock_demo
SET stock = stock - 2
WHERE id = 1;
SELECT stock
FROM inventory_lock_demo
WHERE id = 1;
SQL预期结果。第一次更新因 SHARE 与 ROW EXCLUSIVE 冲突而超时;锁释放后第二次更新成功,最终库存为 8。错误信息中的等待时长可能因环境略有差异。
ERROR: canceling statement due to lock timeout
stock
-------
8
(1 row)使用边界
显式锁表会扩大并发影响,应放在尽可能短的事务中。需要阻止写入但允许查询时,可考虑 SHARE;需要执行结构变更时,通常由 PostgreSQL 自动申请所需锁,不应随意提前获取最强锁。
排查阻塞可查询 pg_stat_activity 和 pg_locks,但最终判断仍要结合事务正在执行的 SQL、锁模式和持锁时间。