The lock that killed my migration
This title could be clearer and more informative.Try out Clickbait Shieldfor free (5 uses left this month).
A production migration pipeline was failing with lock wait timeouts because a streaming SELECT on SQL Server was holding shared locks open for the entire fetch duration, blocking concurrent UPDATE statements on the same table. Using sys.dm_exec_requests and sys.dm_tran_locks, the investigation identified one blocker (a SELECT) starving two writer sessions waiting on LCK_M_IX locks. The fix was setting READ UNCOMMITTED isolation on the fetch connection so no shared locks are acquired during the scan, allowing UPDATE workers to proceed immediately. The post explains the SQL Server locking mechanics behind the conflict and discusses trade-offs, including when to prefer SNAPSHOT or RCSI isolation instead.
Table of contents
Find the blockerThe output told the storyUnderstanding the mechanismRoot causeThe fix: READ UNCOMMITTED on the fetch connectionA word on the trade-offsConclusionSort: