"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

March 14, 2010

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!!!

1 comment:

jamiet said...

Hi,
Nice post.
In your demo of phantom reads you use:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Am I correct in thinking that phantom reads can also occur under READ UNCOMMITTED & READ COMMITTED too? My testing suggests that they can, but I want to make sure my understanding is correct.

thanks