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.klaver@aklaver.com]
> Sent: 20 March 2018 13:51
> To: HORDER Phil; pgsql-general@lists.postgresql.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.klaver@aklaver.com
>
--
Adrian Klaver
adrian.klaver@aklaver.com
