How to simulate non-repeatable read (SQL Server)

Question

I read following lines of code on msdn but I am not clear with this and want to simulate.

Nonrepeatable reads occur when a second transaction accesses the same row several times and reads different data every time. This involves multiple reads of the same row. Every time, the information is changed by another transaction.

I didn't understand how second transaction can access the data if row is locked by first transaction under read committed isolation level.

Answer 1

• Non-repeatable read(fuzzy read) is that a transaction reads the same row at least twice but the same row's data is different between the 1st and 2nd reads because other transactions update the same row's data and commit at the same time(concurrently).

You can simulate non-repeatable read with SQL Server and 2 command prompts.

First, set READ COMMITTED isolation level to occur non-repeatable read:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Then, create person table with id and name as shown below.

person table:

id	name
1	John
2	David

Now, take these steps below with MSSQL queries:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN TRAN; GO;		T1 starts.
Step 2		BEGIN TRAN; GO;	T2 starts.
Step 3	SELECT * FROM person WHERE id = 2; GO; 2 David		T1 reads David.
Step 4		UPDATE person SET name = 'Tom' WHERE id = 2; GO;	T2 updates David to Tom.
Step 5		COMMIT; GO;	T2 commits.
Step 6	SELECT * FROM person WHERE id = 2; GO; 2 Tom		T1 reads Tom instead of David after T2 commits. *Non-repeatable read occurs!!
Step 7	COMMIT; GO;		T1 commits.

Answer 2

Open 2 query windows in SSMS

First query:

set transaction isolation level read committed
begin transaction
select * from _tmp

Second

update _tmp set id = 2

Create and populate table

create table _tmp ( id int)
insert _tmp (id )values(1)

Run first query, then run second then run select statement from first query

You'll see 1 first time and 2 second

Answer 3

how second transaction can access the data if row is locked by first transaction

You are wrong in your assumption that the first transaction would lock the row. It will only lock it for a short time, just enough to read it, then release the lock. Subsequent reads will lock it again, so anything can happen to the row in the meantime.

Consider this time sequence and see how transaction T1, under read committed isolation, sees different results for the same SELECT:

T1: begin transaction
T1: select * from table <-- returns row 1 and row 2
T2: begin; insert into table row 3; commit;
T1: select * from table <-- returns row 1, row 2 and row 3
T3: begin; delete row 2 from table ; commit;
T1: select * from table <-- returns row and row3

Answer 4

You can simulate it in this way. Open Microsoft SQL Management Studio. Open 2 tabs and start a transaction in both.

Select data in first window and modify it in second and commit changes.

Then, re run previous select. Data has been changed. This is non repeatable read phenomen. 

Answer 5

Nonrepeatable reads occur when a second transaction accesses the same row several times and reads different data every time. This involves multiple reads of the same row. Every time, the information is changed by another transaction.

The point here is:

• transaction A reads the data using SELECT ..... and takes a shared (read) lock on those rows it reads - but just while it reads them (and releases the lock right away after first reading them

• transaction B can now modify some of those rows

• when transaction A comes back and read those rows again, using SELECT ... - it might get different results this time around.

This is the problem that you have when using the default setting - READ COMMITTED - a read operation takes a shared lock only just while reading and releases it right away after it's read the data. After that, other transaction can (and will!) modify the data, so reading the same rows again might yield a different result.

With REPEATABLE READ, the reading transaction keeps a shared (read) lock on those rows it reads until the end of the transaction - thus preventing other transactions from modifying the data before it ends.

So really - any read operation using the default READ COMMITTED isolation level is a non-repeatable read. No need to simulate - those are the real things!