Re: Concurrency in an RDB

Roy Hann wrote:
> "David" <davidbl_at_iinet.net.au> wrote in message
> news:1165564572.553799.62710_at_f1g2000cwa.googlegroups.com...
> > Bob Badour wrote:
> > I don't have the RDB experience to know how often and to what extent
> > dead-lock seriously degrades performance. However, I have heard of
> > real cases where repeated dead-lock kills performance.
>
> Since you claim not to be a DB programmer I want to make sure I share the
> understanding of the words you're using here--and there are loads of DB
> programmers who use the same words and get it wrong.
>
> When you say "deadlock" do you mean a deadly embrace, in which no
> transaction can terminate even in principle? Or do you mean a prolonged
> wait for a lock?

A deadly embrace

> In principle a deadlock needn't cause a performance problem. It can, in
> principle, be detected quickly and resolved quickly.

In practice it can only be detected after a cycle appears in the wait-for graph, and that could happen after both transactions have performed most of their work.

Curiously a "solution" I have seen (to a system that suffered from performance degradation) was to follow the rule that transactions tend to update (and therefore lock) the state that causes the greatest contention first. This reduces dead-lock and concurrency. In practise total throughput can increase! In pathological cases this can be significant.

Note that the real point I want to make has little to do with dead-lock issues. I regard that as just the icing on the cake. The real benefit stems from the better concurrency, load balancing and cache management achievable with my proposal. It depends crucially on the claim that mutative work can and should involve very fast, small transactions, and CPU intensive work only requires shared read access to all the data. I would prefer to discuss this claim, if anything!

Cheers,
David Received on Fri Dec 08 2006 - 11:16:44 CET