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.
Here’s the table schema :
Table “test_table”
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------
id | bigint | | not null |
logid | bigint | | not null |
record_id | bigint | | |
name | text | | |
actioninfo | text | | not null |
doneby | character varying(255) | | not null |
userid | bigint | | |
time | timestamp without time zone | | not null |
details | citext | | |
Indexes:
"test_table_pkey" PRIMARY KEY, btree (id, time, logid)
“idx_recordid” btree (record_id)
“idx_logid” btree (logid DESC)
"idx_userid” btree (userid)
"idx_actioninfo” 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 “test_table”
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.
