How To Fix DB6119 - Number of exclusive lock escalations per hour

SAP Error Message - Details

• Message type: E = Error

• Message class: DB6 - DB6: Alert Monitoring and Alert Configuration

• Message number: 119

• Message text: Number of exclusive lock escalations per hour

What causes this issue?

If an application accesses database data to make changes, the system
locks exactly those records in the table that are required for the
duration of the transaction. The system stores this information in a
separate area of main memory. In the case of an exclusive lock, other
transactions can no longer access these records even to read them. If
this memory is exhausted, the entire table is locked for reading from
that point on. This is an exclusive lock escalation.

System Response

The table-wide exclusive lock is causing significant obstruction to
other running transactions and should generally be avoided.

How to fix this error?

Set the size of the memory area available to an application for storing
locks using the database parameters LOCKLIST and MAXLOCK.

Error message extract from SAP system. Copyright SAP SE.

Smart SAP Assistant

• What is the cause and solution for SAP error message DB6119 - Number of exclusive lock escalations per hour ?

  The SAP error message DB6119 indicates that the number of exclusive lock escalations per hour has exceeded a predefined threshold. This typically occurs in database systems when there are too many exclusive locks being requested, which can lead to performance issues and contention for resources.
  Cause:
  
  High Lock Contention: This can happen when multiple transactions are trying to access the same data simultaneously, leading to a high number of exclusive locks.
  Long-Running Transactions: If transactions are held open for a long time, they can accumulate locks, causing escalations.
  Inefficient SQL Queries: Poorly optimized queries can lead to excessive locking behavior.
  Database Configuration: The database settings may not be optimized for the workload, leading to lock escalations.
  Application Design: The way the application is designed can lead to excessive locking, especially if it does not handle transactions efficiently.
  
  Solution:
  
  Analyze Locking Behavior: Use database monitoring tools to analyze which transactions are causing the locks and why. Look for long-running transactions or queries that are holding locks for too long.
  Optimize SQL Queries: Review and optimize SQL queries to reduce the locking footprint. This may involve indexing, rewriting queries, or breaking them into smaller transactions.
  Adjust Transaction Management: Ensure that transactions are kept as short as possible. Commit or rollback transactions promptly to release locks.
  Database Configuration Tuning: Review and adjust database configuration settings related to locking and concurrency. This may include parameters that control lock escalation thresholds.
  Increase Resources: If the workload has increased, consider scaling up the database resources (CPU, memory, etc.) to handle the load better.
  Review Application Logic: If the application is causing excessive locking, consider redesigning parts of the application to minimize lock contention.
  
  Related Information:
  
  Monitoring Tools: Use SAP transaction codes like SM12 (for lock entries) and SM21 (for system logs) to monitor locks and identify problematic areas.
  Database-Specific Tools: Depending on the database (e.g., Oracle, SQL Server, HANA), use specific monitoring tools to analyze locking behavior.
  SAP Notes: Check SAP Notes for any known issues or patches related to locking and performance.
  Documentation: Review SAP documentation and best practices for managing database locks and optimizing performance.
  
  By addressing the underlying causes of lock escalations, you can reduce the frequency of the DB6119 error and improve overall system performance.