How does transaction isolation level work with respect to read/writes and read/write locks?

1
votes

I understand the dirty read, non-repeatable read and phantom read issue.

Also I have read about isolation levels: read uncommitted, read committed, repeatable read, serializable.

I also understand that reading results in a shared lock. To get a shared lock there shouldnt already be an active exlcusive lock. Where as insert/update/delete results in an exclusive lock. To get an exclusive lock there shouldn't be any other exclusive or shared lock active.

For each level, none of the articles I have read explain the isolation level concept with respect to:

  1. Whether the level is applicable to a read or write transaction or both.

  2. Whether reading/writing enforces any read/write locks different to the above explanation

  3. Transaction is a all or nothing concept with regards to write. Whereas is transaction isolation level a concept with regards to reads only?

If anyone can enlighten regarding these points for each level then it will be very helpful.

1
sqlsql-serversql-server-2016
I've thought the documentation is quite clear: 1. Controls the locking and row versioning behavior of Transact-SQL statements. And as of its name it controls all the transactions within a session. There's no reason to hide some details in the description to this statement (I hope MS publishes all the exceptions for the cases inside the article). 2. Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. – astentx

1 Answers

0
votes

You might find these articles by Paul White to be very useful.

But in answer to your questions:

Firstly, Shared vs Exclusive locks define what is allowed to happen concurrently against the lock. The isolation level defines how much is locked and how long for.

  1. Isolation level is applicable to both types of transactions. SNAPSHOT in particular has different effects depending whether a write is involved or not.
  2. There are also Intent locks, which are equivalent versions of other locks and allow a lock to be escalated from Page or Row Lock to Table/Partition.
    You also have Schema Modification locks, which prevent anyone changing the table/column (or index) definitions from underneath you (this is applicable even to NOLOCK).
  3. The isolation level defines how much gets locked, is it a row or a range? It also indicates what happens to a lock after it has been used. Is it held until the end of the transaction, or is released as soon as a commit happens?