Re: Confusing deadlock report

Albe Laurenz schrieb am 16.03.2016 um 14:38:
>>> 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)
>
> Hm, true; I cannot get a lock with these two statements.
>
> Can you determine what statements were executed in these transactions before the deadlock?
> It was probably one of these that took the conflicting lock.

Unfortunately not. Statement logging is not enabled on that server (space-constrained).

And while we know the statements that can possibly be executed by these parts of the application, several on them depend on the actual data, so it's hard to tell which path the two transactions actually used.

Thomas