Re: foreign key constraint lock behavour in postgresql

On Fri, Feb 5, 2010 at 4:00 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> 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.

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

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.

...Robert