Re: Foreign Key locking / deadlock issue.

On 03/20/2018 06:55 AM, HORDER Phil wrote:

Please reply to list also.
Ccing list.

Still not certain what the PK is or what key value refers to?

> Well this is just sample SQL to demonstrate the problem.
> If each process executes lines of code alternately, a deadlock occurs.
>
> The commit would obviously be required by an application, but one transaction would be cancelled by the deadlock exception, and the demonstrator would rollback the other.
>
> Phil Horder
> Database Mechanic

> -----Original Message-----
> From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
> Sent: 20 March 2018 13:51
> To: HORDER Phil; pgsql-general(at)lists(dot)postgresql(dot)org
> Subject: Re: Foreign Key locking / deadlock issue.
>
> On 03/20/2018 04:46 AM, HORDER Phil wrote:
>> Hi,
>>
>> I'm trying to understand why I'm getting a deadlock issue, and how to
>> work around it.
>>
>> At base, I think the problem is:
>>
>> 1.Updates to a parent table are creating row level write locks,
>>
>> 2.updates to a child table set the foreign key value to the parent
>> table, which are then blocked.
>>
>> While investigating, I found the row locking documentation, which says
>> that I can request read locks that don't block.
>>
>> But my sample code still gets blocked.
>>
>> https://www.postgresql.org/docs/9.6/static/explicit-locking.html
>>
>> Can someone explain what I'm missing please?
>>
>> parent process
>>
>> -------------------
>>
>> start transaction;
>>
>> select * from pl where pl_id in (2,3) for no key update of pl;  (1)
>>
>> update pl set m_number = '234' where pl_id = 2;                 (2)
>>
>> update pl set m_number = '345' where pl_id = 3;                 (3)
>>
>> child process
>>
>> -------------------
>>
>> start transaction;
>>
>> select pl_id from pl where pl_id in (2,3) for key share of pl;  (4)
>>
>> update eln set pl_id = 3 where event_id = 303;                  (5)
>>
>> update eln set pl_id = 2 where event_id = 302;                  (6)
>
>
> I do not see a commit for either transaction.
>
>>
>> My Parent process inserts and updates on the PL table, but never
>> changes the key value.
>
> I am assuming when you say key value you are referring to PRIMARY KEY?
>
> What is the key(PK) column?
>
>>
>> My Child process inserts and updates on the ELN table, and can set the
>> FK reference value to the PL table.
>>
>> I can understand that the default lock on the PL update will block the
>> foreign key check from the ELN table.
>>
>> Why does this example still get blocked?
>>
>> Is there a way around this without dropping the foreign key?
>>
>> (And if so.. How can I get this to work in Spring Data / JPA?)
>>
>> Phil Horder
>>
>> Database Mechanic
>>
>> Thales
>>
>> Land and Air Systems
>>
>> Horizon House, Throop Road, Templecombe, Somerset, BA8 0DH, UK
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com