Unlocking a table in HANA

[Former Member]

How to unlock a table in HANA . I searched throughthe help guides(SQL Ref), I could only find lock table syntax . Couldn't find the right syntax to unlock a table locked by a end user.

[ruediger_karl · Accepted solution]

Hi,

you can to cancel the transaction that owns the table lock, instead of restarting HANA. Just check SYS.M_TABLE_LOCKS to for the transaction id. In HANA Admin Console (Performance) you can cancel transactions. The transaction is rolled-back and the locks are released.

Regards, Ruediger

[nithinu · Accepted solution]

Hi Ruediger,

As you said, I have checked the transaction id in SYS.M_TABLE_LOCKS table. But couldn't find the same in HANA Admin Console (Performance) . And the table is still in Lock mode(INTENTIONAL EXCLUSIVE). Can you please suggest what may be the reason..

Thanks,

Nithin

[lbreddemann · Accepted solution]

Well first of all you shouldn't hook on to years old threads!

And after that, you might want to check the SAP HANA documentation and/or my book () to learn about how to cancel open transactions and why it is that this might not always be possible.

[Former Member]

Hi Murali,

I had the same problem in Rev 26, unfortunately the only way I could successfully unlock the table was to stop-start the HANA server. Although I must mention it worked once when I restarted the HANA studio alone, but only once. You are right, there is nothing in the SQL reference guide that would unlock a table.

This was a bug, I am on Rev 29 and I don't get the table unlock error anymore - even when I cancel a data upload midway and try to truncate/drop the table straight after.

So I believe it was solved with SPS04 with either Rev 28 or Rev 29.

Thanks,

Anooj

[Former Member]

Thanks Anooj.

I ended up restarting the HANA Instance. It worked . I wish there a a simple way like unlock table ....

Murali

[Former Member]

Yeah. If you are on Rev 26 or prior I suggest you upgrade to Rev 29 if that isn't much of a trouble.

[Former Member]

Dear Murali

I had the same issue and I solve it like you.

Thank you.

[rama_shankar3]

Murali:

Can you expand on what caused the lock operation on the table?

  Did you use the "With tablelock" option to load data? If so, try issuing the merge delta command (i.e: MERGE DELTA OF "schema"."tablename) , using the same id that ran the import job.

Hope this helps.

Regards,

Rama