|
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.
|
