Deadlock detected while executing concurrent insert queries on same table - Mailing list pgsql-hackers

Hello community, 

I recently encountered a deadlock in postgresql while performing concurrent INSERT statements on the same table in two separate sessions.

The error message explicitly mentions that the deadlock occurred while inserting an index tuple. 

There were no explicit transactions (BEGIN/COMMIT). The inserts were executed as the standalone statements.

PG version : 11.4

Here’s the table definition :

Table “auditlog”

      Column      |            Type             | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------
 id             | bigint                      |           | not null |
 audit_logid       | bigint                      |           | not null |
 recordid         | bigint                      |           |          |
 recordname       | text                        |           |          |
 module           | character varying(50)       |           | not null |
 actioninfo       | citext                      |           | not null |
 relatedid        | bigint                      |           |          |
 related_name      | character varying(255)      |           |          |
 accountid        | bigint                      |           |          |
 accountname      | character varying(255)      |           |          |
 doneby           | character varying(255)      |           | not null |
 userid           | bigint                      |           |          |
 audit_time      | timestamp without time zone |           | not null |
 isauditlogdata   | boolean                     |           | not null |
 details     | citext                      |           |          |
 auditcategory        | integer                     |           | not null |
 operationtype       | integer                     |           | not null |
 source           | integer                     |           | not null |

Indexes:
    "auditlog_pkey" PRIMARY KEY, btree (id, audit_time, audit_logid)
    "auditlog_idx1" btree (recordid)
    "auditlog_idx2" btree (audit_logid DESC)
    "auditlog_idx3" btree (userid)
    "auditlog_idx4" btree (relatedid)
    "auditlog_idx5" gist (actioninfo gist_trgm_ops)

 and exact error message from the logs :

ERROR:  INSERT failed, ERROR:  deadlock detected

        DETAIL:  Process 3841267 waits for ShareLock on transaction 185820512; blocked by process 3841268.

        Process 3841268 waits for ShareLock on transaction 185820513; blocked by process 3841267.

        HINT:  See server log for query details.

        CONTEXT:  while inserting index tuple (31889,32) in relation “auditlog”

Insert Query1 :
INSERT INTO auditlog (ID,AUDIT_LOGID,RECORDID,RECORDNAME,MODULE,ACTIONINFO,RELATEDID,RELATED_NAME,ACCOUNTID,ACCOUNTNAME,DONEBY,USERID,AUDIT_TIME,ISAUDITLOGDATA,DETAILS,AUDITCATEGORY,OPERATIONTYPE,SOURCE) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18)

Insert Query2 :
INSERT INTO auditlog (id, audit_logid, recordid, recordname, module, actioninfo, relatedid, related_name, accountid, accountname, doneby, userid, audit_time, isauditlogdata, details, auditcategory, operationtype, source) VALUES ('67016721806'::bigint, '389760000328846849'::bigint, NULL::bigint, NULL::text, 'Tasks'::character varying(50), 'Deleted'::citext, NULL::bigint, NULL::character varying(255), NULL::bigint, NULL::character varying(255), 'Technologies'::character varying(255), '3470005430253334'::bigint, '2024-03-24 14:39:06'::timestamp without time zone, true, NULL::citext, 0, 11, 20)
 

Could this be a bug, or is it expected behaviour under certain conditions ?

I was unable to reproduce this issue again. Any insights or guidance on how to analyse this further would be greatly appreciated.

Regards,

Sri Keerthi.