Short cheat sheet on locks when reading and changing data depending on the transaction isolation level in MSSQL

Read Uncommitted

• if in one transaction to change the data - select this data (in another transaction or without a transaction) will not wait for the end of the first transaction and will return the recorded data of uncommitted transactions
• if in one transaction to read data - updates of this data in another transaction will not wait for the end of the first transaction
• Shared locks are not used. What is similar to installing NOLOCK hint in all selects in Read Commited
• exclusive locking is set during the execution of the statement and removed at the end of the transaction

Read Committed + read_committed_snapshot off
')
(alter database xxx set read_committed_snapshot off)

• if you change data in one transaction, the select data (in another transaction or without a transaction) will wait for the end of the first transaction. A select with a NOLOCK hint will return changed, but not fixed data.
• if in one transaction to read data - updates of this data in another transaction will not wait for the end of the first transaction
• Shared locking is set in the course of work of the statement and removed at the end of the statement.
• exclusive locking is set during the execution of the statement and removed at the end of the transaction

Read Committed + read_committed_snapshot on

(alter database xxx set read_committed_snapshot on)

• if you change data in one transaction, the select data (in another transaction or without a transaction) will not wait for the end of the first transaction and will return the values ​​at the moment of the start of the statement . A select with a NOLOCK hint will return changed, but not fixed data.
• if in one transaction to read data - updates of this data in another transaction will not wait for the end of the first transaction
• Shared locking is not used; instead, the Row Versioning mechanism is used - the data of the updated records is stored in tempdb
• exclusive locking is set during the execution of the statement and removed at the end of the transaction

Repeatable Read

• if you change data in one transaction, the select data (in another transaction or without a transaction) will wait for the end of the first transaction. A select with a NOLOCK hint will return changed, but not fixed data.
• if in one transaction to read data - updates of this data in another transaction will wait for the end of the first transaction
• Shared locking is set in the process of the work of the statement and removed at the end of the transaction , unlike Read Commited
• exclusive locking is set during the execution of the statement and removed at the end of the transaction

Serializable

• if you change data in one transaction, the select data (in another transaction or without a transaction) will wait for the end of the first transaction. A select with a NOLOCK hint will return changed, but not fixed data.
• if in one transaction to read data - the update of this data in another transaction will wait for the end of the first transaction
• Shared locking is set in the process of work of the statement and removed at the end of the transaction.
• exclusive locking is set during the execution of the statement and removed at the end of the transaction
• set exclusive range locks to keys that fall within the ranges of query criteria, which prohibits inserts from new entries that fall into these ranges, which is similar to setting HOLDLOCK hint in all selets in Read Commited

Snapshot
(alter database xxx set allow_snapshot_isolation on)

• if you change data in one transaction, the select data (in another transaction or without a transaction) will not wait for the end of the first transaction and will return the values ​​at the time the transaction starts . A select with a NOLOCK hint will return changed, but not fixed data.
• if in one transaction to read data - updates of this data in another transaction will not wait for the end of the first transaction
• Shared locking is not used; instead, the Row Versioning mechanism is used - the data of the updated records is stored in tempdb
• exclusive locking is set during the execution of the statement and removed at the end of the transaction

It was checked on MSSQL 2014.