Register Login

SAP HANA Database Suffers from Lock Waits

Updated May 30, 2018

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?