Re: Confusing deadlock report

Thomas Kellerer wrote:
> we have a strange (at least to me) deadlock situation which does not seem to fall into the "usual"
> deadlock category.
>
> The error as reported in the Postgres log file is this:
>
> 2016-03-12 13:51:29.305 CET [23912]: [1-1] user=arthur,db=prod,app=[unknown] ERROR: deadlock detected
> 2016-03-12 13:51:29.305 CET [23912]: [2-1] user=arthur,db=prod,app=[unknown] DETAIL: Process 23912
> waits for ShareLock on transaction; blocked by process 24342.
> Process 24342 waits for ShareLock on transaction 39632974; blocked by process 23912.
> Process 23912: UPDATE alpha SET some_flag = $1 WHERE (id = $2)
> Process 24342: INSERT INTO bravo (..., alpha_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9,
> $10)
>
> (I have "obfuscated" the table names)
>
>
> Process 24342 did update table alpha in an earlier step, but a different row than Process 23912
> updated.
> Table bravo has a foreign key to table alpha.
>
> My understanding of the deadlock report is that the statements shown in the log are the actual
> statements on which the two processes were waiting.
>
> What I think is unusual in this situation is the INSERT statement that is part of the deadlock
> situation.
>
> The only way I can think of how a deadlock could happen during an insert, is if process 23912 had
> inserted a row into bravo with the same PK value that process 24342 is trying to insert. But process
> 23912 never even touches that table, so I am a bit confused on how this can happen.
>
> Can the foreign key between bravo and alpha play a role here? With some simple test setups I could not
> get the insert to wait even if it was referencing the row that the other process has updated.
>
> This happened on 9.3.10 running on Debian

The probable culprit is a foreign key between these tables.

What foreign keys are defined?

Yours,
Laurenz Albe