Обсуждение: Foreign Key locking / deadlock issue.
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)
My Parent process inserts and updates on the PL table, but never changes the key value.
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
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
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
Hello Phil, On Tue, 2018-03-20 at 11:46 +0000, 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) > > > My Parent process inserts and updates on the PL table, but never > changes the key value. > 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 > The details are a bit sparse. I don't understand "why" you are using "no key update" or "for key share". Anyway, if the business rules dictate that table pl and table eln should be updated in a single transaction, the way I'd do this would be to create a method or a sub-routine where three arguments are specified being the connection handle, the values of p_id, m_number and event_id to be used. Then:- BEGIN; SELECT m_number FROM pl WHERE pl_id = $2 FOR UPDATE; UPDATE pl SET m_number = $3 WHERE pl_id = $2; Test for success. If successful, then:- SELECT pl_id FROM eln WHERE event_id = $4 FOR UPDATE; UPDATE eln SET pl_id = $2 WHERE event_id = $4; If successful then COMMIT; If either of the updates fail, ROLLBACK; and inform the user that the transaction failed. If you are using Java then remember that auto-commit is usually set "on" by default. Doing a SELECT . . . FOR UPDATE on a single row just locks that row and if another session also tries to do the same thing, then it just waits until it times out. You haven't said if this code is being generated dynamically by the application. HTH, Rob
Apologies, I committed the heinous crime of not creating a full working demo. It seems that Row Level Security is involved - I'll post an update with full code ASAP Phil Horder Database Mechanic