Tips for Read/Write Locks Depending on Transaction Isolation Level in MSSQL

Read Uncommitted

• If data is being changed in one transaction, selection of this data (in other transaction or without a transaction) will not wait till the first transaction is finished and will return data entries of uncommitted transactions.
• If data is being read in one transaction, updates of this data in other transaction will not wait till the first transaction is finished.
• Shared locks are not used. Identical to setting the NOLOCK hint for all selections in Read Committed.
• Exclusive locks are enabled during statement execution and disabled at the end of the transaction.

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

• If data is being changed in one transaction, selection of this data (in other transaction or without a transaction) will wait till the first transaction is finished. Select with the NOCHECK hint will return modified, but uncommitted data.
• If data is being read in one transaction, updates of this data in other transaction will not wait till the first transaction is finished.
• Shared locks are being enabled during the statement execution and disabled at the end of the statement execution.
• Exclusive locks are being enabled during the statement execution and disabled at the end of the transaction.

Read Committed + read_committed_snapshot on
(alter database xxx set read_committed_snapshot on)

• If data is being changed in one transaction, selection of this data (in other transaction or without a transaction) will not wait till the first transaction is finished, and will return values at the moment of the transaction start. Select with the NOCHECK hint will return modified, but uncommitted data.
• If data is being read in one transaction, updates of this data in other transaction will not wait till the first transaction is finished.
• Shared locks are not used. The Row Versioning mechanism is used instead – data of the updated records is stored in tempdb.
• Exclusive locks are being enabled during statement execution and are disabled at the end of the transaction.

Repeatable Read

• If data is being changed in one transaction, selection of this data (in other transaction or without a transaction) will wait till the first transaction is finished. Select with the NOLOCK hint will return modified, but uncommitted data.
• If data is being read in one transaction, updates of this data in other transaction will wait till the first transaction is finished.
• Shared locks are being enabled during the statement execution and disabled at the end of transaction, unlike Read Committed.
• Exclusive locks are being enabled during the statement execution and disabled at the end of the transaction.

Serializable

• If data is being changed in one transaction, selection of this data (in other transaction or without a transaction) will wait till the first transaction is finished. Select with the NOLOCK hint will return modified, but uncommitted data.
• If data is being read in one transaction, updates of this data in other transaction will wait till the first transaction is finished.
• Shared locks are enabled during the statement execution and disabled at the end of the transaction.
• Exclusive locks are being enabled during the statement execution and are being disabled at the end of the transaction.
• Exclusive range locks are being enabled for keys that meet the query criteria range. Inserts of new records falling within this range are not allowed. Identical to setting the HOLDLOCK hint for all SELECTs in Read Committed.

Snapshot
(alter database xxx set allow_snapshot_isolation on)

• If data is being changed in one transaction, selection of this data (in other transaction or without a transaction) will not wait till the first transaction is finished. and will return values at the moment of the transaction start. Select with the NOLOCK hint will return modified, but uncommitted data.
• If data is being read in one transaction, updates of this data in other transaction will not wait till the first transaction is finished.
• Shared locks are not used. The Row Versioning mechanism is used instead – data of the updated records is stored in tempdb.
• Exclusive locks are being enabled during the statement execution and disabled at the end of the transaction.

Tested on MSSQL 2014.