HEMPLEMAN Matthew wrote:
> I’m in the process of migrating a web application from Oracle to PostgreSQL and, after quite a few
> headaches, it’s almost there. The last step (I hope) is getting to the bottom of an intermittent
> locking issue I see when I run integration tests. Most of the time, all tests are passing, but
> occasionally a test will fail with a stale state exception. Another developer ran a few IT tests and
> said one run stalled and never finished. Based on a cursory look at the internal PG tables, he said
> it looked like too many tables were locked due locks propagating out via foreign keys. I’m not sure
> how this could be possible.
Assume you have two tables referencing each other, e.g.:
CREATE TABLE parent (
parent_id integer PRIMARY KEY,
parent_val text
);
CREATE TABLE child (
child_id integer PRIMARY KEY,
parent_id integer REFERENCES parent(parent_id),
child_val text
);
and an entry in "parent", like
INSERT INTO parent (parent_id, parent_val) VALUES (1, 'something');
Then if you insert something in "child", like:
BEGIN;
INSERT INTO child (child_id, parent_id, child_val) VALUES (42, 1, 'other');
you will have a lock on the corresponding row of the "parent" table as long as
the transaction lasts. This is necessary because if another concurrent session
were allowed to e.g. delete the entry in "parent", the foreign key constraint
would be violated.
Similar locks are taken for UPDATE or DELETE.
> I’ve been looking into the differences between PostgreSQL and Oracle locking behavior, but nothing has
> jumped out at me so far. I’m not a dba so hopefully I’m missing something obvious. Any advice would
> be greatly appreciated! Thanks.
Oracle and PostgreSQL do roughly the same thing in this case.
Which version of PostgreSQL is this? Before 9.3 the locks taken on parent tables were
unnecessarily strong, so you would encounter problems like the one you describe
more easily.
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0ac5ad5134f2769ccbaefec73844f8504c4d6182
Yours,
Laurenz Albe