Re: How to handle deadlocks ?

In <DsszAL.7MC_at_world.std.com> bgw_at_world.std.com (Bruce G Wilson) writes:
>
>
>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 ?

I have had deadlock problems in System 10, also. I found a one sentence reference in the Sybase manuals that recommend application code to handle dealocks if more than one process is accessing a given table. Our solution is to retry up to 10 times before executing an error routine.
I have also encountered deadlocks while doing a select. I didn't think this would be possible. I talked to a Sybase expert at the International Sybase User Group in San Diego in May. The response I got was an obnoxious "What do you expect from multile processes updating the same table?". I pointed out that a deadlock should not be possible when doing a select. The "expert" told me that Sybase did things differently. Good luck! Received on Tue Jun 18 1996 - 00:00:00 CEST