Phantom reads example in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 74 of sequel server
tutorial in this video well discus
phantom read concurrency problem with an
example so when does a phantom read
happen
phantom read happens when one
transaction executes sit very twice and
it gets different number of flows in the
results at each time this happens when a
second transaction inserts a new row
that matches the where Clause of the
query executed by the first transaction
let's understand this with an example
here we have a table that has three
employees at the moment we also have got
two transactions here transaction 1
starts first and it issues the first
read look at the Select query select
star from EMP where ID between 1 & 3 so
this select query is going to produce 2
rows from this table after transaction 1
has issued the first read it's busy
doing some other work so at that point
transaction 2 starts and it inserts a
new employee with ID equal to 2 when
transaction 2 completes at that point
transaction 1 issues the second read now
notice the row that transaction 2 has
inserted into the table ID equal to 2
and look at the we're close here between
ID between 1 & 3 so this new record
satisfies the where clause so the second
read here is going to produce 3 rows and
the first read has produced two rows so
between read 1 and read 2 of this
transaction 1 we have different number
of rows in the result set so this is
called as the phantom read problem let's
look at this problem in action let's
flip to sequel server management studio
so at the moment we have that employees
table with 3 rows let's begin a
transaction here and what we want to do
is select star from TBL employees where
ID between 1 & 3 and then we want to
simulate that this transaction is doing
some other work so I'm going to make
this transaction wait for 10 seconds and
then issue the second read and then
finally
commit the transaction I have another
instance of sequence level management
studio running so let's make this
transaction to and from here we want to
insert a new employee so insert into TBL
employees values let's insert an
employee with ID 2 and let's name the
employee Marcus all right so let's
comment the select statement and execute
our transaction 1 and simultaneously
execute transaction to look at this
transaction to is not blocked while
transaction 1 is still executing so
transaction 2 is allowed to insert that
new row and if you look at the result
sets that trend this transaction 1 has
produced the first trends that are read
has produced 2 rows and the second read
has produced 3 rows so we have different
number of rows within the result set so
this is called the phantom read problem
now let's see how to fix this problem to
fix the phantom read problem we can
either use snapshot or serializable
isolation levels look at the phantom
reads column here with snapshot and
serializable we don't have that problem
where as with all the other isolation
levels we do have phantom read problem
so to fix this all we need to do is set
the transaction isolation level of
transaction 1 as serializable so let's
go ahead and do that
set transaction isolation level to
serializable and let's delete that row
with ID 2 from the table so delete from
TBL employees where ID equals 2 and
let's just select the rows from the
table to make sure that we have only 3
rows with IDs 1 3 and 100 and let's
execute our transaction 1 and
simultaneously transaction 2 now notice
that transaction 2 is blocked while
transaction 1 is executing transaction 2
will be allowed to continue
only after transaction
one has completed look at this after
transaction one has completed
transaction two is allowed to continue
and it was able to insert the new
employee the tidy 2 into the table but
look at the result sets of transaction 1
both the reads has produced the same
number of rows 2 n 2 so setting the
isolation level to serializable has
fixed the problem now let's understand
how it has fixed that problem now when
we have set the transaction isolation
level to serializable it has issued a
range lock now what do we mean by range
lock if you look at the Select query
here you know ID between 1 & 3 so for
that range of rows sequel server has
issued a lock meaning within that range
we will not be able to insert any new
rows so that has prevented this phantom
read problem now let's understand what
is the difference between repeatable
read and serializable
isolation levels repeatable read
prevents only non repeatable read
problem repeatable read isolation level
ensures that the data that one
transaction has read will be prevented
from being updated or deleted by any
other transaction but it does not
prevent new rows from being inserted by
other transactions
hence repeatable read isolation level
can result in phantom read concurrency
problem so there is a very subtle
difference between repeatable read and
serializable isolation levels and that
is repeatable read prevents only
non-repeatable reprogram where are
serializable isolation level claimants
both non repeatable read and phantom
read problems now serializable isolation
level ensures that the data that one
transaction has read will be prevented
from being updated or deleted by any
other transaction it also prevents new
rows from being inserted by other
transactions so this isolation level
prevents both non repeatable read and
phantom read problems thank you for
listening and have a great day