How to check if SAP HANA database suffers from lock waits?
On a very elementary level you can identify lock waits based on the thread states.
Current threads can be displayed via:
- Transaction DBACOCKPIT: Performance --> Threads
- SAP HANA Studio: Performance --> Threads
- SQL: "HANA_Threads_CurrentThreads"
Historic thread activities can be determined via:
- SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" and "HANA_Threads_ThreadSamples_AggregationPerTimeSlice"
Read Here SAP HANA Database Architecture Overview
The following thread states indicate lock wait situations:
- Barrier Wait
- ConditionalVariable Wait
- ExclusiveLock Enter
- Mutex Wait
- Semaphore Wait
- SharedLock Enter
- Sleeping
Read Here What happens in case of deadlocks in SAP HANA?
Not every occurrence of these states is critical. It happens frequently that a thread submits a request to another thread and waits for a related semaphore until the called thread has returned the result.
The comprehensive check command SQL: "HANA_Configuration_MiniChecks" contains also lock related checks. An 'X' in column C indicates potentially critical situations. Example output:
The following SQL errors also indicate lock related problems:
- SQL error 131: transaction rolled back by lock wait timeout
- SQL error 133: transaction rolled back by detected deadlock
The following SAP HANA alerts are another indication for locking issues:
- Alert 49: Long-running blocking situations
- Alert 59: Percentage of transactions blocked
Read Here How to Characterized Lock Situations of HANA Database System?