Description
This wait occurs while a request is waiting to acquire an update lock. An update lock is not just for UPDATE operations. They are used when SQL Server needs to read and then modify a row/page/table. Before modifications it will place an update lock on the data. Once the system is ready these locks will upgraded to exclusive locks. This wait typically occurs while modify requests are blocked by other write transactions (implicit or explicit)
Resolved by
DBAs and Developers
Suggested solutions
- Keep transaction durations short
- Ensure transactions isolation levels are appropriate (avoid SERIALIZEABLE and REPEATABLE READ if possible)
- Investigate enabling the READ_COMMITTED_SNAPSHOT database setting
- Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked
- Indexing, ensure that long running queries and transactions are optimized
- Ensure that row, page and table locks are being escalated correctly. If the table is partitioned consider enabling partition escalation
Additional research
Reducing SQL Server Locks
Understanding Isolation Levels
Identify the Cause of SQL Server Blocking
Lock Escalations