Re: How to handle deadlocks ?

In article <4pi4u1$5i@chleuasme.francenet.fr>, cberthet@MicroNet.fr writes: > In article <DsszAL.7MC@world.std.com>, bgw@world.std.com (Bruce G Wilson) says: > > > > > >I've been on a couple of contracting jobs where the client uses > >Sybase (4.9 and System 10). At each place, there were occasions > >where they had problems with deadlock: two processes trying to > >allocate two resources in opposite order. 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; with this, > >it would be made clear in the code where the locks are being taken > >and might make it easier to determine where the problem is. > > > >I'm wondering, what kinds of techniques are used in "standard" > >practice with Sybase to detect/prevent deadlocks ? Do IS > >organizations establish rules for accessing the database, like, > >"always do an update to table A before doing an update to table B" ? > >It strikes me that that's really the only way to control this > >sort of problem. > > > >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. > To understand why you have a deadlock you can activate two traceflags, > they will generate a stack in your errorlog with all the details > (store proc, pages..) about your deadlock. > Your applications must presume a deadlock can occurs anywhere, thus > code deadlock processing. See Sybase FAQ for those traceflags - see .sig. Pablo Sanchez | Ph # (415) 933.3812 Fax # (415) 933.2821 pablo@sgi.com | Pg # (800) 930.5635 -or- pablo_p@corp.sgi.com =============================================================================== I am accountable for my actions. http://reality.sgi.com/pablo [ /Sybase_FAQ ]