Re: foreign key constraint lock behavour in postgresql

Robert Haas wrote:
> Just for kicks I tried this out and the behavior is as the OP
> describes: after a little poking around, it sees that the INSERT grabs
> a share-lock on the referenced row so that a concurrent update can't
> modify the referenced column.
>
> It's not really clear how to get around this. If it were possible to
> lock individual columns within a tuple, then the particular update
> above could be allowed since only the name is being changed. Does
> anyone know what happens in Oracle if the update targets the id column
> rather than the name column?

I have investigated what Oracle (10.2) does in this situation.

First the original sample as posted by wangyuxiang:

insert into tb_a(id,b_id) values('a1','b1');

will place a ROW EXCLUSIVE lock on tb_a, an EXCLUSIVE lock
on the row that was inserted and a ROW SHARE lock on tb_b.
No lock on any row in the parent table is taken.

update tb_b set name='changed' where id='b1';

will place a ROW EXCLUSIVE lock on tb_b and an EXCLUSIVE
lock on the modified column.

Since ROW EXCLUSIVE and ROW SHARE do not conflict, both statements
will succeed.

Now to your question:

update tb_b set id='b2' where id='b1';

This will place a ROW EXCLUSIVE lock on tb_b, an EXCLUSIVE lock
on the updated row and a SHARE lock on tb_a.
This last lock is only held for the duration of the UPDATE statement
and *not* until the end of the transaction.

So this update will block, because the SHARE and the ROW EXCLUSIVE
lock on tb_a are incompatible.

So it seems that Oracle handles this quite differently.
I was particularly surprised that it uses locks that are not held
until end-of-transaction.

Yours,
Laurenz Albe