Re: How to update multiple rows atomically

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 29 Jul 2006 16:38:04 GMT
Message-ID: <M9Myg.28617$pu3.386765@ursa-nb00s0.nbnet.nb.ca>

Marshall wrote:

> Bob Badour wrote:
> 

>>Marshall wrote:
>>
>>
>>>What makes me think any of those things is my lack of a mental model
>>>of locking in DBMS products.
>>>
>>>So, *would* the above update, (or my earlier one) being made from
>>>a variety of processes, be subject to deadlock or starvation? It
>>>seems clear it would be immune to race conditions, but again
>>>I don't really have a sufficient mental model to say.
>>>
>>>Alternatively, the more important question is: what can I go read
>>>so as to be able to answer the above question myself?
>>
>>Ah, now, there's the rub. Whether it might experience deadlock or
>>starvation or any other concurrency problem will depend on what
>>concurrency mechanisms the dbms implements, which concurrency options
>>the dba and various users choose, and how the dbms implements them. It
>>will also depend on the other concurrent queries.
>>
>>Most dbmses detect deadlock automatically and use timeout to deal with
>>starvation etc. They don't do much to prevent deadlock or starvation etc.

> 
> Well, crap. That's no fun.
> 
> So, does this condition represent a theoretical limit, a lack of a
> decent model, or just lousy implementations?

None of the above and all of the above.

It is possible to avoid deadlock, as you observed, using canonical locking orders. However, doing that will preclude lazy evaluation in at least some cases and will limit the choice of access paths thereby affecting physical independence. This, then, gets into the nitty gritty world of engineering design tradeoffs.

I particularly enjoyed the paper recently posted to the group that discussed proofs. In particular, the authors proved the maximium resource usage of their program and then turned off runtime resource checks.

I would like to see systems capable of more global optimizations that could identify and automate or assist such tradeoff decisions. Thus, the dbms could optimize some universe of code to avoid deadlock while allowing ad hoc queries that could cause deadlock. When an ad hoc query involves a deadlock-free query in a deadlock situation, the ad hoc query would always lose with an error allowing the deadlock-free query to proceed. The dbms would only have to check for deadlocks for the ad hoc queries and one could write applications that do not account for deadlock errors knowing that none could ever occur. Received on Sat Jul 29 2006 - 11:38:04 CDT