SQL Server: Demonstrating Non Repeatable-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).

Within the same transaction we would expect that querying the same query twice would yield the same result, if not it is called a Non Repeatable-Read.

In this demo I would like to demonstrate a non repeatable read, which is a phenomena that can occur when setting the transction isolation level of Read Uncommitted:

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

Product table

What will happens if we will start a transaction (and set the isollation level to READ COMMITTED)

BEGIN TRAN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Inside the transaction lets read the price of productId 1:

SELECT ProductID, Price FROM Products WHERE ProductID = 1;

Now, the trick is to simulate a delay:

WAITFOR DELAY '00:00:30';

And lets read it again and commit the first transaction:

SELECT ProductID, Price FROM Products WHERE ProductID = 1;

COMMIT;

This is what we expect to see (after 30 seconds):

Repeatable Read

Now lets run the first transaction again, but lets open another tab and run a second transction to update the Price within the 30 seconds:

BEGIN TRAN;

UPDATE Products SET Price = 120.00 WHERE ProductID = 1;

COMMIT;

Now the first select will retrun the ProductId while its Price was 100.00 and the seconds query returns 120.00:

Non Repeatable Read