Short crib on locks when reading and changing data depending on the level of transaction isolation in MSSQL

    Read Uncommitted

    • if you change data in one transaction - a selection of 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 you read data in one transaction - updates of this data in another transaction will not wait for the end of the first transaction
    • Shared Loki are not used. Which is similar to setting the NOLOCK hint to all selects in Read Commited
    • exclusive locks are set during the execution of the statement and are removed at the end of the transaction


    Read Committed + read_committed_snapshot off

    (alter database xxx set read_committed_snapshot off)

    • if you change the data in one transaction - a selection of this data (in another transaction or without a transaction) will wait for the end of the first transaction. A select with the NOLOCK hint will return the changed but not commomized data.
    • if you read data in one transaction - updates of this data in another transaction will not wait for the end of the first transaction
    • shared locks are set during the operation of the statement and are removed at the end of the statement
    • exclusive locks are set during the execution of the statement and are removed at the end of the transaction




    Read Committed + read_committed_snapshot on

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


    Repeatable Read

    • if you change the data in one transaction - the selection of this data (in another transaction or without a transaction) will wait for the end of the first transaction. A select with the NOLOCK hint will return the changed but not commomized data.
    • if you read data in one transaction - updates of this data in another transaction will wait for the end of the first transaction
    • shared locks are set during the operation of the statement and are removed at the end of the transaction , unlike Read Commited
    • exclusive locks are set during the execution of the statement and are removed at the end of the transaction


    Serializable

    • if you change the data in one transaction - the selection of this data (in another transaction or without a transaction) will wait for the end of the first transaction. A select with the NOLOCK hint will return the changed but not commomized data.
    • if you read data in one transaction - an update of this data in another transaction will wait for the end of the first transaction
    • shared locks are set during the operation of the statement and are removed at the end of the transaction
    • exclusive locks are set during the execution of the statement and are removed at the end of the transaction
    • exclusive range locks are set for keys that fall into the ranges of query criteria, which prohibits inserts of new records that fall into these ranges, which is similar to setting the HOLDLOCK hint in all selects in Read Commited


    Snapshot
    (alter database xxx set allow_snapshot_isolation on)

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


    It was tested on MSSQL 2014.

    Also popular now: