/* Isolation Levels */
/*
In SQL Server, isolation levels can be broadly categorized as locking, row versioning, and Read uncomitted.
Locking Isolation Levels:
- Read Committed
- Repeatable Read
- Serializable
Row Versioning Isolation Levels:
- Read Committed Snapshot Isolation
- Snapshot Isolation
Uncategorized:
- Read Uncommitted (NOLOCK)
- Still takes schema locks
- Will read data from uncommitted transactions
- Commonly referred to as “Dirty Reads”
- This is the one you should avoid
Locking Isolation Levels:
The locking isolation levels work directly with data in tables and indexes by aquiring shared or exclusive
locks as data is read.
Read queries under locking isolation levels may be blocked by modification queries while waiting to
take shared locks on locked rows, and may also block modification queries under certain conditions where
shared locks are held until the statement has finished executing.
Depending on the strictness of the locking isolation level used, shared locks may either be held or released.
Both Repeatable Reads and Serializable will hold shared locks to prevent modifications from modifying read data.
Row Versioning Isolation Levels:
The row versioning isolation levels will read copies (versions) of locked rows, which can ultimately
result in fewer detrimental blocking and deadlocking scenarios, but is not a free lunch. Nothing ever is.
Both the process of versioning rows, and reading from large stores of versioned rows, may slow workloads down.
The being said, non-locking isolation levels are generally better and less troublesome than alternatives.
How can you tell this is true?
Consider that SQL Server by default uses Read Committed (locking) as its default isolation level, yet nearly every application I see is written extensively with NOLOCK hints, regardless of years of warnings about the perils of doing so. NOLOCK is synonym for Read Uncommitted, of course.
Also consider that when it came time for Microsoft to offer a cloud managed version of SQL Server (Azure SQL Database) they made the default isolation level Read Committed Snapshot.
Much of this is just to set the stage for simpler statements:
- Under row versioning isolation levels:
- Read queries are not blocking write queries
- Read queries do not block write queries
- Deadlocks do not occur between read and write queries
- Under locking isolation levels:
- Read queries can be blocked by write queries
- Read queries can block write queries
- Deadlocks can occur between read and write queries
There are of cource advanced caveats and edge cases that can complicate these simple statements, but those are not from now. So what is row versioning?
*/