I have a question regarding transactions.
When updating a row of a table PostgreSQL obtains a RowExclusive lock, whic=
h according to the docs means that no other transaction can obtain a RowExc=
lusive lock on this row in the same table until the existing one is release=
d. That seems fair enough when inserting, updating or deleting rows from a =
table.=20
But why does PostgreSQL need a RowExclusive lock on the foreign key table w=
hen it is not going to update the row in the foreign key table? Surely it o=
nly needs a sharable lock that stops other transactions updating or deletin=
g 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 t=
bl_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 =3D 'foo' WHERE id =3D '1';
Before the above transaction issues a COMMIT or ROLLBACK statement the foll=
owing occurs from another process:
Transaction B:
BEGIN;
UPDATE tbl_atable SET otherdata =3D 'foobar' WHERE id =3D '2';
COMMIT;
Currently I observe that Transaction B is able to complete before Transacti=
on A when using PostgreSQL.
Now we add a foreign key constraint such as:=20
ALTER TABLE tbl_atable ADD CONSTRAINT tbl_atable_fkey FOREIGN KEY (id_user)=
REFERENCES tbl_users (id) ON DELETE RESTRICT ON UPDATE RESTRICT NOT DEFERR=
ABLE INITIALLY IMMEDIATE;
Now that we have this constraint condition the above transaction scenario n=
o longer holds true. That is, Transaction B cannot complete until Transacti=
on A completes.=20
I checked the view on pg_catalog.pg_locks and found that PostgreSQL was obt=
aining a RowExclusive lock on the foreign key table - tbl_user. Because bot=
h 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 INITIALL=
Y DEFERRED then we can achieve the scenario where Transaction B can complet=
e 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 someth=
ing fundamental and obvious.
Thanks
Donald Fraser.
