SAP HANA alert :Total Open Transactions Check / Alert ID 48: "Long-running uncommitted write transaction either False or Duration of the Uncommitted Transaction is Inaccurate #HANA Database

When you receive an alert about "Total Open Transactions" or "Long-running uncommitted Transaction' write tra in SAP HANA, it typically indicates too many open transactions, impacting performance and stability. Here’s a step-by-step guide to troubleshoot and resolve this issue:

1. Identify Open Transactions

First, identify which transactions are open:

1. Check System View:
• As the System is up and running fine, we can use HANA Administration tools to find active transactions by using the SQL command given below:

SELECT * FROM "SYS"."M_TRANSACTIONS" WHERE TRANSACTION_STATUS ='ACTIVE';

     2. Transaction Manager:

•  The next step to identify open transactions is to use SAP HANA Studio or HANA Cockpit and navigate to the Transaction Manager to view and analyze open transactions.

2. Analyse the Transactions

Identify the details of the open transactions to understand their impact:

1. Transaction Duration:
• Check how long the transactions have been open. Long-running transactions can be problematic.

SELECT TRANSACTION_ID, CONNECTION_ID, START_TIME, DURATION, USER_NAME, APPLICATION FROM M_TRANSACTIONS WHERE STATE='ACTIVE';

     2.Transaction Type:

• Determine if the transactions are read or write operations. Write operations can cause locks and affect performance.

3. Investigate the Root Cause

Understanding why the transactions are not being closed is crucial:

1. Application Issues:
• Check if there are any application issues causing transactions to remain open. Review the application logic for proper transaction management.
2. Long-Running Queries:
• Identify any long-running queries or processes that might be holding transactions open. Optimize these queries or processes if necessary.
3. Lock Contention:
• Investigate if there is any lock contention that might be causing transactions to stay open. Use the following SQL to check lock waits:

SELECT * FROM M_BLOCKED_TRANSACTIONS.

4. Take Corrective Actions

Based on your findings, take appropriate actions:

1. Terminate Unnecessary Transactions:
• If there are unnecessary transactions, terminate them. This can be done via HANA Studio or SQL:

sql

Copy code

ALTER SYSTEM CANCEL TRANSACTION <TRANSACTION_ID>;

2. Application Fixes:
• Ensure that applications correctly commit or rollback transactions. Review and fix any application code if needed.
3. Query Optimization:
• Optimize any long-running queries or processes. Ensure proper indexing and efficient query design.
4. Configuration Tuning:
• Tune HANA system parameters if necessary. For example, adjust the max_open_transactions parameter if the current limit is too low for your workload.