How to kill a session

[scott_xu]

I want to kill a session in HANA version 80.  Through HANA Studio, I found the session and kill the session by 'Cancel session...'. The session status was changed to 'RUNNING(CANCEL REQUESTED)', but it never be killed.  Instead of rebooting the HANA, is there any way to real kill the session?

Thanks,

Scott

[lbreddemann]

It has been explained a couple of times before and I am not sure why it still seems to be a mystery that sometimes sessions don't get cancelled immediately (or ever).

From my book SAP HANA Administration | Book and E-Book - by SAP PRESS:

10.2.4 Killing a Session
Sometimes it might be desirable to not only cancel a currently running statement of a session but to disconnect the session completely. The command for this is ALTER SYSTEM DISCONNECT SESSION '<CONNECTION ID>' and can be used similarly to the CANCEL SESSION command. The difference between this and the CANCEL WORK command simply is that not only will the current command be cancelled but the session itself will be terminated.

Note
Disconnecting the session is not a “stronger” way to force the termination of a long running command. With either option, the running command first needs to recognize the cancellation flag and then the transaction rollback must be performed, which also can take a considerable amount of time. If the CANCEL WORK command did not succeed to stop a running command, then there is no point in trying DISCONNECT SESSION.

10.2.5 Problems with Session Cancellation

As you have seen, the ability to stop running commands and disconnect sessions relies on the idea that the session that should be cancelled actively checks for the cancellation flag to be set. This approach has the drawback that there are situations  in which the session cannot check the flag and thus can never be cancelled.
For example, a session could open a transaction and update a table but not commit the update. Instead, the session no longer does anything and therefore just holds a lock on the changed record (e.g., think of a user having a data-entry mask open and unsaved leaving for lunch). If other sessions now need to change this record as well, they need to wait until the first session releases the lock.

In this situation, a CANCEL SESSION on the lock holder session does not help, because the session is currently not running any code that could check for the cancellation flag. This situation, in which the session is IDLE, can only be resolved by disconnecting the session.

The problem with this is obvious: What if the client application is currently legitimately processing the data and should write the results back to the database? In this case, the work is lost and the session needs to be restarted. Therefore, it is important to try to find out why the session is currently inactive while a transaction is open and decide on a case-by-case basis whether or not to disconnect the session. (We will see how to find out who is running a session in Section 10.3.)

A second problem is that sometimes it takes several minutes until the rollback of a session is performed and the cancellation is successfully finished. During that time, there are no visible signs, in the Threads monitor, for example, that the specific thread is flagged for termination. This might lead to DBAs trying to run the CANCEL SESSION command over and over again until it works. Clearly, this is futile.

To double-check whether the session control commands have been understood, you can examine the index server trace file in Listing 10.4.
[...]
[12351]{301610}[-1/-1] 2013-12-19 03:03:05.410801 i SQLSessionCmd
Statement.cc(03254) : 
session control command is performed by 301610, 
user=LARS, application user=I028297, 
application source=csns.sql.editor.SQLExecuteFormEditor$1$1
.run(SQLExecuteFormEditor.java:796);
, query=ALTER SYSTEM CANCEL SESSION '301611'
[...]
Listing 10.4 Index Server Trace File Excerpt

In the case that a statement or session cancellation does not succeed at all, it could be that SAP HANA currently executes a routine that does not check (yet) for the cancel Flag. To find these routines and to improve them, SAP HANA development needs to know about them. Such information can be gathered by the means of a runtime dump. SAP Note 1951590 covers this.

In such a case, unfortunately, the only chances to stop the running session are to trigger the client to release the lock (if that is possible), to stop the client process, or, as a very last resort, to stop and restart the whole indexserver process, which will also end all other sessions.

Therefore, before taking this very last step, consider if the cancellation can wait until, for example, the majority of users has logged off and important jobs have finished. Also, you should open an incident with SAP Support for this situation and make sure to collect the runtime dump so that the root cause can be analyzed even though the system was restarted. In short, do not rush to cancel threads by restarting the system!

[former_member182967]

Hi Scott,

Additionally, for how to correctly kill/cancel session in SAP HANA, please refer to note 2092196 - How-To: Terminating Sessions in SAP HANA

Regards,

Ning

[former_member380929]

Hello, it seems that the SAP note does not load, do you remember what was the solution proposed by the note?

[nicholas_chang]

Hi Scott xu,

you can try below:

ALTER SYSTEM DISCONNECT SESSION '<connection_id>'

hdbcons 'transaction c <transaction_id>'

it'll look longer time to cancel a long running query. refer to note 2092196, 233401

hope it helps,

Nicholas Chang

[scott_xu]

Thanks Nicholas,

I tried your scripts. Both scripts runs successfully. But the session still exists when I checked.

Scott

[former_member183326]

Try killing it from hdbcons

[scott_xu]

I tried it from hdbcons.

> hdbcons 'transaction c 59'

Successfully cancelled transaction 59

[OK]

It's strange. I still see the session with transaction 59.

Scott

[muthuram_shanmugavel2]

We too faced the same issue.

I clicked Cancel Request and execute SQL queries

ALTER SYSTEM CANCEL WORK IN SESSION, DISCONNECT SESSION.

But It did not stop.

(I did not try hdbcons 'transaction c  command at that time)

Is this command to be executed from Unix?

Finally We could not kill the session and That session was keep on running and used CPU memory more than 95%.

So we did DB restart and closed the session.

It would be great if we get some solution to kill the sessions without DB restart.

Regards,

Muthuram

[former_member183326]

Usually in this case it is best approached by doing the following BEFORE the restart:

1: Run 5 runtime dumps. Make sure each runtime dump has 2-3 min gap between executions.

2: Run a kernel profiler trace.

Then by checking the wait.dot file you can check which call stack is causing the issue. Finding the call stack allows you then to find the thread which will lead to problem SQL.

Please see here for more information.

Without the above steps it is impossible to get an idea of what was happening. Chances are the statement you were trying to cancel was not the issue and it was maybe a different session that was actually causing the issue.