How to deadlock an INSERT query in PostgreSQL-CodePudding

I have an application that I'm using to insert some data the db. This application has a field to put an SQL like:

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

What I want to check, is how this application behave in case of a deadlock. So, my question is how to deadlock this INSERT query. What should I run to make this happen?

I'm using this table:

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"));

While I was using MariaDB I managed to create a lock timeout using:

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

But in PostgreSQL this doesn't sent even create a lock timeout.

EDIT: Let's say I add this one in the application, is it possible to get a deadlock using this:

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

CodePudding user response：

I don't think you can force a deadlock with INSERTs given the table definition you have as the primary key value is generated automatically. But if you use a manually assigned PK value (or any other unique constraint) you can get deadlock when inserting the same unique values in different transactions

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

it is possible following the usual approach for deadlocks: interleaving locking of multiple resources in different order.

The following will result in a deadlock in step #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');

CodePudding user response：

There are an infinite number of ways you could change it to create a deadlock, but most of those ways would be to essentially throw it away and start over with something else entirely. If you want to make as few changes as possible, then I suppose it look something like putting a unique index on "message", then doing:

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

but you would have to run these in two different sessions at the same time, with the order of 'a' and 'b' reversed in one of them.