How To Fix DB6117 - Number of lock escalations per hour

SAP Error Message - Details

• Message type: E = Error

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

• Message number: 117

• Message text: Number of lock escalations per hour

What causes this issue?

If a transaction accesses the database to make changes, the system
locks exactly those records in the tables that the application is
accessing for the duration of the transaction. The system stores this
information in a separate area of the main memory, and other
transactions can no longer access those records to change them. If this
memory is used up, the entire table is locked from that point on. This
is a lock escalation.

System Response

The table-wide lock obstructs 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 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 DB6117 - Number of lock escalations per hour ?

  The SAP error message DB6117 indicates that the number of lock escalations per hour has exceeded a predefined threshold. Lock escalation occurs when the database system converts row-level locks into table-level locks to reduce the overhead of managing many individual locks. This can lead to performance issues and contention among transactions.
  Cause:
  
  High Concurrency: A large number of transactions trying to access the same data simultaneously can lead to many row-level locks being created.
  Long-Running Transactions: Transactions that hold locks for an extended period can increase the likelihood of lock escalations.
  Insufficient Lock Management: The database configuration may not be optimized for the workload, leading to excessive lock escalations.
  Application Design: Poorly designed applications that frequently access the same rows or tables can exacerbate locking issues.
  
  Solution:
  
  Analyze Locking Behavior: Use transaction monitoring tools to analyze which transactions are causing the most locks and why.
  Optimize Transactions: Review and optimize long-running transactions to ensure they complete as quickly as possible. This may involve breaking them into smaller transactions or optimizing the queries involved.
  Adjust Database Configuration: Depending on the database being used (e.g., Oracle, SQL Server, etc.), you may need to adjust parameters related to locking and escalation thresholds.
  Implement Locking Strategies: Consider using optimistic locking or other strategies that reduce the likelihood of lock contention.
  Review Application Logic: Ensure that the application logic is designed to minimize lock contention, such as accessing data in a consistent order or reducing the frequency of updates to the same rows.
  Increase Resources: If the workload has increased, consider scaling up the database resources (CPU, memory, etc.) to handle the load more effectively.
  
  Related Information:
  
  Monitoring Tools: Use SAP transaction codes like SM12 (to view locks) and SM21 (to view system logs) to monitor locking behavior.
  Database-Specific Documentation: Refer to the documentation for the specific database you are using with SAP (e.g., Oracle, HANA, SQL Server) for best practices on managing locks and escalations.
  SAP Notes: Check SAP Notes for any known issues or patches related to locking and performance that may apply to your version of SAP.
  
  By addressing the underlying causes of lock escalations and optimizing both the database and application design, you can reduce the frequency of this error and improve overall system performance.