How to handle HANA alert 49 – Long-running

blocked transactions
A Knowledge Article of the SAP HANA Operations Recommendations Series (#OpsRec-Hana)

This Knowledge Base Article (KBA) is part of a series of HANA Operation Recommendations. Its
Focus is on providing best practise instruction on handling of HANA Alerts related to Blocked
Transactions.

SYMPTOM
A long-lasting lock situation is presently observed on the SAP HANA Database:
1. An 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
SAP HANA Database (any kind of edition).

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 the Knowlede Base Article How to handle Alert 59 Percentage of transactions
blocked.
RESOLUTION

1. Analyze Problem

ACTIVITIES

 Check 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.
 Check the Blocked transaction Monitor and get details on blocked and blocking transactions
 Get information on the lockholder and blocked transactions from application level
 Contact the user who is executing the lockholder transaction to get details of his activity
 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.

PATH

 /nDBACOCKPIT – Select <SID of your SAP HANA database> – Current Status - Alerts
 /nDBACOCKPIT – Select <SID of your SAP HANA database> – 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.

HOW TO:

1. Access the Alert Tab in SAP HANA Studio – SAP HANA Administration Console:

1.1. Look at the Check Information to get an overall idea of the system’s status. Click twice on
column Max Priority to sort by Max Priority (descending) and capture alerts with high priority

 The following alerts may go along with the Blocked Transaction Alert without necessarily
having a different root cause:

49 – Long-running blocking situations

39 – Long-running statements
47 – Long-running serializable transactions
48 – Long-running uncommitted write transactions

 The following alerts may go along with blocked transaction alert and they may indicate a
more severe root cause that should be investigated in parallel and with high priority, e.g.
issues with IO or network performance or issues with internal locks held by the HANA
Dabatase Engine or the Operating System.

59 – Percentage of transactions blocked

54 – Savepoint duration

Note down the identified alerts. Confirm that “Last run” has been recent. If you find many
performance related alerts then contact SAP Support for a thorough analysis of the HANA
Database System. Use Component BC-DB-HDB or SV-BO-DB-HAN

Copyright/Trademark
Fig. 1: Alert Tab in HANA Studio with Check inform ation and Current Alerts sorted by Priority

1.2. Mouse over the High-priority alerts and note down situative details of the current alert such as
e.g. specific Transaction ID and Client PID. Such Information will help you to identify if different
alerts actually relate to the same user action or issue and thus shall be handled together.

Fig. 2: Alert Tab in HANA Studio with Current Alerts sorted by Pri orit y

For Alert 49 “Long-running blocked transactions” note down from the Alert Details the
Transaction and TID of the blocked and blocking transaction

Copyright/Trademark
Fig. 3: Alert Details in HANA Studio for an Alert Long -running bl ocked transaction

For comparison the Alert Details are shown below for the Alert „Long-running Statements“ (Alert
ID 39). In this case it can be seen from the transaction number and transaction ID that the
occurrence of alert 48 is due to the update being blocked:

Fig. 4: Alert Tab in HANA Studio with Check inform ation and Current Alerts sorted by Priority

Fig. 5: Alert Details in HANA Studio for an Alert Long -running statem ents

Copyright/Trademark
2 Go to the Blocked Transaction Monitor (SAP HANA Studio - under the Performance Tab).

The ‘Blocked Transactions’ monitor lists those transactions that are currently blocked. The
ordering is done via a blocking/blocked relation. That means transactions that are blocked are
highlighted and directly listed under the causing blocking transactions.

 If there are multiple blocked transactions than sort by LOCK_OWNER_PID.

 Investigate if there are blocking transactions that are blocked themselves and analyze these
first. Create a screenshot and note down dependencies.

 If multiple transactions are blocked by the same lock these should be handled with priority.

 If there is a large number of blocking transactions then this may indicate a more severe issue
that shall be processed along the recommendations for alert “Percentage of blocked
transactions”.

Note:
 The Connection ID helps to identify the transaction in other tabs (e.g. Thread monitor,
Session Monitor).
 The Process ID (‘Client PID’) helps to identify the corresponding Process within the calling
application; e.g. in an ABAP Application the Client PID corresponds to the Process ID of the
Work Process as displayed in Transaction SM50 (Process Overview) or SM66 (Global
Workprocess Overview).

Search for the record that has been reported in the blocked transaction alert. Right-click and
choose Details and capture the information in a screenshot for later reference:

Fig. 6: Blocked Transactions Monitor in HANA Studio with one bl ocking situation

The table is very large. The following columns are in particular interesting. Scroll to the right to
display details:

 Connection ID:
Connection ID in the HANA Database. This information is required if you want to kill the
application on DB level
 Transaction ID and Lock Owner Transaction ID:
Shows the Transaction ID of the user activity. For the transaction holding a lock the Lock
Owner Transaction ID is empty, for the blocked transaction it includes the Tranaction ID of
the lock-holder.
 Blocked timestamp:
Timestamp when the blocking situation occurred-
 Thread Detail:
Information on the currently performed SQL-Statement in the Thread.
 Lock Type and Lock Mode
Information about the lock itself.
 Waiting_SCHEMA_NAME, WAITING_TABLE_NAME, Lock_type and Lock_mode:
Details of the object that is currently locked.
 Application
The application that has issued the statement

Copyright/Trademark
 Application Source:
Name of the application program that issued the statement
 Application User
Name of the End-user in the application running on SAP HANA
 User
Name of the database user that is used by the application. Generic user when application
has its own user management. End-user when query is directly performed on native HANA.
 Client host, Client PID:
Server name and Process ID in the application that has issued the database query (e.g. in
ABAP systems this corresponds to the Workprocess number)

3 Optional: Analyze lockholder and blocked transaction on Application Level

Perform this step in case the Blockde transaction Monitor does not provide you the full details on
the enduser name and the performed transaction.

If the application is an ABAP application, then go to Workprocess Overview SM50. For other
applications go to the corresponding process monitor.

Note: that in case of an ABAP System running on multiple servers you need to go to the server
list (SM51) first and double click on the Server (since Workprocess Overview is Server specific)

In this example
 the lockholder is Program ZBADGUY, executed by user TESTER1 and running in
Workprocess No 12 (identified by Process ID 43.393)
 the blocked transaction is in program ZPOORGUY, executed by user POORGUY and
running in Workprocess No 11 (identified by Process ID 13.917)

Fig. 7: Server List of an ABAP system where the blocking situation occured

Fig. 8: Process Overvie w of an ABAP system with runni ng lockhold er and blocke d process

Copyright/Trademark
4 Contact Application User

Contact the user and ask him:

 Details of the performed activity (e.g. objective, transaction, input data)
 Whether the user can close the application.
 Discuss if this is a one-time activity or if similar application usage is likely required in the
future

Note: If you were unable to proceed the previous step e.g. because you do not have access to
the application, the Session Monitor in HANA Studio shows some of the information, too:
 The user name is displayed in column “Database User” or, in case the application has its
own user management (e.g. SAP BW), in the “Application User” column.
 The Sessions Monitor also shows Application and Application Source.

To access the Session Monitor go to SAP HANA Studio – SAP HANA Administration Console –
Performance – Sessions.

5 Contact the owner of the application

If the user action is likely to be repeated in the future then follow-up with the owner of the
application or even the application developer to have them check how such situations can be
avoided.

For SAP delivered applications, iIf counter measures are not clear, then open a ticket to SAP on
Service market place. In an ABAP System you find the application component for the ticket in
the attributes of the package that the transaction or program are assigned.

Copyright/Trademark
2. Apply Solution

ACTIVITIES

 Ask End-user of the blocking transaction to cancel the application

 Cancel the process of the blocking transaction from application level (e.g. SM50)
 Cancel the database session of the blocking transaction from SAP HANA Studio – Session
Monitor

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
enties 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.

PATH

 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.

HOW TO:

1. Cancel the process of the blocking transaction from application level (e.g. SM50)

To cancel the process of the blocking transaction from application level (e.g. SM50) mark the
work process and choose Administration – Process – Cancel – Without Core from the menu:

Fig. 3: Alert Details in HANA Studio for an Alert Lon -running blocked transaction

Copyright/Trademark
2. Cancel the process of the blocking transaction from application level (e.g. SM50)

To cancel the right session on database level with the SAP HANA Studio you can navigate from
blocked transaction monitor directly to the right session in the Session monitor. If there is no
session with that connection ID then choose Navigate to Threads with Connection ID …

In the Session Monitor or in the Thread Monitor you can cancel he session by doing a right-click
and choosing Cancel Operations for the Thread that is already marked (identifier is the
Connection ID)

SEE ALSO

 SAP HANA Administration Guide – Chapter 2.5.8.3 Blocked Transactions Monitoring

http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf
 SAP Note 1858357 - HANA DB: Analysis of wait situations

KEYWORDS
SAP HANA – Blocked Transactions – Alert – Operation Recommendation – #OpsRec-HANA

Copyright/Trademark