SQL server lost update problem Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 72 of sequence server
tutorial in this video well disk is the
lost update problem in sequel server
when does the lost update problem happen
the lost update problem happens when two
or more transactions read and update the
same data
let's understand this with an example
here we have a table that keeps track of
how many iPhones we have in stock notice
from the diagram there are two
transactions transaction 1 and
transaction 2 transaction 1 starts first
and it reads the number of items in
stock as 10 around the same time
transaction 2 also starts and it also
reads the number of items in stock as 10
transaction 2 makes the sale of 2 items
so it has to update items in stock 10
minus 2 is 8
transaction 2 is going to update items
in stock as 8 in the meantime
transaction 1 makes the sale of 1 item
and this transaction 1 is still thinking
the number of items in stock as 10 so 10
minus 1 is 9 so transaction 1 is going
to update items in stock as 9 so what it
just happened
transaction 1 has silently overwritten
the update of transaction 2 that means
we have lost the update of transaction 2
that is why this problem is called as
lost update problem so if you look at
these two transactions they are making a
sale of 3 items together that is
transaction 2 is making a sale of two
items transact one is making a sale of
one item and a total number of items in
stock is 10 so 10 minus 3 the items in
stock at the end of these two strands
actions should have been 7 but because
of the lost update problem items in
stock is 9 at the end of both of these
transactions so let's look at this
example in action let's flip this equals
little management studio at the moment I
have two instances of sequel server
management studio running and if you
look at this inventory table notice that
items in stock at the moment is 10 so
let's make this transaction one vendor
and I'm going to begin a transaction
here and let's declare a variable let's
call this items in stock and there's
going to be of type integer select add
items in stock equals items in stock
from TBL inventory table where ID equals
one and let's make this transaction wait
for 10 seconds because we want
transaction to to complete before
transaction 1 so I'm going to make
transaction 1 wait for a few seconds so
wait for delay and let's make this wait
for 0 hours 0 minutes and 10 seconds and
then transaction 1 is going to make a
sale of one item so set at items in
stock equals at items in stock minus 1
and then we want to update the table so
update TBL inventory set items in stock
equals at items in stock where ID equals
1 and then we want to print the value n
at items in stock and then we want to
commit the transaction all right let's
copy the same code and paste it in the
other instance of sequence of
management's to do so and let's make
this transaction 2 we want this
transaction to to complete before
transaction 1 so I'm going to make this
transaction wait just for one second
and we want this transaction to make a
sale of two items so let's make that two
alright so we have two transactions
transaction 1 is going to make a sale of
one item transaction 2 is going to make
a sale of two items all right so let's
first execute transaction 1 and now
let's execute transaction 2 so notice
transaction 2 completes first and
get the items in stock it updates the
value as 8:00 and after transaction 1
completes look at the value it's 9 so if
we actually select the value from TBL
inventory look at that items in stock is
still 9 it should have actually be in 7
right and that is because this
transaction 1 has silently overwritten
the update of transaction 2 now the
default isolation level for sequel
server is read committed this lost
update problem is present in read
uncommitted and read committed isolation
levels with the other higher isolation
levels like repeatable read serializable
and snapshot isolation levels we don't
have this lost update problem now let's
actually make the transaction isolation
level in both of the cases to repeatable
read and see if we will have this
problem so first of all let us go ahead
and update TBL inventory table set items
in stock equals 10 search should set
that value back to 10 now what I'm going
to do is set transaction isolation level
to repeatable read and let's do the same
thing for transaction 2 as well
all right so now look at this we execute
transaction 1 and we also execute
transaction 2 look at this transaction 2
now is actually blocked
that's because repeatable read is going
to use additional logs so that the data
that this transaction has already read
no other transaction can modify or
delete that data and look at this
transaction 1 completed successfully
but transaction 2 failed and look at the
error message that we get let's actually
copy this error message and paste that
into a notepad so can we can read it so
if you look at this error message it
says transaction was deadlocked on lock
resource with another process and has
been chosen as the deadlock with them
rerun the transaction so this
transaction right here this has been
chosen as a deadlock victim because you
know this transaction has already read
some data and it's doing some update for
that and since we are using repeatable
read isolation level this is going to
ensure that no other transaction can
modify or delete the data that this
transaction 1 has read okay and you know
after this transaction 1 is complete we
have stale data in the memory here so
when this try to update that in the
table it failed with an error message
and this is made a deadlock victim
because this was waiting for this
transaction to complete ok and it's
saying us to rerun the transaction and
look at this when we read on this
transaction the number of items in stock
should be updated correctly and that is
7 so at the end of both of these
transactions we should have a value of 7
and that's what we get when we use
repeatable read isolation level because
we don't have the lost update problem so
here is the example that we have just
discussed and notice this dirty reads
problem we have that only with read
uncommitted isolation level but not with
any of the isolation levels if you look
at law
to update problem we have that side
effect with read uncommitted and read
committed isolation levels but not with
repeatable read snapshot or serializable
thank you for listening and have a great
day