Re: foreign key constraint lock behavour in postgresql

Robert Haas wrote:
[explanation of how Oracle locks on Updates involving foreign keys]
>
> Yeah, that seems odd. I assume they know what they're doing; they're
> Oracle, after all. It does sound, too, like they have column level
> locks based on your comment about "an EXCLUSIVE lock on the modified
> column". I doubt we're likely to implement such a thing, but who
> knows.

Sorry, that was a mistake. I meant "an EXCLUSIVE lock on the modified
row". Oracle works quite like PostgreSQL in locking modified rows.

> Another interesting point is that a statement that involves
> only tb_b can trigger a share lock on tb_a; presumably that means they
> know they need to take a share lock on every table that references the
> updated column, which seems like it could be fairly expensive in the
> worst case.

Yes, that's the only way Oracle's method makes sense, by taking out
a shared lock on every table that references the updated table.

It may be expensive, but as the example shows, it also allows concurrency
in a way that PostgreSQL doesn't, so maybe it's worth the pain.

On the other hand, Oracle has some problems that PostgreSQl doesn't.
If you run the following example, assuming the original setup of
wangyuxiang:

SESSION 2:
BEGIN;
UPDATE tb_b SET id='b2' WHERE id='b1';

SESSION 1:
INSERT INTO tb_a (id,b_id) VALUES ('a1','b1');

SESSION 2:
UPDATE tb_b SET id='b1' WHERE id='b2';
COMMIT;

it will succeed just fine on PostgreSQL (with SESSION 1 blocking until
SESSION 2 COMMITs), but on Oracle it will cause a deadlock aborting
SESSION 1.

So, according the the principle of preservation of difficulties, both
implementations have their snags, and I wouldn't say that PostgreSQL
is worse off.

> One idea that occurs to me is that it might be possible to add to PG
> some tuple lock modes that are intended to cover updates that don't
> touch indexed columns. So, say:
>
> SHARED NONINDEX - conflicts only with EXCLUSIVE locks
> SHARED - conflicts with EXCLUSIVE or EXCLUSIVE NONINDEX locks
> EXCLUSIVE NONINDEX - conflicts with any lock except SHARED NONINDEX.
> must have this level or higher to update tuple.
> EXCLUSIVE - conflicts with any other lock. must have this to update
> any indexed column of a tuple.
>
> Then a foreign key constraint could take a SHARED NONINDEX lock on the
> target tuple, because any column that's the target of a foreign key
> must be indexed; and so we don't care if the nonindexed columns get
> updated under us. I think. Also, I believe you'd also need to
> duplicate any SHARED NONINDEX locks for any new versions of the tuple
> that got created while the lock was held, which might be sticky.

That should work and improve concurrency in PostgreSQL!

Yours,
Laurenz Albe