Tables Vs HANA DB Locks Vs ABAP WORKPROCESS

Relationship between Tables,locks and workprocess

Below blog consists of two section:

a.To find out if there are any table lock on any table in HANA DB

b.Relationship between SAP work process and client ip which is holding locks in DB level  for a specific table .

SECTION 1:TABLE Vs LOCKS

To find out if a table has any locks on itself in HANA DB level , navigate to the below path in Studio:

Type the table name here and click on execute. This will help with the table details. Also you can find from which server this lock is coming in case of a distributed environment and also the type of lock involved.

Below is the screen shot after clicking on execute.

The same can also be queried using M_TABLE_LOCKS

SECTION 2:Relationship between SAP workproces and the table lock it might be holding in HANA DB level

Using below simple SQL, we can instantly find out which SAP work process and client ip which is holding locks in DB level  for a specific table .

Steps:

1.Access list of table locks from system information .

Display the lock and filter to find out details about 1 table .Here we can choose first entry with LOCK_OWNER_TRANSACTION_ID as 1859

2.Now using the simple query

select * from M_CONNECTIONS where TRANSACTION_ID in (SELECT distinct LOCK_OWNER_TRANSACTION_ID FROM SYS.M_TABLE_LOCKS where TABLE_NAME=’xxxxxxx06515000′) and CONNECTION_STATUS = ‘RUNNING’;

We can also use table M_TRANSACTIONS to get the CONNECTION ID involved.

In SAP level check for the CLIENT_PID

3.If we want to find out list of all the threads that this connection holds is by using the below SQL.

select * from M_SERVICE_THREADS where CONNECTION_ID=’223984′ and APPLICATION_USER_NAME=’ALEREMOTE’;

NOTE: If any table was not successful enough to attain a table lock, the involved connection’s thread will be in Semaphore Wait state and it continues to be like that until the time specified in lock time out parameter, post which the executions fails with the same message .

ERROR:***LOG BY0=>transaction rolled back by lock wait timeout

ie,The default value of <transaction_lock_wait_timeout> is specified in indexserver.ini, in the transaction section. The value must specify the number of milliseconds and be between 0 and 2147483647. If a lock is not acquired before the timeout period expires, then the executing statement returns the error code 131. The transaction is rolled back by <transaction_lock_wait_timeout>.

Thanks for reading!
Follow for more such posts!https://people.sap.com/rajarajeswari_kaliyaperumal
Like and leave a comment or suggestion if any!