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

Question

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.

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.

Answer 1

General rules about locks and lock escalation:

Row level locks can get escalated to Table level (or Partition level) locks. So, it is possible for unrelated rows to be locked, under certain circumstances.

Those circumstances are documented here. Also note that lock escalation can be disabled with the LOCK_ESCALATION table option.

Also some query plans use page locks, and some query plans will need to read a row locked by another transaction to determine if it should be modified. Eg update foo where someUnindexedColumn = 'someval' will need to read every row, and so will become blocked by any other writer.

Please note this (which we'll need at the last paragraph):

(i) Important

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

Rules about SNAPSHOT isolation level:

The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.

In short, in this isolation level:

• row-versioning is used (instead of locks) to protect modifications by other transactions.
• Readers do not block readers.
• Readers do not block writers.
• Writers do not block readers.

However, the documentation does NOT mention that "writers do not block writers", so we are safe to assume that writers indeed CAN block writers in SNAPSHOT isolation level.

More info about how snapshot works can be found in Transaction locking and Row Versioning:

Snapshot isolation also uses row versioning, which does not use shared locks during read operations.
...
When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Every time a row is modified by a specific transaction, the instance of the SQL Server Database Engine stores a version of the previously committed image of the row in tempdb. ...

and under

Behavior when modifying data
...
Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Otherwise, locks are not acquired on data until the data is to be modified. When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the SQL Server Database Engine and there is no way to disable the update conflict detection.

This paragraph above and especially the part that locks are indeed acquired just before the data is to be modified explains the blocking (writers to writers) behaviour (and considering the "Important" note mentioned at the top):

So under SNAPSHOT isolation:

• Let's say that transaction A reads various data. No locks are acquired.
• Other transactions can read the same data and no locks are acquired either.
• When transaction A tries to modify some data, a lock is acquired and held until the end of transaction, which can be either commit or rollback.
• Other transactions can still read the data (of course they see some previous version). No locking.
• If transaction B however tries to modify data already held by some lock (from uncommitted transaction A), then it will try to put some lock as well, and B will be blocked.
• If transaction A commits at some point, then its locks will be released, so transaction B will be unblocked. However B will now see an update conflict and not be able to succeed.
• If transaction A rollbacks, then its locks will be released and its row versions removed, so transaction B will be unblocked and its modifications can go forward.