Deadlock detected while executing concurrent insert queries on same table

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.
ReplyForward
Add reaction