Sample Header Ad - 728x90

The finer points of writers locking each other out under the snapshot isolation levels

3 votes
1 answer
614 views
The read committed snapshot and snapshot isolation levels in SQL Server do away with most locking except one: [A writer still locks out other writers](https://stackoverflow.com/questions/1044164/using-isolationlevel-snapshot-but-db-is-still-locking) . The documentation tiptoes around saying as much and subsequently doesn't document anything else which would be very interesting to know: Is it really ever just a modified row that gets exclusively locked? Or can it also be unrelated rows (eg. adjacent in an index) or pages? I did have a look at the locks in sys.dm_tran_locks and I only ever saw exclusive locks on modified rows during an uncommitted transaction - pages where merely locked as IX. I also tested whether two transactions could modify two different rows simultaneously during two uncommitted transactions in a really small table that likely fits into one page and that worked as well. If indeed only modified rows are exclusively locked, than this would give an application with exclusive access to the database the guarantee of lock-free writing if it makes sure that no two connections write simultaneously to the same row. This would be possible in the scenario I have in mind - but there's hardly a way to do something like that if page locks come into play as it's unpredictable what rows exactly would be affected.
Asked by John (775 rep)
Oct 10, 2022, 08:11 PM
Last activity: Oct 12, 2022, 03:08 PM