Database Lock Waits and Deadlocks

 

Causes, Impacts

• Long waits and deadlocks typically arise from bulk or batch operations with mass updates or deletes, while other jobs or online activities are also working on the same set of table data.
• DB locking and wait mechanisms can vary by database, but the essential concept is to synchronize access to data and ensure consistency. Application design has to consider both performance and data integrity in a parallel processing environment.
• BASIS (DBA is implied) should care about two main types of situations:
  • Performance impacts of wait or blocked situations, bottlenecks and contention
  • Deadlocks: transactions can get rolled back when there is lock wait timeout, deadlock detection, or if the application is designed to not wait for busy resources

 

How is it monitored?

• ABAP: TCODE DB01 “Blocked Transactions” is under the Diagnostics → Locks section of DBACOCKPIT, or Lock Monitor
• HANA Studio → Performance → Blocked Transactions
• Database MiniChecks → Locks → (Internal or Transactional) scripts to check for various lock situations
• SAP provides alerts of lock situations, which for transactional locks often needs to be correlated back to the application enqueue objects. HANA has some historical SNAPSHOT of wait situations, but this is not supportive of historical top down analysis unless someone is actively monitoring current DB locks and SAP enqueue locks in real time.