如何在PostgreSQL中死锁INSERT查询

Question

我有一个应用程序，我正在使用插入一些数据数据库。这个应用程序有一个字段来放置SQL，如:

INSERT INTO public.test_table ("message") VALUES (%s::text)  # %s will be used as a parameter in each iteration

我想检查的是这个应用程序在死锁情况下的行为。所以，我的问题是如何死锁这个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中，这甚至不会创建一个锁超时。

编辑:假设我在应用程序中添加了这个，是否有可能使用这个来获得死锁:

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

Answer 1

由于主键值是自动生成的，所以我不认为您可以使用insert强制发生死锁。但是，如果您使用手动分配的PK值(或任何其他唯一约束)，那么当在不同的事务中插入相同的唯一值时，可能会出现死锁

create table test_table 
(
id integer primary key, 
code varchar(10) not null unique
);

可以按照通常的方法处理死锁:以不同的顺序交错锁定多个资源。

下面的语句将导致步骤#4中的死锁

Step      | Transaction 1               |     Transaction 2
----------|-----------------------------|----------------------------------
#1        | insert into test_table      |
|   (id, code)                |
| values                      |
|   (1, 'one'),               |
|   (2, 'two');               |
----------|-----------------------------|----------------------------------
#2        |                             | insert into test_table 
|                             |   (id, code)
|                             | values
|                             |   (3, 'three');
----------|-----------------------------|----------------------------------
| -- this waits               |
#3        | insert into test_table      | 
|   (id, code)                |
| values                      |
|   (3, 'three');             |
----------|-----------------------------|----------------------------------
#4        |                             | -- this results in a deadlock
|                             | insert into test_table 
|                             |   (id, code)
|                             | values
|                             |   (2, 'two');

Answer 2

有无数种方法可以通过更改它来创建死锁，但大多数方法实际上都是将其丢弃并从头开始。如果您想做尽可能少的更改，那么我想它看起来就像在"message"上添加一个唯一索引，然后执行:

BEGIN;
INSERT INTO public.test_table ("message") VALUES ('a');
INSERT INTO public.test_table ("message") VALUES ('b');

但是你必须同时在两个不同的会话中运行它们，其中一个会话中'a'和'b'的顺序颠倒了。