Re: improving foreign key locks

On Nov26, 2010, at 21:06 , Alvaro Herrera wrote:
> Excerpts from Florian Pflug's message of vie nov 26 10:48:39 -0300 2010:
>> On Nov25, 2010, at 23:01 , Alvaro Herrera wrote:
>>> So I've been working on improving locks for foreign key checks, as
>>> discussed in a thread started by Joel Jacobson a while ago. I've posted
>>> about this:
>>> http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/
>>> http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks_part_2/
>>
>> To me, the whole thing seems to be special case of allowing to not only lock whole tuples FOR UPDATE or FOR SHARE, but also individual fields or sets of fields. Except that for simplicity, only two sets are supported, which are
>> A) All fields
>> B) All fields which are included in some unique constraint, including primary keys.
>>
>> I'd therefore suggest to extend the FOR SHARE / FOR UPDATE syntax to be
>> SELECT FOR { SHARE | UPDATE } [ OF <table1>[.<field1>], ... ]
>> and obtain what you call a "KEY LOCK" if (for a particular table) the set of fields is a subset of (B). Otherwise, we'd obtain a full SHARE lock. Thus we'd always lock at least the fields the user told us to, but sometimes more than those, for the sake of a more efficient implementation.
>
> The problem with this idea is that it's not possible to implement it.

How so? The implementation you proposed in your blog should work fine for this. XMAX_KEY_LOCK would signal that only fields from set (B) are locked, while XMAX_SHARE_LOCK (or however thats called, didn't check the code) would signal that all fields are locked. These are the only two field sets that we'd support, and for any set of columns the user specified we'd pick the smallest superset of the set we *do* support and use that (Thus, we obtain a key lock if only fields from a unique index where specified, and a share lock otherwise).

The only difference is that instead of presenting this to the user as an entirely new lock type, we instead present it as a generalization of SHARE locks. The advantage being that *if* we ever figure out a way to support more fine-grained locking of fields, (say, locking only the fields contain in some *specific* index, maybe by storing locking the index tuple), we can do so completely transparent to the user.

greetings,
Florian Pflug