PostgreSQL / 系统课时
观察 INSERT UPDATE DELETE 加锁
观察 INSERT UPDATE DELETE 加锁
这节课用三个并发会话直接观察常见写操作的锁行为:先确认它们都会拿表级 RowExclusiveLock,再制造同一行更新冲突,最后从 pg_locks 与 pg_stat_activity 看到等待实际落在事务 ID 上。
学习目标
- 验证
INSERT、UPDATE、DELETE对目标表取得的表级锁。 - 制造同一行写冲突,并用系统视图找到阻塞者与等待者。
- 理解“表锁已授予”与“SQL 仍在等待”可以同时成立的原因。
准备可重复的实验表
下面的实验需要三个 psql 会话:A 用于持锁,B 用于制造竞争,C 只负责观察。先在任意会话初始化数据;如果表已存在,脚本会重建它。
DROP TABLE IF EXISTS lock_lab;
CREATE TABLE lock_lab (
id bigint PRIMARY KEY,
balance integer NOT NULL,
note text NOT NULL
);
INSERT INTO lock_lab (id, balance, note)
VALUES (1, 100, 'row-1'), (2, 200, 'row-2');预期输出
DROP TABLE
CREATE TABLE
INSERT 0 2INSERT、UPDATE、DELETE 都会在被修改的目标表上取得 RowExclusiveLock。这种表级锁彼此并不冲突,所以两个写事务即使都拿到了表锁,也仍可能因为操作同一行而等待。:contentReference[oaicite:0]{index=0}
观察 INSERT 的表级锁
在会话 A 执行插入但暂不提交,并立即查询当前后端在 lock_lab 上的关系锁。
BEGIN;
SET LOCAL application_name = 'lock-a';
INSERT INTO lock_lab (id, balance, note)
VALUES (3, 300, 'insert-a');
SELECT locktype, mode, granted
FROM pg_locks
WHERE pid = pg_backend_pid()
AND relation = 'lock_lab'::regclass;预期输出的关键行如下,行顺序可能不同。
BEGIN
SET
INSERT 0 1
locktype | mode | granted
----------+------------------+---------
relation | RowExclusiveLock | t执行 ROLLBACK; 结束实验事务。
ROLLBACK;预期输出
ROLLBACK这里看见的是表级锁。普通行级锁记录在数据页的元组上,通常不会以 tuple 锁行直接出现在 pg_locks 中;发生行锁等待时,更常见的是等待者对持锁事务的 transactionid 请求 ShareLock。:contentReference[oaicite:1]{index=1}

制造 UPDATE 行级冲突
先在会话 A 更新 id = 1 并保持事务打开。application_name 用来让观察查询能稳定识别该会话。
BEGIN;
SET LOCAL application_name = 'lock-a';
UPDATE lock_lab
SET balance = balance + 10
WHERE id = 1;预期输出
BEGIN
SET
UPDATE 1接着在会话 B 更新同一行。该语句不会立刻返回,因为它需要等待会话 A 结束。
SET application_name = 'lock-b';
UPDATE lock_lab
SET note = 'updated-by-b'
WHERE id = 1;预期结果
暂时无返回;语句处于锁等待状态保持 A、B 不动,在会话 C 查询未授予的锁,并让 PostgreSQL 直接给出阻塞者 PID。
SELECT
a.application_name,
l.locktype,
l.mode,
l.granted,
l.transactionid,
pg_blocking_pids(l.pid) AS blocked_by
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE a.application_name = 'lock-b'
AND NOT l.granted;预期输出的关键形态如下;<XID_A> 与 <PID_A> 是动态值。
application_name | locktype | mode | granted | transactionid | blocked_by
------------------+---------------+-----------+---------+---------------+------------
lock-b | transactionid | ShareLock | f | <XID_A> | {<PID_A>}此时再检查两个会话在目标表上的关系锁,会发现它们都已经成功取得 RowExclusiveLock。
SELECT
a.application_name,
l.mode,
l.granted
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE a.application_name IN ('lock-a', 'lock-b')
AND l.relation = 'lock_lab'::regclass
AND l.locktype = 'relation'
ORDER BY a.application_name;预期输出
application_name | mode | granted
------------------+------------------+---------
lock-a | RowExclusiveLock | t
lock-b | RowExclusiveLock | t在会话 A 提交后,A 持有的行锁与事务 ID 锁释放,B 的 UPDATE 随即继续并返回 UPDATE 1。
COMMIT;预期输出
COMMIT验证 DELETE 的锁行为
DELETE 同样取得目标表的 RowExclusiveLock,并对被删除的行取得与 FOR UPDATE 等价强度的行锁;如果另一事务正在锁定同一行,它也会等待。:contentReference[oaicite:2]{index=2}
BEGIN;
DELETE FROM lock_lab
WHERE id = 2;
SELECT mode, granted
FROM pg_locks
WHERE pid = pg_backend_pid()
AND relation = 'lock_lab'::regclass
AND locktype = 'relation';
ROLLBACK;预期输出的关键部分如下。
BEGIN
DELETE 1
mode | granted
------------------+---------
RowExclusiveLock | t
ROLLBACK观察 DML 锁时不要只盯着 relation 锁:它能说明写事务进入了表,但无法单独解释“为什么同表两个 RowExclusiveLock 都是 granted = true,SQL 却仍卡住”。定位真正的行级写等待,应把未授予的 transactionid 锁、pg_stat_activity 的等待事件和 pg_blocking_pids() 组合起来看。