How to find out objects in HANA blocked transactions

[former_member585959]

Hello Experts

We have an ECC system (SoH) with HANA DB Rev122.12 and we frequently get blocked transactions on table NRIV. We raised the issue to SAP and received below recommendation

"High lock time for Number Range Table (NRIV), objects EINKBELEG (Purchasing documents) and RV_BELEG (Billing Documents - External Assignment). It should be evaluated the setup of buffering for theses objects."

I tried to make analysis from my side and found the corresponding statement hash from "HANA_Locks_Transactional_LockWaits_Hierarchy_1.00.100+" script and I'm attaching the output as txt file hana-locks-scn.txt. Also found the SQL statement as

"SELECT TOP 1 * FROM "NRIV" WHERE "CLIENT" = ? AND "OBJECT" = ? AND "SUBOBJECT" = ? AND "NRRANGENR" = ? AND "TOYEAR" = ? FOR UPDATE". 

Since its using bind variable how can I find the OBJECT causing the lock situation as pointed out by SAP. I can see the bind values using HANA_SQL_ExpensiveStatements_BindValues_CommaList_1.00.60+. Is it the right method? Does all blocked transactions comes under Expensive Statements?

[jgleichmann · Accepted solution]

Hi,

you already have the answer in your output of statement 'HANA_Locks_Transactional_LockWaits_Hierarchy_1.00.100+'. Just have a look at column 'WAIT_S'. Every sample (row with timestamp) with no value in this column - the first one. This is the blocker. So as you can see there are different once: CONN_ID 331893 and 355323. Check out SAP TX STAD for the specified time frame for user SAP-USER-SD. But keep in mind that this statistics will be overwritten every 48h by default.

Another way is the code source of the blockers: SAPLV60B:8109 and SAPLSNR3:1995

You can use HANA_SQL_StatementsWithSameABAPCodingLocation to find the ABAP Source by adjusting the modification section.

To optimize NRIV access you can use buffering of objects RV_BELEG or RF_BELEG but this depends on your country laws. Check out the SAP notes for buffering. Optimize the coding and the commit cycles after acquiring a number from NRIV.

Regards,

Jens

[eason_chen]

Hi，

You may use following SQL attached in note 1969700 to find out the bind values.

HANA_SQL_StatementHash_BindValues_CommaList

statement hash and time stemp need to be specified.

Best Regards

[former_member585959]

Thanks Eason. In below case, i'm not getting statement hash itself. How to go further in this case.