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@xxxxxxxxxxx
