Re: Shared row locking

On Sun, 19 Dec 2004, Alvaro Herrera wrote:

> On Sun, Dec 19, 2004 at 09:52:01AM +0000, Simon Riggs wrote:
>
> Simon,
>
>> In similar circumstances, DB2 uses these techniques:
>>
>> - when locktable X % full, then escalate locks to full table locks: both
>> locktable memory and threshold% are instance parameters
>
> This is not useful at all, because the objective of this exercise is to
> downgrade locks, from exclusive row locking (SELECT ... FOR UPDATE) to
> shared row locking.  Keep in mind that this is for foreign key locking,
> which is one area where deadlocks are frequently encountered because we
> use too strong a lock.

Actually it might help in some scenarios. Remember, we're not talking 
about upgrading shared locks to exclusive locks. We're only talking about 
locking more rows than necessary (all rows).

I believe DB2 does the escalation also for perfomance. Getting a full 
table lock is cheaper than individually locking every row.

- Heikki