Re: Concurrency in an RDB

Bob Badour wrote:
> Christopher Browne wrote:
>

>> In an attempt to throw the authorities off his trail, Bob Badour 
>> <bbadour_at_pei.sympatico.ca> transmitted:
>>
>>> Transactions may acquire and release many locks over the duration of
>>> the transaction which may be orders of magnitude longer than the
>>> duration of any lock.
>>
>>
>> Are you sure they ought to release them?
>>
>> I seem to recall there being some anomalies where, if locks get
>> released, it gets easier for deadlocks to occur...

The 2PL protocol basically states that no locks are acquired after any locks have been released (see Gray). This definition of transaction is a very strict and safe one, although people often complain that it reduces concurrency. Safe in the sense that if followed, it guarantees that no commit will fail.

When I have heard such complaints, the lack of thought given to the transaction design was blatant, not to mention the lack of attention paid to the real business requirements. Often the design was a cookbook one that somebody had found useful elsewhere. The starvation problems I used to see often coincided with EAV designs where a dbms was taking locks left right and centre an nobody had any idea exactly what rows and tables were being locked without resorting to ponderous traces.

Note that 2PL refers to logical locks, for example predicate locks, not the physical locks which might be taken by an engine for internal reasonbs and which might be very short-lived, as small as 100 microseconds or maybe even less.

p Received on Thu Dec 14 2006 - 23:27:10 CET