How To Kill Process/Thread in HANA

[Former Member]

Hi Guys,

I have a statement running which is halting the system.

Host: hana

Port: 30003

Service: indexserver

Hierarchy: 0

Thread ID: 25529

Thread Type: SqlExecutor

Thread Method: ExecuteStatement

Thread Detail: ALTER TABLE "DM"."B_DIMM3" DISABLE DELTA LOG

Duration [MS]: 2129231 <----

---

Caller:

Calling:

User: SYSTEM

This should take seconds.

So, I want to Kill this process/thread.

Does anybody know how?

Best Regards,

Rasmus

[Former Member]

Hello All,

I ran an expensive insert statement and due to this my HANA studio hanged. So I had to kill my HANA Studio and reopen it.

Now that I have reopened i guess this will have a new session and new thread Id. In this case how do I find out original session Id or thread id - in which I ran the expensive insert statement ?

Thanks,

Chandan S Murthy

[Former Member]

go to tab performance->session, right-click the session, cancel session.

Please give feedback.

[virendra_raval]

I am using SPS07 and running the following statement to import a schema's all objects into another schema.

IMPORT "Schema_A"."*" FROM '/home/hanauser/hana_backup' with RENAME SCHEMA Schema_A TO Schema_B

The statement keeps in "running" state infinitely (already running for 3 hours).

I tried cancelling session using the option provided but the session remains unchanged and it shows same status ie 'RUNNING'.  I also tried executing the "alter" statements but not able to kill the session. 

In such situation is there anything we can try except resetting whole HANA DB Instance?

[lbreddemann]

Hi Virendra

there is never a way to "force" the cancellation of a session, just a way to flag a session for "self-destruction".

It may be that the code part your import session was in just didn't check for this flag or that it actually was waiting for a lock on something.

In such a case, you either can just wait (eventually a timeout will stop the waiting for locks) or if you feel it's really urgent to stop this session, you have to restart the instance.

Usually this is a last measure that can be avoided by e.g. waiting out.

- Lars

[Former Member]

Hi Lars,

that "self-destruction" flag to a session that you talked about, how exactly I can do that?

We're having a issue with some "ghost threads". Sometimes a XS process that should end quickly just stuck there doing, basically, nothing. But after hours it start to generate more and more sub-threads until hitting the limit and making a crashdump. It'd be good to have this "self-destruction" mode every few minutes or so.

Thx!

Rodrigo

[lbreddemann]

Hi Rodrigo,

the way to set the flag is to use the ALTER SYSTEM CANCEL SESSION command:

That's the API that is available.

There is no way to interfere with the evaluation of the flag from a XS or SQL Script level.

To address your problem, I'd recommend to find out why the problem is occurring, instead of accepting it and shooting your solution in the head every now and then.

- Lars

[Former Member]

Hello Lars,

I am running session cancellation tests on Rev70.

I have a SQL Statement which uses a CV and runs for 1 minutes using many cores.

I want to cancel this session after a couple seconds.

I uses alter system cancel/discceont session statements but the job is still running and reading your posts I was wondering if there is any thing else to do to not to wait till the process ends and rolls back.

Should I do something to make my SQL code aware ( runs on HANA Studio SQL Session) that a cancellation request has been made.

Thanks,

Ugur

[lbreddemann]

Hi Ugur,

the cancellation of commands does not happen between two SQL commands, where the user could check for the flag.

Instead it happens during the execution of plan operators at a much lower SAP HANA internal level.

So there in fact is nothing you can do to your SQL code to make it easier to be cancelled.

- Lars

[kyle_mcadam]

Thanks Lars.

You have a great way with words. And you have built yourself a reputation, with me anyway. Whenever I am looking for confirmation of an issue; I look for your comment on the thread.

[lbreddemann]

Hey Kyle,

thanks for that. Really nice of you to write that.

BTW: more words on SAP HANA  from me and

can be found in our new book SAP HANA Administration. of Richard Bremer, Lars Breddemann - by SAP PRESS

cheers, Lars

[kyle_mcadam]

I hope it's also published in English! I'm not like all of you bilingual folk there in Germany

[lbreddemann]

It sure is And thankfully the good folks at SAP Press double and triple checked the language for us...

[Former Member]

Hello All,

The original question regarding canceling thread has not been answered.

We need to cancel an XS code (without CONNECTION_ID) that might be stuck.

I can see the running thread and its THREAD_ID at the Performance query (Taken from the tab in administration)

But couldn’t find any canceling relevant syntax for this.

Please your opinion

BR,

Elad

[lbreddemann]

Hi Elad,

there is no way to safely cancel/stop a thread that doesn't belong to a user (external) session.

If this is a core requirement for your development project, you may want to talk to XS product management.

- Lars

[Former Member]

Thanks Lars.

[lbreddemann]

Hi Guys,
> 
> I have a statement running which is halting the system.
> Thread Method: ExecuteStatement
> Thread Detail: ALTER TABLE  "DM"."B_DIMM3" DISABLE DELTA LOG
> Duration [MS]: 2129231  <----
> This should take seconds.
> So, I want to Kill this process/thread.
> Rasmus

HI Rasmus,

as the "how to kill the session?" question was already answered, I've another one:

Why do you think that disabling the delta log of the table should be a quick action?

Maybe a huge delta merge operation is necessary to complete the switch off? And such an operation may take a very considerable amount of time and resources.

Just killing the session that is performing this action might not be the solution to the problem...

best regards,

Lars

[Vitaliy-R]

I reviewed SQL manualfor SPS3, but cannot find clause

 ALTER TABLE xxx.yyy DISABLE DELTA LOG 

there.

Is it one of non-documented treats? Am just curious.

Thanks,

-Vitaliy

[tomas-krojzl]

Hello,

Is it one of non-documented treats? Am just curious.

Might be also internal statement that is called as part of more complex operation...

Tomas

[Former Member]

Hi Lars,

The kill Question has not been answered as none of the statements actually kills the process.

As to why i think the ALTER TABLE statement should be a quick, i believe so because the table is empty.

Best Regards,

Rasmus

[Former Member]

@Tomas and Vitaly

The statement occurs when i am loading the table from Data Services.

I believe that the statement is cause due to the use of "bulk loading" i have however not confirmed this yet.

- Rasmus

[tomas-krojzl]

Hello,

The kill Question has not been answered as none of the statements actually kills the process.

Well.. you need to provide the feedback to our suggestions..

"ALTER SYSTEM CANCEL \[WORK IN\] SESSION session_id" gives a syntax error on WORK IN

Of course it does - \[ \] is there to highlight that this is just optional part...

What was the result of these commands:

SELECT CONNECTION_ID FROM "SYS"."M_CONNECTIONS" WHERE CREATOR_THREAD_ID=25529

ALTER SYSTEM CANCEL WORK IN SESSION '200012'

Also did you check log area?

Tomas

[Former Member]

Hi Rasmus Stokholm,

You can kill the process in BODS itself by using managment console or Designer

Regards,

Manoj.

[Former Member]

Hi all, Manoj,

I am running into the same issue.

Loading data through BODS on Windows Server using buld loader.

I can see that when the bulk loader option is enabled, the very first thing BODS does is that it executes the statement to disable the delta log.

BODS is stuck after this point. Even if you kill the BODS process, the lock on the table remains. No other process can write to the same table.

The only solution (I know of) is to restart Hana. It still gets stuck the next time I run BODS.

I also see that if I execute the same statement through studio, it never completes.

Wierd thing is I dont run into the same issue if the BODS job server is on the same box as Hana ( Suse Linux 11 )

Any solutions?

[Former Member]

Hello all,

I found out why BODS was unable to bulk load:

The table I was writing to also had a view defined on top of it.

If I remove that view, bulk loading succeeds.

I'm not sure why having a view on top of the table affects BODS bulk loading. I will update when I find out more.

Regards,

Sachin

[tomas-krojzl]

Hello,

there is following statement that might help...

ALTER SYSTEM CANCEL \[WORK IN\] SESSION session_id

Cancels the currently executed operation by the specified session, however, the session is not disconnected.

Tomas

[tomas-krojzl]

Hello,

to identify session_id you might need to query table "SYS"."M_CONNECTIONS"

Tomas

[Former Member]

Hi Tomas,

Thanks for the quick reply.

Though, I cant quite get it to work.

In "M_CONNECTIONS" i found the process there is no session_id but only connection_id and transaction_id

I went with the transaction id which was 104

The

"ALTER SYSTEM CANCEL [WORK IN] SESSION session_id" gives a syntax error on [WORK IN]

ALTER SYSTEM CANCEL  [WORK IN] SESSION 104

If i remove it gives syntax error on the session_id.

ALTER SYSTEM CANCEL SESSION 104

Can you see what I am doing wrong ?

Best Regards,

Rasmus

[Former Member]

Hi Rasmus

Use the CONNECTION_ID from view M_CONNECTIONS as a string and execute

ALTER SYSTEM CANCEL SESSION '<M_CONNECTIONS.CONNECTION_ID>';

cheers

--Juergen

[Former Member]

Thanks, that helped with the syntax

Now it executed, but it said 0 rows affected.

Statement 'ALTER SYSTEM CANCEL SESSION '200012'' successfully executed in 2 ms 867 µs - Rows Affected: 0

the process is still running.

Best Regards,

Rasmus

[tomas-krojzl]

Hello,

Now it executed, but it said 0 rows affected.

That is ok - it is always writing this... you can ignore - it is valid only when doing INSERT, UPDATE or DELETE...

Be sure you have correct session - try following:

SELECT CONNECTION_ID FROM "SYS"."M_CONNECTIONS" WHERE CREATOR_THREAD_ID=25529

ALTER SYSTEM CANCEL SESSION '200012'

Try also:

ALTER SYSTEM CANCEL WORK IN SESSION '200012'

Tomas

[tomas-krojzl]

Hello,

one more idea - please ensure that your LOG file system is NOT full...

it is probably not the cause because when this happened to me I was not even able to connect via HANA Studio but just to be sure...

Tomas

[Former Member]

Hello Tomas,

I ran an expensive insert statement and due to this my HANA studio hanged. So I had to kill my HANA Studio and reopen it.

Now that I have reopened i guess this will have a new session and new thread Id. In this case how do I find out original session Id or thread id - in which I ran the expensive insert statement ?

Thanks,

Chandan S Murthy

[Former Member]

I tried pressing "Cancel" in the progress bar in HDB Studio and the process just stayed in a state of waiting to cancel. The following did got work for me either:

ALTER SYSTEM CANCEL SESSION '400210'

However, when I did a disconnect, I at least saw it disappear from the progress bar/tab in HDB Studio and then was able to do other queries.

alter system disconnect session '400210'

It appears that under Performance --> Sessions, this particular session is still waiting to be canceled.

[Former Member]

Unfortunately, I face the same problem.

[Former Member]

They improved this in SPS08 but it's still a problem. Maybe SPS09 will help.

[lbreddemann]

Well, actually you do see an improvement here (that had been requested by me and others):

You do have the confirmation that the cancellation request had "gone through".

The fact that the thread/command/session doesn't get stopped immediately is not a problem here in the first place. As soon as the thread code will be able to check for the flag, it will initiate the self-destruction, but this might take some time.

If you think that it takes too long, you might take some runtime snapshots in quick succession (you can trigger snapshot-series as well) to find which SAP HANA kernel function your thread is currently in. With this information it's not too difficult to get back to development and request for more checks on the cancel flag.

As mentioned before, there is a trade-off between cancellation responsiveness and performance.

The more SAP HANA has to check for a cancellation the less efficient it can work on your queries.

- Lars

[Former Member]

The biggest one I notice is during bulk loads - if you kick off an activation when a bulk load is running, you often get a race condition. That got a lot better since SPS06, I guess due to better lock checking.

But there are definitely still times where a cancellation might take 4-6 hours.

[lbreddemann]

Correct, and that might be due to cleanup/rollback activities as well.

Knowing that the "kill switch" had been acknowledged at least allows the DBA to not shoot the instance blindfolded and then have to wait for the recovery...

And sure enough the Dev colleagues spent a big deal of effort on making this more responsive. I like it as well.

[Former Member]

It would be nice to have a mandatory check every 60 seconds. That would serve as a catch-all for when queries have gone into a tight loop that shouldn't have a check in it.

Would also be nice to have a check on object activation which fails after 60 seconds if the database lock isn't granted.

[lbreddemann]

I would add a SELECT FOR UPDATE WITH NOWAIT to the wish list.

That would allow applications to easily implement their own lock-wait scheme.

Seeing that Christmas is coming up, maybe it's time for a new wish list

[Former Member]

This message was moderated.