Transactions and RowExclusive locks on foreign key tables

Started by Donald Fraserabout 23 years ago2 messagesbugs
Jump to latest
#1Donald Fraser
demolish@cwgsy.net

I have a question regarding transactions.
When updating a row of a table PostgreSQL obtains a RowExclusive lock, which according to the docs means that no other transaction can obtain a RowExclusive lock on this row in the same table until the existing one is released. That seems fair enough when inserting, updating or deleting rows from a table.
But why does PostgreSQL need a RowExclusive lock on the foreign key table when it is not going to update the row in the foreign key table? Surely it only needs a sharable lock that stops other transactions updating or deleting this row.

To understand exactly what I mean and the problem that it creates consider the following:

CREATE TABLE tbl_atable ( id INT4 NOT NULL, somedata TEXT, otherdata TEXT, id_user INT4 NOT NULL, CONSTRAINT tbl_atable_id_pkey PRIMARY KEY (id));
CREATE TABLE tbl_users ( id INT4 NOT NULL, name TEXT NOT NULL, CONSTRAINT tbl_users_id_pkey PRIMARY KEY(id));
INSERT INTO tbl_users (id, name) VALUES('1', 'a name');
INSERT INTO tbl_atable (id, id_user) VALUES('1', '1');
INSERT INTO tbl_atable (id, id_user) VALUES('2', '1');

Transaction A:
BEGIN;
UPDATE tbl_atable SET somedata = 'foo' WHERE id = '1';

Before the above transaction issues a COMMIT or ROLLBACK statement the following occurs from another process:

Transaction B:
BEGIN;
UPDATE tbl_atable SET otherdata = 'foobar' WHERE id = '2';
COMMIT;

Currently I observe that Transaction B is able to complete before Transaction A when using PostgreSQL.

Now we add a foreign key constraint such as:
ALTER TABLE tbl_atable ADD CONSTRAINT tbl_atable_fkey FOREIGN KEY (id_user) REFERENCES tbl_users (id) ON DELETE RESTRICT ON UPDATE RESTRICT NOT DEFERRABLE INITIALLY IMMEDIATE;

Now that we have this constraint condition the above transaction scenario no longer holds true. That is, Transaction B cannot complete until Transaction A completes.
I checked the view on pg_catalog.pg_locks and found that PostgreSQL was obtaining a RowExclusive lock on the foreign key table - tbl_user. Because both transactions reference the same row in the foreign key table - tbl_user, Transaction B must wait until Transaction A releases its RowExclusive lock on it.

I also note that if the foreign key is specified as ... DEFERRABLE INITIALLY DEFERRED then we can achieve the scenario where Transaction B can complete before Transaction A.
It still doesn't explain why a RowExclusive lock is required on the foreign key table, when we want be updating it?

Apologies in advance to those experienced users if I have overlooked something fundamental and obvious.

Thanks
Donald Fraser.

#2Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Donald Fraser (#1)
Re: Transactions and RowExclusive locks on foreign key tables

On Thu, 13 Mar 2003, Donald Fraser wrote:

I have a question regarding transactions.

When updating a row of a table PostgreSQL obtains a RowExclusive lock,
which according to the docs means that no other transaction can obtain
a RowExclusive lock on this row in the same table until the existing
one is released. That seems fair enough when inserting, updating or
deleting rows from a table.

But why does PostgreSQL need a RowExclusive lock on the foreign key
table when it is not going to update the row in the foreign key table?
Surely it only needs a sharable lock that stops other transactions
updating or deleting this row.

There isn't currently such a lock in PostgreSQL, the row exclusive lock
was what was available at the time. This has been discussed on -general
and -hackers in the near past, you might want to look at the archives.