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 秒后超时。

bash
#!/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。错误信息中的等待时长可能因环境略有差异。

text
ERROR:  canceling statement due to lock timeout
 stock
-------
     8
(1 row)

使用边界

显式锁表会扩大并发影响,应放在尽可能短的事务中。需要阻止写入但允许查询时,可考虑 SHARE;需要执行结构变更时,通常由 PostgreSQL 自动申请所需锁,不应随意提前获取最强锁。

排查阻塞可查询 pg_stat_activity 和 pg_locks,但最终判断仍要结合事务正在执行的 SQL、锁模式和持锁时间。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

认识表级锁

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通