🚀 go-pugleaf

> A DBMS would be well off with three (3) levels of locks,
> (operating system people LISTEN!)  We need shared, update, and
> exclusive locks. (This may be advisory since the data is accessed
> by special routines)
> 	Conflict table:
> 		S U E
> 	      S + + -
> 	      U + - -
> 	      E - - -
> Reasoning: If you think you might have to change some data
> in a file you get an update lock.  Read only routines can have
> shared locks.  If you find you don't have to change data you
> remove the lock or lower it to shared, allowing someone else to
> get an update lock.  If you do have to change the data you
> raise the lock to exclusive and wait until all the shared locks go away.
> Know what else goes away?  Deadlock.
> (Well, not completely but the problem is easier.)

I'm curious about this proposed scheme.  I can't see how it would make
deadlock less frequent, and why would it make it easier to deal with?
With two-level locking, deadlock happens if two transactions hold locks
and each tries to get locks that conflict with the other's.  For example,
t1 gets an exclusive lock on a, t2 gets a shared lock on b, t1 gets a
shared lock on b but blocks trying to escalate it to exclusive,
and t2 blocks trying to get a shared lock on a.  Using update locks,
t1 would get an update lock on a and escalate it to exclusive, t2 would
get an update lock on b and de-escalate it to shared, t1 would get an update
lock on b but would block trying to escalate it to exclusive, and t2 would
block trying to get an update lock on a.  You still get deadlocked, even
though the exact locks involved are not the same.  And since deadlock is
possible, one has to deal with it somehow, so I don't see why the problem
is easier.

Also, how do you decide when it is safe to de-escalate a lock from
update to shared?  This would be easy to do with single-statment
transactions, but RTI's version of INGRES has multi-statement transactions
(that is, one can have more than one QUEL statement within a transaction).
The only solution I can see is to de-escalate to shared for each object
touched but not altered in each statement, then re-escalate to update if
another statement re-reads the object with the possibility of changing it.
Under some circumstances, this could lead to a lot more locking activity
than one would encounter with two-level locking, which could be costly.
-- 
Jeff Lichtman at rtech (Relational Technology, Inc.)
aka Swazoo Koolak

{amdahl, sun}!rtech!jeff
{ucbvax, decvax}!mtxinu!rtech!jeff