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