SAP HANA – Troubleshooting Blocked Transaction Issues that Occurred in the Past

Finding the root cause of blocked transaction situations that you have resolved is more difficult than troubleshooting issues that are currently happening. Tools such as the Load Monitor, system views and the SQL Plan cache are available to help you.
First use the Load Monitor to isolate the exact time frame where the issue happened. Using that information, investigate what happened at this specific time frame. You should check the following monitoring and StatisticServer views:

  1. _SYS_STATISTICS.HOST_BLOCKED_TRANSACTIONS: Analyze the columns “WAITING_SCHEMA_NAME”, “WAITING_TABLE_NAME” and “WAITING_RECORD_ID” to identify the database objects that lead to blocked transactions
  2. SYS.M_DELTA_MERGE_STATISTICS: The column “START_TIME” and “EXECUTION_TIME” provide you with the information if there was a Delta Table Merge running A longer history can be found in the StatisticServer table _SYS_STATISTICS.HOST_DELTA_MERGE_STATISTICS●
  3. SYS.SAVEPOINTS: Check if a savepoint was written during the time period. A longer history can be found in _SYS_STATISTICS.HOST_SAVEPOINTSIn addition the SAP HANA studio SQL Plan Cache monitor may be able to provide information about the statements that were involved in the situation:

    Only check entries that have “TOTAL_LOCK_WAIT_COUNT” > 0. For those entries, compare the column “MAX_CURSOR_DURATION” against “AVG_CURSOR_DURATION”. If there is a significant difference, there was at least one situation where the transactions took much longer than average. This can be an indication that it was involved in the situation.

Leave a Reply