2079396 - How to handle HANA Alert 49: 'long-running blocking transaction'

Symptom

This Knowledge Base Article (KBA) is part of a series of HANA Operation Recommendations. Its focus is to prvide best practise instruction on handling of HANA Alerts related to blocked transactions.

A long-lasting lock situation is presently observed on the SAP HANA Database:

1. Alert “Long-running blocking situations” (Alert ID 49) – “Transaction <X> has been blocked by transaction <Y> for more than <N> minutes“ has been raised by the SAP HANA Database.
2. End-Users report that an application behaves slow, unresponsive. It may seem to hang or even results in a dump (time-out).

Environment

Instructions in this Knowledge Base Article have been created on a SAP HANA Database with Revision 71 (Support Package Stack SPS 7) and may differ in other revisions.

Cause

Blocked transactions are write transactions that are unable to be further processed because they need to acquire transactional locks (record or table locks) which are currently held by another write transaction.
Usually the root cause is a database lock held by a transaction of another application (dialog or batch). This is the focus of this Knowledge Base Article.
In some cases the lock is held due to

1. Performance issues on storage or network level.
   
2. Contention of internal locks that are used by the database engine or operation system for controlling access resources via concurrent threads.
   

These cases are described in KBA 2039424 - How to handle Alert 59 Percentage of transactions blocked.

Resolution

1. Procedure

1. Check the Alert Monitor for SAP HANA to get an indication if the problem relates to one or few transactions or if there are general system performance issues.
2. Check the Blocked Transaction Monitor and get details on blocked and blocking transactions.
3. Get information on the lockholder and blocked transactions on application level.
4. Contact the user who is executing the lockholder transaction to get details of his activity.
5. If re-occurrence is likely from an end-user perspective, then contact the application owner and (if necessary) the developer to get preventive measures identified.
6. Cancel blocking transaction (if required).
   

Note: In some cases the blocking situation is resolved automatically by the system, e.g.:

• The application terminates a session with a time-out. In this case the indexservertrace shows entries like:
  session control command is performed by 247913, user=SAPB2J, application user=, application source=, query=ALTER SYSTEM CANCEL SESSION '231733'
• The HANA Database terminates a session after a predetermined length of time has elapsed. This is done through mvcc_anti_ager mechanism based on the setting of the indexserver parameter lock_wait_timeout (default value = 1800000 ms).

Both cases are reflected with entries in the indexserver trace file that can be searched based on e.g. timestamp information, user name, connection id or keywords like mvcc_anti_ager.

2. PATH 

• SAP HANA Studio – SAP HANA Administration Console – Select <SID of your SAP HANA database> – Current Status - Alerts.
• SAP HANA Studio – SAP HANA Administration Console– System Information – Others – Blocked Transactions.
• Managed System – Process Monitor of the application, e.g. SM50 for an ABAP Application.
• SAP HANA Studio – SAP HANA Administration Console – Performance – Sessions.
• SAP HANA Studio – SAP HANA Administration Console – Performance – Threads.

3. HOW TO:

For a detailed How-to description please refer to the attached document.

See Also

Further information is available in the following information sources:

Source	Type
2039424 - How to handle HANA Alert 59: Percentage of transactions blocked	SAP Knowledge Base Article
1858357 - HANA DB: Analysis of wait situations	SAP Note
1999998 - FAQ: SAP HANA Lock Analysis	SAP Knowledge Base Article
1969700 - SQL Statement collection for SAP HANA	SAP Knowledge Base Article
SAP HANA Troubleshooting Guide	SAP help document
SAP HANA Administration Guide	SAP help document

Header Data

Released On	14.10.2014 07:45:05
Release Status	Released to Customer
Component	SV-BO-DB-HAN Backoffice Service Delivery - DB/HANA Performance
Other Components	HAN-DB SAP HANA Database
Priority	Normal
Category	How To
Database	HDB 1.0