Non repeatable read example in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 73 of sequel server
tutorial in this video well discuss the
non repeatable read concurrency problem
with an example so when does a non
repeatable read happen non-repeatable
reads when one transaction reads the
same data twice and another transaction
updates the data in between the first
and second read of transaction 1 let's
understand this with an example here we
have a table that keeps track of how
many iPhones we have install notice
there are two transactions transaction 1
and transaction 2 transaction 1 starts
first and it issues the first read it
reads items in stock as 10 and before
transaction 1 issues the second read
it's actually doing some other verb at
that point transaction 2 starts and it
updates items in stock as five when
transaction 2 completes at that point
transaction 1 issues the second read and
it reads items in stock as 5 so if you
look at these 2 reads read 1 and read
two the transaction 1 has issued notice
read 1 produce 10 and redo produced 5 so
we are not able to repeat the same read
that's why this problem is called as the
non repeatable read problem let's look
at this in action let's flip to sequel
server management studio so here we have
the table TBL inventory at the moment
items in stock equals 10 so let's make
this of a transaction 1 let's begin the
transaction and all we want to do is
select items in stock from TBL inventory
table where ID equals 1 so that's our
first read and then we want this
transaction 1 to do some of the work and
to simulate that I'm going to make this
wait for 10 seconds so we want this
transaction to wait for 0 hours 0
minutes and 10 seconds and then we want
to issue our second read and then
finally we want to commit the
transaction
so that's our transaction 1 I have
another instance of sequence level
management studio running let's make
this our transaction 2 and all we want
to do here is update TBL inventory table
set items in stock equals 5 wear ID
equals 1 all right
let's come in the Select query and
notice at the moment items in stock
equals 10 so let's execute our
transaction 1 and let's execute our
transaction to notice that transaction 2
completed immediately transaction 1 is
still being executed because we made it
deliberately to wait for Wen's 10
seconds and look at the reads read 1
produced 10 and read to produced 5 so we
are not able to repeat the read hence
the name non repeatable read so how can
we solve this non repeatable read
problem so if you look at this table
right here so we have the non-repeatable
reads problem here read uncommitted and
read committed transaction isolation
levels have that problem whereas repeat
will read snapshot and serializable
these isolation levels does not have
that problem so to fix this set the
isolation level of transaction 1 to
repeat will read or any other higher
isolation level so what this repeat will
read going to do is it is going to use
some additional logs to ensure that
whatever data this transaction 1 has
read will not be updated or deleted by
any other transaction in our case
transaction 2 will not be able to delete
or update that data so that is going to
fix our problem the reason why we have
that problem here is because you know
after transaction 1 has issued the first
tree transaction 2 is able to update the
same data to a different value and then
when transaction 1 issues the second
read you know it gets a different value
if we can prevent transaction 2 from
updating the data that transaction 1 has
read then we can solve this non
repeatable read problem and that's what
repeatable read isolation
is going to do so to fix this let's go
ahead and set transaction isolation
level of transaction one to repeatable
read and let's update items in stock 210
and now let's change it to five and if
we select the data from the table look
at that items in stock equals ten now
let's execute our transaction one and
let's exclude our transaction to look at
that transaction two now is blocked
because we have used the repeatable read
isolation level so transaction 2 now is
not able to update that data and when
transaction 1 completes at that point
transaction 2 is able to make any
changes to that data that's why this
transaction 2 was blocked until
transaction 1 is completed and if you
look at the reads here notice both of
them produces 10 and 10 in our next
video we'll discuss the phantom read
concurrency problem thank you for
listening and have a great day