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

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