LCK_M_UIX_ABORT_BLOCKERS

Description

This wait occurs when a request is waiting to acquire an Update Intent Exclusive lock with the low priority options added in SQL Server 2014. An update lock are not just for UPDATE operations. They are used when SQL needs to read then modify a row/page/table, before modification it will place an Update on the data then when ready upgrade this to an Exclusive Lock. This wait typically occurs when modify requests are blocked by other write transactions (implicit of explicit) been kept open for extended periods of time. The low priority operations must be set to abort blockers for this wait type to accumulate

Resolved by

DBAs and Developers

Suggested solutions

  1. Keep transaction durations short.
  2. Ensure transactions isolation levels are appropriate (avoid SERIALIZEABLE and REPEATABLE READ if possible)
  3. Investigate enabling the READ_COMMITTED_SNAPSHOT database setting
  4. Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked
  5. Indexing, ensure long running queries and transactions are optimised.
  6. Ensure locks being escalated correctly: row, page or table (if table is partitioned considered enabling partition escalation)
  7. Additional research

    Exploring Low Priority Lock Wait Options
    Common Language Runtime from the DBA Point of View
    Understanding Isolation Levels
    Identify the Cause of SQL Server Blocking
    Lock Escalations

    See more

    Consider using ApexSQL Monitor, a Windows and SQL Server monitoring tool able to track SQL Server wait types, including the individual query wait statistics

    ⇐ Back to index