Killing sleeping DB processes

[Brian Luther]

We have a problem with a Sybase 4.9.2 server running under HP/UX and
PC-based clients (home grown VB app). If the user gets impatient because
some database query or update takes too long, they'll reboot which leaves
the connection to the database hanging indeterminate. The other part of
the puzzle is that the application is doing BEGIN TRAN and
COMMIT/ROLLBACK in the client code rather than in the stored procedure
that is called, so when the connection is left hanging, there are locks
still be held by that database processes which get in the way of other
clients trying to do the same function.

So, I guess one solution is to put the transaction processing inside the
stored procedure since even if the connection goes away, the stored
procedure should continue to completion and commit or rollback.

The only other way I can see is to KILL the sleeping processes, but the
KILL is deferred until the process wakes, correct? There appears to be
some internal timeout that'll wake these processes after a couple of
hours? Is that timeout configuraable? And, we don't want to down the server
to get rid of them, which leaves the need for a back door to kill a sleeping
processes immediately.

Anyone been down this road?

thanks,
brian

--
------------------------------------------------------------------------------
brian luther, p.eng mailto:brian....@freenet.hamilton.on.ca
stoney creek, ontario http://www.freenet.hamilton.on.ca/~ab644/Profile.html

[CliffordA]

I'm using 4.9.2 and I have had pretty good luck killing sleeping spids.
My client is PowerBuilder with AutoCommit=false set in SQLCA which means
PowerBuilder handles the begin tran and commit - very nasty. Users get
impatient, give the three finger salute, and presto, orphan spid. Some
people diddle with the keep alive but sometimes this does not work.

Anyway, I make a dbcc call (undocumented by Sybase, except on Compuserve,
among other places) that returns the oldest open transaction spid # . I
then kill that spid and run my dbcc again which gives me bogus information
but then I run it a second time which gives me the new oldest open
transaction spid. My sleeping spid has awoke only to get its throat cut.
It just seems to work. I do run checkpoints before each dbcc call.

Much luck and may all your spids drink lots of coffee.

[sekar jayaraman]

USE CAUTION:

This is a two step process and has to be done sequentially.

Step 1: Kill the process using Kill command. This will wait for the
sleeping process to wakeup.

Step 2: Use the lct_admin command. Please refer to Sybase manual for
more information and impacts. The second command to wakeup the sleeping
process is :
Select lct_admin("UNSUSPEND",17)
Note: "17" is the database ID of the database in which the "Sleep"
process is resident. This command will wake up the process and the
pending Kill command will IMMEDIATELY kill the process.

Shekar Jayaraman. CCSA, Inc.

In <4epd8r$a...@newsbf02.news.aol.com> clif...@aol.com (CliffordA)
writes: