SQL Server: Demonstrating Dirty-Reads

Isolation (The I in ACID), is a term that ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially (wiki).

In other words, if you do the operations again with the same base data the outcome will always be the same.

The trade off is between the isolation level (concurrency) and data anomalies, so highest concurrency level has the most anomalies and no concurrency at all offers no anomalies.

Anomalies:

1. Dirty reads (reading non persistent data).
2. Non repeatable reads (reading different result for the same query)
3. Phantom reads (reading while a concurrent transaction is in the process of updating/inserting rows)

Isolation levels:

Read Uncommitted (NOLOCK)

Its like traffic jam with no rules, the maximum concurrent level but might have all of the three possible anomalies.

credit: link.

Read Uncommitted (NOLOCK)

The default behavior, high concurrency without dirty-reads.

credit: link.

Repeatable Read.

The same as Read Uncommitted but without repeatable-read, and less concurrency.

Serializable.

No anomalies at all, but no concurrency.

credit: link.

In this demo I would like to demonstrate a dirty read, which is a phenomena relevant only for the Read Uncommitted isolation:

Lets assume we have a table only with two different rows:

MyCleanTable

1. Lets start a transaction at read uncommitted isolation level without ending it (Transaction A):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
BEGIN TRAN
      SELECT * FROM [MyCleanTable]

MyCleanTable

2. Lets start a concurrent update transaction (Transaction B):

BEGIN TRAN

     UPDATE [ConcurrencyDemo].[dbo].[MyCleanTable]
     SET [Value] = 'I am dirty'  
     WHERE [Id] = 1

3. Within the execution context of Transaction A, lets query the table again, and end the transaction:

    SELECT * FROM [ConcurrencyDemo].[dbo].[MyCleanTable]

COMMIT;

MyCleanTable with dirty update

4. Rollback the update transaction (Transaction B):

ROLLBACK;

As we may see, the rollback command rolled back the transaction B update command but the transaction A already was reading the dirty value of I am dirty , even if it was later rolled back, so the actual state of the table is:

MyCleanTable