postgresql deadlock while recording build

[fho]

In our CI system a postgresql deadlock occured while recording a task run for baur. It's likely that multiple baur instances ran in parallel at the same time and where querying and or recording task runs.

2022-02-25 09:37:26 UTC ERROR:  deadlock detected
2022-02-25 09:37:26 UTC DETAIL:  Process 158747 waits for ShareLock on transaction 44098; blocked by process 157931.
        Process 157931 waits for ShareLock on transaction 44099; blocked by process 158747.
        Process 158747:
                   INSERT INTO input_file (path, digest)
                   VALUES
        ($1, $2),  ($3, $4),  ($5, $6),  ($7, $8),  ($9, $10),  ($11, $12),  ($13, $14),  ($15, $16),  ($17, $18),  ($19, $20),  ($21, $22),  ($23, $24),  ($25, $26),  ($27, $28),  ($29, $30),  ($31, $32),  ($33, $34),  ($35, $36),
 ($37, $38),  ($39, $40),  ($41, $42),  ($43, $44),  ($45, $46),  ($47, $48),  ($49, $50),  ($51, $52),  ($53, $54),  ($55, $56),  ($57, $58),  ($59, $60),  ($61, $62),  ($63, $64),  ($65, $66),  ($67, $68),  ($69, $70),  ($71, $72)
,  ($73, $74),  ($75, $76),  ($77, $78),  ($79, $80),  ($81, $82),  ($83, $84),  ($85, $86),  ($87, $88),  ($89, $90),  ($91, $92),  ($93, $94)
                       ON CONFLICT ON CONSTRAINT input_file_path_digest_uniq
                       DO UPDATE SET id=input_file.id
                RETURNING id

        Process 157931:
                   INSERT INTO input_file (path, digest)
                   VALUES
        ($1, $2),  ($3, $4),  ($5, $6),  ($7, $8),  ($9, $10),  ($11, $12),  ($13, $14),  ($15, $16),  ($17, $18),  ($19, $20),  ($21, $22),  ($23, $24),  ($25, $26),  ($27, $28),  ($29, $30),  ($31, $32),  ($33, $34),  ($35, $36),
 ($37, $38),  ($39, $40),  ($41, $42),  ($43, $44),  ($45, $46),  ($47, $48),  ($49, $50),  ($51, $52),  ($53, $54),  ($55, $56),  ($57, $58),  ($59, $60),  ($61, $62),  ($63, $64),  ($65, $66),  ($67, $68),  ($69, $70),  ($71, $72)
,  ($73, $74),  ($75, $76),  ($77, $78),  ($79, $80),  ($81, $82),  ($83, $84),  ($85, $86),  ($87, $88),  ($89, $90),  ($91, $92),  ($93, $94),  ($95, $96),  ($97, $98),  ($99, $100),  ($101, $102),  ($103, $104),  ($105, $106),  (
$107, $108),  ($109, $110),  ($111, $112),  ($113, $114),  ($115, $116),  ($117, $118),  ($119, $120),  ($121, $122),  ($123, $124),  ($125, $126),  ($127, $128),  ($129, $130),  ($131, $132),  ($133, $134),  ($135, $136),  ($137, $
138),  ($139, $140),  ($141, $142),  ($143
2022-02-25 09:37:26 UTC HINT:  See server log for query details.
2022-02-25 09:37:26 UTC CONTEXT:  while inserting index tuple (1875,27) in relation "input_file"
2022-02-25 09:37:26 UTC STATEMENT:
                   INSERT INTO input_file (path, digest)
                   VALUES
        ($1, $2),  ($3, $4),  ($5, $6),  ($7, $8),  ($9, $10),  ($11, $12),  ($13, $14),  ($15, $16),  ($17, $18),  ($19, $20),  ($21, $22),  ($23, $24),  ($25, $26),  ($27, $28),  ($29, $30),  ($31, $32),  ($33, $34),  ($35, $36),
 ($37, $38),  ($39, $40),  ($41, $42),  ($43, $44),  ($45, $46),  ($47, $48),  ($49, $50),  ($51, $52),  ($53, $54),  ($55, $56),  ($57, $58),  ($59, $60),  ($61, $62),  ($63, $64),  ($65, $66),  ($67, $68),  ($69, $70),  ($71, $72)
,  ($73, $74),  ($75, $76),  ($77, $78),  ($79, $80),  ($81, $82),  ($83, $84),  ($85, $86),  ($87, $88),  ($89, $90),  ($91, $92),  ($93, $94)
                       ON CONFLICT ON CONSTRAINT input_file_path_digest_uniq
                       DO UPDATE SET id=input_file.id
                RETURNING id

happened in version: baur 2.1.0. should affect all baur versions >=2.0.0

[fho]

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order. In the example above, if both transactions had updated the rows in the same order, no deadlock would have occurred. One should also ensure that the first lock acquired on an object in a transaction is the most restrictive mode that will be needed for that object. If it is not feasible to verify this in advance, then deadlocks can be handled on-the-fly by retrying transactions that abort due to deadlocks.

https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS