Thomas Kellerer wrote:
>>> 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)
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.
Yours,
Laurenz Albe