PostgreSQL两类事务表锁取舍：避免死锁的技术问询

表结构 #

CREATE TABLE clicks (
  page_id UUID REFERENCES pages (id) ON DELETE CASCADE,
  status                TEXT        NOT NULL,
  click                 BIGINT      NOT NULL,
  PRIMARY KEY (page_id, status)
);

执行代码块 #

code_block_1 #

BEGIN;
LOCK TABLE clicks IN SHARE UPDATE EXCLUSIVE MODE;
INSERT INTO clicks (page_id, status, count)
            VALUES ($1, $2, $3 )
            ON CONFLICT (page_id, status) DO UPDATE SET count = clicks.count + EXCLUDED.count;
-- 该INSERT语句会在事务内的循环中针对表中不同行多次执行
COMMIT;

code_block_2 #

BEGIN;
LOCK TABLE clicks IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO clicks (page_id, status, count)
VALUES ($1, $2, $3)
ON CONFLICT (page_id, status) DO UPDATE SET
count = EXCLUDED.count;
-- 该INSERT语句会在事务内的循环中针对表中不同行多次执行
COMMIT;

场景与问题 #

• code_block_1更新的行可能被多线程并行更新；
• code_block_2更新的行不会被其他线程并行更新。

问题：在希望避免死锁的前提下，可以舍弃哪一个表锁（如果存在可舍弃的）？

解答 #

可以舍弃code_block_2中的表锁，code_block_1的表锁必须保留，具体分析如下：

关于code_block_2 #

它操作的行不会被其他线程并行修改，而ON CONFLICT DO UPDATE本身会自动为目标冲突行加上行级排他锁（FOR UPDATE），确保当前事务对该行的独占修改权限。即使在事务内循环执行多次UPSERT，由于没有其他线程会触碰这些行，不存在多个线程交叉持有行锁的场景，完全不会有死锁风险。

此时添加的SHARE ROW EXCLUSIVE表级锁属于多余操作——这种表锁会阻塞其他需要修改表结构、添加更高级别表锁的操作，去掉它不仅不会引发死锁，还能提升系统的并发处理能力。

关于code_block_1 #

它操作的行存在多线程并行更新的情况，且事务内会循环处理多个不同行。如果去掉SHARE UPDATE EXCLUSIVE表锁，多个并发线程很可能出现交叉锁定行的死锁场景：比如线程1先锁定行X，线程2先锁定行Y，之后线程1尝试锁定行Y、线程2尝试锁定行X，双方互相等待对方释放锁，最终触发死锁。

SHARE UPDATE EXCLUSIVE表锁的作用是限制同一时间只有一个这类事务执行，从根源上避免了交叉行锁的可能性，因此这个表锁必须保留才能防止死锁。

内容的提问来源于stack exchange，提问作者Gilo