Foreign Key locking / deadlock issue.

[HORDER Phil]

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

RE: Foreign Key locking / deadlock issue.

[HORDER Phil]

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

RE: Foreign Key locking / deadlock issue.... v2

[HORDER Phil]

OK,

Let's try again, with a full script, and including the bit that makes the 
difference…



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 – because there is Row Level Security on the 
parent table.







-- The setup





drop table if exists ELN;

drop table if exists PL;



Create table PL

(pl_id integer,

m_number text

);





alter table PL ENABLE row level security;

alter table PL FORCE row level security;



-- A dummy security policy, that allows everybody access to everything

drop policy if exists security_policy on PL ;

create policy security_policy on PL TO public using (true);   -- (1)



Alter table PL add constraint PL_PK primary key (pl_id);

Insert into PL values (1, null);

Insert into PL values (2, null);

Insert into PL values (3, null);



Create table ELN

(event_id integer,

pl_id integer

);





Alter table ELN add constraint ELN_PK primary key (event_id);

Alter table ELN add constraint ELN_PL_FK foreign key (pl_id) references PL 
(pl_id);



Insert into ELN values (301, null);

Insert into ELN values (302, null);

Insert into ELN values (303, null);



-- Parent table processing – long running process

--

start transaction;

update pl set m_number = '234' where pl_id = 2;   -- (2)

update pl set m_number = '345' where pl_id = 3;   -- (3)



-- …. Pause while other processing happens …..

(commit;)





-- Child table processing – occurs often & quickly. Starts after parent update.



start transaction;

update eln set pl_id = 3 where event_id = 303; -- (4)

update eln set pl_id = 2 where event_id = 302; -- (5)



-- blocked by parent Update.

(commit;)





I think that the RLS on the parent table is upgrading the row lock, even if the 
RLS policy doesn’t do anything.

I can understand that the RLS will normally want access to fields in the row to 
validate the user request – but it has access to those anyway, for the update.



The RLS documentation says that Foreign Keys bypass RLS, so RLS on the parent 
shouldn’t make any difference to it.



If the RLS policy at (1) is dropped, or RLS disabled on table PL, the problem 
goes away.

With RLS, statement (4) is blocked by statement (3).

If the parent updates interleave with child updates, deadlock occurs, as 
statement (2) is also blocked by (4).



Is the RLS responsible for a lock upgrade?

Can I get around it, without disabling RLS for the update process on PL?



Thanks.



Phil Horder

Database Mechanic



Thales

Land and Air Systems

Horizon House, Throop Road, Templecombe, Somerset, BA8 0DH, UK

RE: Foreign Key locking / deadlock issue.... v2

[HORDER Phil]

Thanks Rob,

The security policy in the example was reduced to the bare minimum, to 
demonstrate the issue was with the existence of the policy, not what it did.
Obviously my real-application policy does much more, and uses other columns to 
do both read & write checks.

Adding the write check as suggested makes no difference to the demo - the 
blocking lock still occurs.


Phil Horder
Database Mechanic

-Original Message-
From: rob stone [mailto:floripa...@gmail.com] 
Sent: 22 March 2018 11:11
To: HORDER Phil; Adrian Klaver
Cc: pgsql-general
Subject: Re: Foreign Key locking / deadlock issue v2

Hello Phil,

On Wed, 2018-03-21 at 15:26 +0000, HORDER Phil wrote:
> OK,
> Let's try again, with a full script, and including the bit that makes 
> the difference…
>  
> 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 – because there is Row Level 
> Security on the parent table.
>  
>  
>  
> -- The setup
> 
>  
> drop table if exists ELN;
> drop table if exists PL;
>  
> Create table PL
> (pl_id integer,
> m_number text
> );
>  
>  
> alter table PL ENABLE row level security; alter table PL FORCE row 
> level security;
>  
> -- A dummy security policy, that allows everybody access to everything 
> drop policy if exists security_policy on PL ;

I don't use row level security but after reading the documentation, I'd alter 
this:-

> create policy security_policy on PL TO public using (true);   -- (1)

to

CREATE POLICY security_policy on PL FOR ALL TO PUBLIC USING (true) WITH CHECK 
(true);

and trying again.

HTH,
Rob

RE: Foreign Key locking / deadlock issue.... v2

[HORDER Phil]

Rob, thanks for looking.



The "pause" is only to not-do-the-commit yet, so that the child process can 
then try and access the record - I've not left anything out.

This code is my own demo, not a cut from our production code.



Did you run this as the 'postgres' superuser?  That would bypass the RLS, and 
probably avoid the problem.



I checked by creating a new user, and ran my code in that:



Sql> create user test password 'password';



After running my test script, psql \dp shows:



Schema |   Name| Type  |Access privileges| Column 
privileges | Policies

+---+---+-+---+--

public | eln   | table | |  
 |

public | pl| table | |  
 | security_policy:+

|   |   | | 
  |   (u): true +

|   |   | | 
  |   (c): true



(plus some other stuff for postGIS)



Here’s my code again:



drop table if exists ELN;

drop table if exists PL;



Create table PL

(pl_id integer,

m_number text

);



alter table PL ENABLE row level security;

alter table PL FORCE row level security;



drop policy if exists security_policy on PL ;

CREATE POLICY security_policy on PL FOR ALL TO PUBLIC USING (true) WITH CHECK 
(true);



Alter table PL add constraint PL_PK primary key (pl_id);

Insert into PL values (1, null);

Insert into PL values (2, null);

Insert into PL values (3, null);



Create table ELN

(event_id integer,

pl_id integer

);





Alter table ELN add constraint ELN_PK primary key (event_id);

Alter table ELN add constraint ELN_PL_FK foreign key (pl_id) references PL 
(pl_id);



Insert into ELN values (301, null);

Insert into ELN values (302, null);

Insert into ELN values (303, null);



commit;



-- process 1:

start transaction;

update pl set m_number = '234' where pl_id = 2;

update pl set m_number = '345' where pl_id = 3;



-- wait here, and run process 2

commit;





-- process 2:

start transaction;

update eln set pl_id = 3 where event_id = 303;

update eln set pl_id = 2 where event_id = 302;

-- Gets blocked by process 1

commit;





Phil Horder

Database Mechanic



-Original Message-
From: rob stone [mailto:floripa...@gmail.com]
Sent: 23 March 2018 11:43
To: HORDER Phil; pgsql-general
Subject: Re: Foreign Key locking / deadlock issue v2



Hello Phil,



I've run your sample script on 9.6.5 and 10.3.

The only thing that I added was a commit; after the initial inserts just to 
ensure the rows were saved.

No errors were reported for either version.



The output of \dp after running was:-



Access privileges  Schema | Name | Type  | 
Access privileges | Column privileges

| Policies

+--+---+---+---+---

---

public | eln  | table |   |   |

 public | pl   | table |   |   |

security_policy:+

|  |   |   |   |   (u):

true





--> including the FOR ALL in the create policy statement as well as

WITH CHECK(true).



   Access privileges  Schema | Name | Type  | 
Access privileges | Column privileges

| Policies

+--+---+---+---+---

---

public | eln  | table |   |   |

 public | pl   | table |   |   |

security_policy:+

|  |   |   |   |   (u):

true +

|  |   |   |   |   (c):

true





The only mystery is what happens here:-







-- …. Pause while other processing happens …..

(commit;)



-- Child table processing – occurs often & quickly. Starts after parent update.



<\snip>





I'd like to know more about RLS and trying to de-bug your script.



On a production application you'd be testing for errors and raising exceptions 
so as to inform users that a problem occurred.



So, without knowing what occurs during "Pause while other processing happens" I 
can't help any further.



Cheers,

Rob

RE: primary key and unique index

[HORDER Phil]

Some databases will create a unique index for you when you create a primary key.
Oracle will create one, but only if you haven’t already done that.

Postgres will ALWAYS create a unique index based on the primary key – so you 
should never do that as well, or the db will have to maintain two identical 
indexes.

(When we migrated our db design from Oracle we ended up with lots of duplicate 
indexes).

SQL> Select * from pg_indexes order by schemaname, tablename;

Phil Horder
Database Mechanic

Thales
Land and Air Systems
Horizon House, Throop Road, Templecombe, Somerset, BA8 0DH, UK
www.thalesgroup.com/uk

Tel: +44 (0) 1963 37 2041
Email: phil.hor...@uk.thalesgroup.com

Please consider the environment before printing a hard copy of this email.

The information contained in this e-mail is confidential. It is intended only 
for the stated addressee(s) and access to it by any other person is 
unauthorised. If you are not an addressee, you must not disclose, copy, 
circulate or in any other way use or rely on the information contained in this 
e-mail. Such unauthorised use may be unlawful. If you have received this e-mail 
in error, please inform us immediately on +44 (0)1963 370511 and delete it and 
all copies from your system.

Thales UK Limited. A company registered in England and Wales. Registered 
Office: 350 Longwater Avenue, Green Park, Reading, RG2 6GF. Registered Number: 
868273

From: Thomas Poty [mailto:thomas.p...@gmail.com]
Sent: 23 March 2018 07:56
To: pgsql-general@lists.postgresql.org
Subject: primary key and unique index

Hi all,
I am migrating fromMySQL to Postgresql 9.6.
In MySQL a  "show create table" gives me :
 ...
  PRIMARY KEY (`ID`,`CountryCode`,`LanguageCode`),
  UNIQUE KEY `unique_my table_4` (`ID`,`CountryCode`,`LanguageCode`),
...
So, In PostgreSQL, does it make sense to create a primary key AND a unique 
index based on the same columns?
Is PostgreSQL smart enough to use the unique index created for the primary key.
I know PostgreSQL can be based on a unique index to create a primary key but I 
also know it is possible to create several indexes on the same columns with the 
same order.
Thanks
Thomas

RE: Foreign Key locking / deadlock issue.... v2

[HORDER Phil]

> This was never explained. You are running "process 2" in an entirely 
> different session

Yes, two processes are required to get a deadlock.

> If that is true, why don't you commit the updates to table pl and release the 
> locks?

It's a long story... but I can't change it at the moment, the PL update is a 
single long-running batch process, which shouldn't get in the way of the ELN 
process.

> The fact that eln.pl_id is a foreign key is irrelevant. If you tried to alter 
> eln.pl_id to a value that doesn't exist in pl.pl_id will cause an exception 
> to occur due to the constraint.

I don't see how this is irrelevant, it's the FK check against the PL table that 
is causing the lock contention.
We're not getting FK violations - the data is being constructed correctly.

The issue I have is that the FK check is being blocked by an apparently 
upgraded lock on the PL table, caused by the RLS policy.

I'm running Postgres 9.6.1 (I think)


Phil Horder
Database Mechanic


-Original Message-
From: rob stone [mailto:floripa...@gmail.com] 
Sent: 24 March 2018 11:30
To: HORDER Phil; pgsql-general
Subject: Re: Foreign Key locking / deadlock issue v2

Hello Phil,

On Fri, 2018-03-23 at 15:42 +, HORDER Phil wrote:
> Rob, thanks for looking.
>  
> The "pause" is only to not-do-the-commit yet, so that the child 
> process can then try and access the record - I've not left anything 
> out.
> This code is my own demo, not a cut from our production code.
>  
> Did you run this as the 'postgres' superuser?  That would bypass the 
> RLS, and probably avoid the problem.

No. Never use super user to test scripts.

>  
> I checked by creating a new user, and ran my code in that:
>  
> Sql> create user test password 'password';
>  
> After running my test script, psql \dp shows:
>  
> Schema |   Name| Type  |Access privileges   
> | Column privileges | Policies
> +---+---+--
> ---+---+--
> public | eln   | table |
> |   |
> public | pl| table |
> |   | security_policy:+
> |   |   |  
>   |   |   (u): true +
> |   |   |
> |   |   (c): true
>  
> (plus some other stuff for postGIS)
>  
> Here’s my code again:
>  
> drop table if exists ELN;
> drop table if exists PL;
>  
> Create table PL
> (pl_id integer,
> m_number text
> );
>  
> alter table PL ENABLE row level security; alter table PL FORCE row 
> level security;
>  
> drop policy if exists security_policy on PL ; CREATE POLICY 
> security_policy on PL FOR ALL TO PUBLIC USING (true) WITH CHECK 
> (true);
>  
> Alter table PL add constraint PL_PK primary key (pl_id); Insert into 
> PL values (1, null); Insert into PL values (2, null); Insert into PL 
> values (3, null);
>  
> Create table ELN
> (event_id integer,
> pl_id integer
> );
>  
>  
> Alter table ELN add constraint ELN_PK primary key (event_id); Alter 
> table ELN add constraint ELN_PL_FK foreign key (pl_id) references PL 
> (pl_id);
>  
> Insert into ELN values (301, null);
> Insert into ELN values (302, null);
> Insert into ELN values (303, null);
>  
> commit;
>  
> -- process 1:
> start transaction;
> update pl set m_number = '234' where pl_id = 2;   
> update pl set m_number = '345' where pl_id = 3;  
>  
> -- wait here, and run process 2

This was never explained. You are running "process 2" in an entirely different 
session

If that is true, why don't you commit the updates to table pl and release the 
locks?
The fact that eln.pl_id is a foreign key is irrelevant. If you tried to alter 
eln.pl_id to a value that doesn't exist in pl.pl_id will cause an exception to 
occur due to the constraint.

Cheers,
Rob

> commit;
>  
>  
> -- process 2:
> start transaction;
> update eln set pl_id = 3 where event_id = 303; 
> update eln set pl_id = 2 where event_id = 302;
> -- Gets blocked by process 1
> commit;
>  
>  
> Phil Horder
> Database Mechanic
>  
> -Original Message-
> From: rob stone [mailto:floripa...@gmail.com]
> Sent: 23 March 2018 11:43
> To: HORDER Phil; pgsql-general
> Subject: Re: Foreign Key locking / deadlock issue v2
>  
> Hello Phil,
>  
> I've run your sample script on 9.6.5 and 10.

RE: Foreign Key locking / deadlock issue.... v2

[HORDER Phil]

psql> select version();
PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit