PostgreSQL / 系统课时

观察 INSERT UPDATE DELETE 加锁

观察 INSERT UPDATE DELETE 加锁

这节课用三个并发会话直接观察常见写操作的锁行为:先确认它们都会拿表级 RowExclusiveLock,再制造同一行更新冲突,最后从 pg_locks 与 pg_stat_activity 看到等待实际落在事务 ID 上。

学习目标

  • 验证 INSERT、UPDATE、DELETE 对目标表取得的表级锁。
  • 制造同一行写冲突,并用系统视图找到阻塞者与等待者。
  • 理解“表锁已授予”与“SQL 仍在等待”可以同时成立的原因。

准备可重复的实验表

下面的实验需要三个 psql 会话:A 用于持锁,B 用于制造竞争,C 只负责观察。先在任意会话初始化数据;如果表已存在,脚本会重建它。

sql
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');

预期输出

text
DROP TABLE
CREATE TABLE
INSERT 0 2

INSERT、UPDATE、DELETE 都会在被修改的目标表上取得 RowExclusiveLock。这种表级锁彼此并不冲突,所以两个写事务即使都拿到了表锁,也仍可能因为操作同一行而等待。:contentReference[oaicite:0]{index=0}

观察 INSERT 的表级锁

在会话 A 执行插入但暂不提交,并立即查询当前后端在 lock_lab 上的关系锁。

sql
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;

预期输出的关键行如下,行顺序可能不同。

text
BEGIN
SET
INSERT 0 1
 locktype |       mode       | granted
----------+------------------+---------
 relation | RowExclusiveLock | t

执行 ROLLBACK; 结束实验事务。

sql
ROLLBACK;

预期输出

text
ROLLBACK

这里看见的是表级锁。普通行级锁记录在数据页的元组上,通常不会以 tuple 锁行直接出现在 pg_locks 中;发生行锁等待时,更常见的是等待者对持锁事务的 transactionid 请求 ShareLock。:contentReference[oaicite:1]{index=1}

写操作从表级锁到事务等待的观察链路

制造 UPDATE 行级冲突

先在会话 A 更新 id = 1 并保持事务打开。application_name 用来让观察查询能稳定识别该会话。

sql
BEGIN;
SET LOCAL application_name = 'lock-a';

UPDATE lock_lab
SET balance = balance + 10
WHERE id = 1;

预期输出

text
BEGIN
SET
UPDATE 1

接着在会话 B 更新同一行。该语句不会立刻返回,因为它需要等待会话 A 结束。

sql
SET application_name = 'lock-b';

UPDATE lock_lab
SET note = 'updated-by-b'
WHERE id = 1;

预期结果

text
暂时无返回;语句处于锁等待状态

保持 A、B 不动,在会话 C 查询未授予的锁,并让 PostgreSQL 直接给出阻塞者 PID。

sql
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> 是动态值。

text
 application_name |   locktype    |   mode    | granted | transactionid | blocked_by
------------------+---------------+-----------+---------+---------------+------------
 lock-b           | transactionid | ShareLock | f       | <XID_A>       | {<PID_A>}

此时再检查两个会话在目标表上的关系锁,会发现它们都已经成功取得 RowExclusiveLock。

sql
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;

预期输出

text
 application_name |       mode       | granted
------------------+------------------+---------
 lock-a           | RowExclusiveLock | t
 lock-b           | RowExclusiveLock | t

在会话 A 提交后,A 持有的行锁与事务 ID 锁释放,B 的 UPDATE 随即继续并返回 UPDATE 1。

sql
COMMIT;

预期输出

text
COMMIT

验证 DELETE 的锁行为

DELETE 同样取得目标表的 RowExclusiveLock,并对被删除的行取得与 FOR UPDATE 等价强度的行锁;如果另一事务正在锁定同一行,它也会等待。:contentReference[oaicite:2]{index=2}

sql
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;

预期输出的关键部分如下。

text
BEGIN
DELETE 1
       mode       | granted
------------------+---------
 RowExclusiveLock | t
ROLLBACK

观察 DML 锁时不要只盯着 relation 锁:它能说明写事务进入了表,但无法单独解释“为什么同表两个 RowExclusiveLock 都是 granted = true,SQL 却仍卡住”。定位真正的行级写等待,应把未授予的 transactionid 锁、pg_stat_activity 的等待事件和 pg_blocking_pids() 组合起来看。

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

目录与阅读设置

记录一个想法

观察 INSERT UPDATE DELETE 加锁

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

登录哪都通