Re: Confusing deadlock report

Albe Laurenz schrieb am 16.03.2016 um 13:20:
>> 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)
>>
>> 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?

The FK in question is:

alter table bravo foreign key (alpha_id) references alpha (id);

But by simply creating two tables (with a foreign key) and doing an update in one transaction and the insert in another, I do not get any locks or waiting transactions.
(And to be honest: I would have been pretty disappointed if I had)

Thomas