Non-repeatable read and Phantom read

While trying to learn on concurrency problems I wanted to know difference between Non-repeatable read and Phantom read. The following post in MSDN is useful.

Nonrepeatable read: If somebody performed UPDATE or DELETE of any of the rows you read earlier.
Phantom: If anybody INSERTed a row within the range you had for an earlier query (i.e., you see new rows).

A Quick Experiment on Phantom Reads

Step 1 - Created a Test Table and Populated few records

USE TestDatabase
IF OBJECT_ID ('TestIsolation') IS NOT NULL DROP TABLE TestIsolation
CREATE TABLE TestIsolation
(Id INT Identity (1,1) PRIMARY KEY CLUSTERED ,
Value CHAR(20) )

DECLARE @I INT
SET @I = 0
WHILE 1=1
BEGIN
     INSERT INTO TestIsolation(Value)
     VALUES ('Value' + CONVERT(VARCHAR(20), @I))
     SET @I = @I+1
     IF @I > 100
     BREAK;
END

Step 2-Open a Session and Run Below Command

USE TestDatabase
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
     SELECT * FROM TestIsolation
    WAITFOR DELAY '00:00:05'
    SELECT * FROM TestIsolation
ROLLBACK

Step 3 - Parallelly Open another session and run below command

USE TestDatabase
GO
BEGIN TRAN
     INSERT INTO TESTIsolation(VALUE)
     VALUES ('New Entry')
COMMIT

Step 4 - Output would be as below for Step 2. As you see newly inserted row is reflected in second select query (103 rows affected)

Note: For Step2 if You change isolation level to Serializable, You will see same number of affected rows  for the above case. A serializable scan acquires a key range lock which prevents the insertion of any new rows anywhere within the range

Another Quick Experiment on Nonrepeatable Reads

Nonrepeatable Read - Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time

Step 1 - Run the below query in one session

USE TestDatabase

GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
     SELECT * FROM TestIsolation
     WAITFOR DELAY '00:00:05'
     SELECT * FROM TestIsolation
ROLLBACK

Step 2- When Step1 executes run Step2

USE TestDatabase
GO
BEGIN TRAN
    Update TESTIsolation
    SET VALUE = 'Modified Value'
    WHERE ID = 100
COMMIT

Step 3 - Output for Step 1

For Id 100 Data is modified as you can see above.

For Read-Committed Isolation level Advantages are

• Only committed changes are visible
• It acquires short lived share locks on a row by row basis
• The duration of these share locks is just long enough to read and process each row 

Disadvantages of Read-Committed Isolation Level

• Nonrepeatable Read - Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time
• Phantom reads occur when an insert action is performed against a row that belongs to a range of rows being read by a transaction.

Happy Learning!!!