Re: How to handle deadlocks ?

Robert David wrote: > > In <4pi4u1$5i@chleuasme.francenet.fr> cberthet@MicroNet.fr (Christophe > Girard-Berthet) writes: > > > >In article <DsszAL.7MC@world.std.com>, bgw@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@dircon.co.uk Editor, NEWS/400.UK +44 (0)161 929 0777