Running KILL

[oneeyeman1]

Hi, ALL,

This is a follow-up to

https://community.sap.com/t5/technology-q-a/debugging-software-that-connects-to-sap-ase/qaa-p/144014....

So I tried to use Google. Turns out comparing to other DBMSes, like SQL Server, Postgres or MySQL, SAP ASE does not clean up if the connection is broken unexpectedly, just as I suspected in the last post in the linked thread.

So, when I tried googling, it suggest to do following:

1. Run sp_who to identify the (still) running process.

2. Run "KILL <spid>" to kill the process that is stuck.

So I ran "dbisql", connect as "sa" and then ran "sp_who". It succeeded and gave me the spid of the process that is stuck.

Next I I tried to run "KILL" on that spid. However I got an error saying that I can't kill the process that I started.

What do I do?

I presume that even if I create a user in the "sa" domain, it will not be able to kill the process, because it started as "sa"?

Or not?

And if not - how to create a user in "sa" domain?

Thank you.

[ChrisBaker]

What 'status' reported by sp_who is indicating that a spid requires 'kill'ing? How is the connection being broken unexpectedly??

[oneeyeman1]

@ChrisBaker,

"status" reported is "RUNNING" and the "cmd" is "INSERT".

I was debugging my software and hit the bug. So I close debugging session and so the connection should be closed, all transaction rolled back.

Thank you.

[ChrisBaker]

Rereading this, it appears that you tried to kill a process that was your own connection via isql/dbisql. If no other spid showed, that implies that the connection did clean up. For now I would suggest examining the actual application to see if it is coded correctly instead of trying to debug ASE or the driver.

[sladebe]

Re: Nothing in syslogshold.  Restarting the (ASE) server does not help. (re) Running the program still stuck in the "BEGIN TRANSACTION".

How do you know you're stuck in "begin transaction" state.   Is that something you see in the sp_who output in ASE?

The 'begin transaction" command doesn't block on anything, it would be an insert/update/delete (or maybe select) operation inside the open transaction (after the "begin transaction" statement) that actually blocks.

Once you re-run gdb, and your client program submits SQL to the ASE server that hangs, can you connect to the ASE server and run this SQL (shown here with the sqsh utility -mvert option to display rows vertically):

select p.spid, p.status, p.hostname, hostpid=p.hostprocess, p.program_name, p.cmd, BlockedOnSpid=p.blocked,
  db=l.DBName,obj=object_name(l.ObjectID,l.DBID),l.LockID, l.LockState, l.LockType, l.LockLevel,
  WaitSecs=l.WaitTime, l.BlockedState, BlockedByLockID=l.BlockedBy
from master..sysprocesses p, master..monLocks l
where p.spid=l.SPID and p.spid!=@@spid
and BlockedState is not null
go -mvert

You might see something like this:

spid:            131
status:          recv sleep
hostname:        host22
hostpid:         2076467
program_name:    sqsh-2.4
cmd:             AWAITING COMMAND
BlockedOnSpid:   0
db:              mytest
obj:             mytbl
LockID:          262
LockState:       Granted
LockType:        exclusive intent
LockLevel:       TABLE
WaitSecs:        NULL
BlockedState:    Blocking
BlockedByLockID: NULL

spid:            305
status:          lock sleep
hostname:        host22
hostpid:         3178717
program_name:    sqsh-2.4
cmd:             LOCK TABLE
BlockedOnSpid:   131
db:              mytest
obj:             mytbl
LockID:          610
LockState:       Requested
LockType:        exclusive table
LockLevel:       TABLE
WaitSecs:        154
BlockedState:    Blocked
BlockedByLockID: 262