Re: Shared row locking

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.

> - use a lock mode called Cursor Stability that locks only those rows
> currently being examined by a cursor, those maintaining the lock usage
> of a cursor at a constant level as the cursor moves. The lock mode of
> Repeatable Read *does* lock all rows read

We can't release the locks until the end of the transaction.

> The second is a usable, practical alternative that should be considered
> and might avoid the need to write the spill-to-disk code and then
> discover it performs very badly.

I don't think any of them serves the objective I'm trying to accomplish
:-(

Thanks for your ideas anyway. And keep having them!

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"Uno puede defenderse de los ataques; contra los elogios se esta indefenso"