PostgreSQL中如何触发INSERT查询死锁以测试应用表现

• 现有一款面向数据库的插入数据应用，支持自定义SQL输入字段，当前配置的插入语句如下，语句中%s会在每次迭代时作为参数传入：

INSERT INTO public.test_table ("message") VALUES (%s::text)

• 测试目标是复现死锁场景，验证应用碰到死锁时的运行表现，需要明确基于现有INSERT语句触发死锁的具体操作步骤。
• 当前测试用表结构：

CREATE TABLE public."test_table" (
"number" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
"date" time with time zone NOT NULL DEFAULT NOW(),
"message" text,
PRIMARY KEY ("number"));

• 此前在MariaDB环境中，通过以下语句成功复现过锁超时：

START TRANSACTION;
UPDATE test_table SET message = 'foo';
INSERT INTO test_table (message) VALUES ('test');
DO SLEEP(60);
COMMIT;

• 但相同逻辑迁移到PostgreSQL后完全无法触发锁超时。
• 补充疑问：如果应用改用显式事务包裹单条INSERT，如下所示，是否有可能触发死锁？

BEGIN;
INSERT INTO public.test_table ("message") VALUES (%s::text);

首先说两个核心前提：

1. 你之前MariaDB的逻辑在PostgreSQL不生效的原因很简单：PostgreSQL的行锁只针对操作涉及的已有行，不带WHERE条件的全表UPDATE确实会给表内所有现存行加上排他锁，但普通INSERT只会插入全新的数据行，不需要访问已有的被锁行，自然不会被阻塞，更不可能触发锁等待或超时。如果只是想复现锁超时，直接在一个会话中执行BEGIN; LOCK TABLE public.test_table IN ACCESS EXCLUSIVE MODE; SELECT pg_sleep(60); COMMIT;，此时其他所有会话对该表的读写都会被阻塞，达到lock_timeout设置的阈值后就会报锁超时错误。另外PostgreSQL里没有DO SLEEP()的语法，延时要调用pg_sleep()函数。
2. 死锁的核心必要条件是两个及以上并发事务形成循环锁等待：每个事务都持有其他事务需要的锁，同时等待其他事务释放自己持有的锁。你提到的「显式事务包裹单条普通INSERT」的写法，只要事务内没有其他操作，永远不可能触发死锁——这种场景下每个事务只会申请自己新插入行的锁，不会和其他事务产生锁等待，根本形成不了环路。只有当事务内存在多个会申请行锁的操作（比如UPSERT、带条件的UPDATE/DELETE、SELECT FOR UPDATE等），且不同并发事务的加锁顺序不一致时，才可能触发死锁。

基于你现有INSERT语句复现死锁的步骤 #

最适配你现有业务逻辑的方案是给message字段加唯一约束，通过UPSERT（INSERT ... ON CONFLICT）构造行锁冲突，不需要修改你应用传参的逻辑，全程只需要传message参数即可：

1. 先执行以下语句给表加唯一约束（自增主键本身是唯一索引，用主键也能构造冲突，但需要手动传入number参数，不如加message字段唯一约束方便）：

ALTER TABLE public.test_table ADD CONSTRAINT uk_test_msg UNIQUE ("message");

2. 提前插入两条测试数据，用于构造锁冲突：

INSERT INTO public.test_table ("message") VALUES ('msg_a'), ('msg_b');

3. 打开两个独立的数据库连接（对应应用两个并发请求即可），按以下顺序严格执行操作：

• 步骤1：两个会话同时执行BEGIN;开启显式事务
• 步骤2：会话1执行带UPSERT逻辑的插入，拿到msg_a对应行的排他锁：

INSERT INTO public.test_table ("message") VALUES ('msg_a') 
ON CONFLICT("message") DO UPDATE SET message = EXCLUDED.message;

• 步骤3：会话2执行带UPSERT逻辑的插入，拿到msg_b对应行的排他锁：

INSERT INTO public.test_table ("message") VALUES ('msg_b') 
ON CONFLICT("message") DO UPDATE SET message = EXCLUDED.message;

• 步骤4：会话1执行插入语句，尝试获取msg_b的锁，此时会进入阻塞等待状态：

INSERT INTO public.test_table ("message") VALUES ('msg_b') 
ON CONFLICT("message") DO UPDATE SET message = EXCLUDED.message;

• 步骤5：会话2执行插入语句，尝试获取msg_a的锁，此时两个事务互相持有对方需要的锁，循环等待形成，死锁触发。

等待1~2秒后，PostgreSQL内置的死锁检测机制就会识别到环路，自动回滚其中一个事务，返回deadlock detected的错误，即复现成功。

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