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

[bo2l]

The process you see 'Running' should be your current dbisql process but not the mentioned 'hanging/zombie' process from gdb.
The Error '6104 - You cannot use KILL to kill your own process' will raise when you are trying to kill your current process.

You can verify your current spid by execute this command in dbisql:
select @@spid

To verify if there are hanging process in ASE server you can run:
select * from master..syslogshold
go

Are there spids returned? Check the timestamp to find which corresponds to the process of when gdb connected ASE. Then try to kill that spid.

[oneeyeman1]

@bo2l,

Thx for the reply.

Yes, I tried to run "SELECT @@spid" and it returned the one process that was running from "sp_who".

I also tried to run the second query but it didn't return any records.

So, what does it mean?

This is what happened:

1. I ran my C++ code with gdb and hit a problem inside transaction.

2. I interrupted that session without COMMIT/ROLLBACK, fixed the issue and restarted the gdb session.

3. At this point the program got stuck in side BEGIN TRANSACTION.

After googling, I saw that the way forward is "sp_who/KILL".

Do you know what can I do to identify the cause of my second gdb session to be stuck?

Thank you.

[bo2l]

I don't know much about the gdb/programming, but if syslogshold doesn't return any process and sp_who doesn't return other spids likely to be the 'gdb' process then the process should just not exist in ASE. From ASE perspective you can try restarting the server if possible.

[oneeyeman1]

@bo2l,

Thank you for the reply.

"select * from master..syslogshold" does not return any records.

I got that sequence trying to google.

Restarting the server does not help. Running the program still stuck in the "BEGIN TRANSACTION".

Any other idea?

Thank you.