Introduction to SQL Server Transactions (Transaction Isolation Part 4)

This is forth part of "Introduction to SQL Server Transaction" series. You can see previous sections below:

Part 1

Part 2

Part 3

In this part we discuss two SQL Server isolation levels in deep.

Read Uncommitted – The “Wild West” of Isolation

Read Uncommitted is the lowest isolation level. It allows dirty reads, non-repeatable reads, and phantoms – basically all issues relate to concurrency can be seen in this isolation level. No locks are taken on reads, so you can read uncommitted changes. 

We can set the isolation level to Read Uncommitted on SQL Server using statement like below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Also in query level we can use query hint (NOLOCK) to force Read Uncommitted isolation level.

Example: Dirty-read scenario. A pending payment transaction withdraws $45 from Betty’s $78 balance, then rolls back. Meanwhile an ATM transaction (right) reads the balance as $33. The ATM saw a dirty value that never became final.

Demonstration:

-- Session A: Begin a transaction and update, but do not commit immediately.

BEGIN TRAN;

SELECT Balance FROM BankAccount WHERE AccountNumber = 'Betty'

-- This return $78

UPDATE BankAccount

SET Balance = Balance - 45

WHERE AccountNumber = 'Betty';

-- Wait or do other work before committing/rolling back...

WAITFOR DELAY '00:00:10';  

ROLLBACK;  

-- Session B (simulated concurrently): Read without locking

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT Balance FROM BankAccount WHERE AccountNumber = 'Betty';

SELECT in Session B will return 33 (assuming you have run the Session B within 10 second delay in Session A). But real balance is 78.

Under Read Uncommitted, SQL Server does not issue any shared locks on reads, so a select can return “dirty” changes from another transaction. The only advantage is maximum concurrency (no waiting), but at the cost of consistency: all anomalies can happen.

Read Committed – Default Safe Reads

Read Committed is the SQL Server default isolation level (when no explicit level is set). It prevents dirty reads: you will never see another transaction’s uncommitted changes. However, it still allows non-repeatable reads and phantoms (unless you enable row-versioning, more about this in later blog posts). 

In locking mode (SQL Server’s default), a read places shared locks that last only for the duration of each statement. If another transaction has an exclusive lock on a row, your SELECT will wait until that transaction commits or rolls back. Thus, Read Committed “protects” your reads from dirty data.

-- Session A: Start and update a row, but hold it.

BEGIN TRAN;

UPDATE BankAccount

SET Balance = Balance - 45

WHERE AccountNumber = 'Betty';

-- (Session A, not yet committed), carrying out other statement execution

-- Session B: Default Read Committed SELECT

SELECT Balance FROM BankAccount WHERE AccountNumber = 'Betty';

-- Session B will *wait* here until Session A commits or rolls back.

As you can see above, session B waits, in executing query mode (no results).

-- If Session A commits, B then sees the new balance (i.e. 33) ; if A rolls back, B sees the old balance (i.e. 78).

COMMIT;  -- Session A eventually commits or rollbacks

This will be the result in Session B:

In above demonstration, SQL server used row level exclusive lock in Session A. Then because of the default isolation level (i.e. Read committed), Session B has used shared lock. However, since "Betty's" row is exclusively locked by Session A, Session B couldn't get the shared lock on the row. Therefore, Session B (select) had to wait till Session A committed or rollback the transaction.

Since Session B had to wait, till Session A finishes, there were no dirty reads (didn't see the temporary balance of 33). But as you can see, we now have locking (i.e. Session B waiting) scenario. This is why you should handle isolation levels very carefully.

Furthermore, data can be changed by other transactions between individual statements… resulting in nonrepeatable reads or phantom data”. In other words, each SELECT in your transaction sees only committed data at that moment, but different SELECTs in the same transaction might see different results if other transactions commit in between.