Re: How to handle deadlocks ?

Robert David wrote:
>
> In <4pi4u1$5i_at_chleuasme.francenet.fr> cberthet_at_MicroNet.fr (Christophe
> Girard-Berthet) writes:
> >
> >In article <DsszAL.7MC_at_world.std.com>, bgw_at_world.std.com (Bruce G
> Wilson) says:
 

> >>It struck me that part
> >>of the reason why this is such a problem is that locks are taken
> >>(and escalated) implicitly in Sybase, so that it's difficult from
> >>a superficial read of the code to tell when a lock is being taken
> >>and where the conflict might be occurring. I guess I'm kind of
> >>surprised to find that locks can't be taken explicitly
> >>
> >>
> >>I can't imagine that other databases (Oracle, Informix) don't also
> >>have this kind of problem. How are deadlocks handled in these ?
> >>
> >
> >Ordering access to the objects in your database is the best way
> >to REDUCE dealock, keep transactions as short as possible too.
>
> Some large sites have thrown hardware at the problem. There was an
> article in ComputerWorld 'bout 2 years ago on this very subject. It
> seemed that going to faster I/O ("keeping the transactions as short as
> possible") made a world of difference.

These are both good tips. Keeping the transactions as short as possible, of course, minimises the amount of time any one transaction will be hogging a table; it also minimises your chances of rolling back if it all goes pear-shaped. Swings and roundabouts.

However, I'm surprised at the way Sybase handles locks (even in S. 10). It sounds like a recipe for trouble.

Oracle, from v7 onwards at least, has (a variety of) implicit and explicit locks; does not escalate locks implicitly; & does not lock on select (if you exclude SELECT FOR UPDATE).

And DB2/400... I'll get back to you on that one.

-- 
Phil Edwards                    news-uk_at_dircon.co.uk
Editor, NEWS/400.UK             +44 (0)161 929 0777