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