Preventing Phantom Reads in SQL Server using Locks

In multi user systems, when a user is allowed to read data from a row that has been modified by another user and not yet committed, is known as Phantom Reads.

For example – I have a Students table that contains Students record. I update the student name, with id 5, from ‘Mila’ to ‘Maria’. But before it is committed, another user reads the student with id 5. The other user will get ‘Mila’ for the name but actually the ‘Mila’ is changed to ‘Maria’ and so it becomes Phantom Read (i.e. a non-existent record or phantom).

How to Prevent Phantom Reads

During the time of insert, update or delete work on the table, the SQL Server will automatically place the table under lock. So it will automatically prevent Phantom Reads.

But if one user removes the lock (for faster execution of query) then Phantom reads can happen

Note - Removing the lock can be done with this statement - with(nolock)

Suppose there are 2 users and both of them executes some SQL Statement.

Note - The second user executes the statement after 1 second.

First User executes:

begin tran

update Student set name='Maria' where ID=5

waitfor delay '00:00:15'

rollback     

Second User executes (after 1 second):

select name from Student with(nolock) where ID=5 

Here you can see that the first user changes the student name from ‘Mila’ to ‘Maria’ then after a delay of 15 seconds rolls back it to ‘Mila’.

The second, who executes the SQL statement by relaxing the lock will read the name as ‘Maria’. 

The name ‘Maria’ is a Phantom (non-existant) since first user has reverted back to ‘Mila’

Note – The relaxation of lock can be used for faster execution of Queries in some situations. But I would not recommend using them when doing CRUD Operations in SQL Server.

You can run both the users statements in separate query window to see the result they give to you.

If the second user does not use with(nolock) then SQL Server will make him wait for 15 seconds till the lock gets removed and then gives him ‘Mila’ for the student with id 5.