SQL server dirty read example Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 71 of sequel server
tutorial in this video we'll discuss
dirty read concurrency problem with an
example this is continuation to part 70
so please watch about 70 before
proceeding when the sibility read
happened a dirty read happens when one
transaction is permitted to read data
that has been modified by another
transaction that has not had been
committed in most cases this would not
cause a problem however if the first
transaction is ruled back after the
second reads the data this second
transaction has dirty data that does not
exist in the database anymore
let's understand this with an example
here we have a table that keeps track of
how many iPhones we have caught in stock
let's say for the purpose of this demo
we are in two iPhones selling business
whenever we sell anything there are at
least two things that we need to do
first we need to reduce the inventory by
the number of items that we have sold
second we need to build a customer and
let's say we have this transaction one
that is doing both of those things for
us let's say we have a customer who
wants to buy one iPhone so first we need
to reduce the items in stock at the
moment we have got 10 so it updates the
items in stock to 9 and second we need
to build the customer let's say the
customer wants to pay using his debit
card that means this transaction 1 has
to talk to the payment gateway provider
to check if there are sufficient funds
and let's assume this payment gateway
provider is going to take a few seconds
to get back to us
so while transaction 1 is billing the
customer there is another transaction
transaction 2 and all this transaction
want to do is read the number of items
in stock and it reads that number which
is 9 and then a few seconds later
transaction 1 in hour starts proceeding
because the payment gateway came back
with insufficient funds that means we
cannot proceed with this transaction so
it's going to roll that transaction back
when we roll the transaction Bank what's
going to happen to items in stock it
will be reverted to 10 but what is the
number that transaction 2 is looking at
9 and that is dirty read that is dirty
data so why did this dirty deed happen
this happened
because in this example transaction 2
was allowed to read data that was
modified by transaction 1 but at that
point when transaction to read that data
that data was not committed by
transaction 1 so dirty reads happen when
we permit a transaction to read data
that has been modified by another
transaction but that has not been
committed yet let's look at this example
in action so let's flip to sequel server
management studio so I've already
created that inventory table and if you
look at the inventory table at the
moment we have got 10 items in stock and
here I have two instances of sequel
server management studio so the first
instance here on the left is going to
eat you transaction 1 and the one on the
right is going to eat you transaction 2
so let's begin a transaction and the
first thing that we want to do is update
the table it's a update table TBL
inventory and we want to set items in
stock equals 9 where ID equals 1 and we
want to build the customer right and to
build the customer since he's paying by
his debit card we want to talk to the
payment gateway provider let's just
simulate that and to simulate the
waiting I'm going to use wait for and
introduce some artificial delay let's
say we want to block this for 0 hours 0
minutes and 15 seconds and then
eventually you know for this example the
payment gateway is going to come back
with an insufficient funds at that point
we want to roll back the transaction
right and on the right hand side all I
want to do is read you know the
inventory level so select star from TBL
inventory we're ID equals 1 okay and
look at this when we execute this you
know the transaction is processing and
I'm going to execute this code by d4
transaction two will be blocked look at
that transaction two it says executing
query and this will complete its
execution as soon as transaction one
completes okay that's because the
default transaction isolation level is
read committed look at the name read
committed that means a transaction will
be able to read some data and that data
should be committed okay that's what
read committed isolation will do because
here this transaction is updating this
row with ID one and you know it updated
that and that statement is part of the
transaction and here it's blocked inhale
it's actually waiting for something to
happen so this transaction is not
completed yet that means whatever change
it has done it's not committed yet and
this transaction too is going to work
you know with the same data it needs the
same data it's trying to read that same
data and that data is not committed yet
so that's why transaction two will be
blocked because the default isolation
level is read committed it can only read
the committed data now to be able to
read uncommitted data we have to use a
different isolation level and that is
read uncommitted so let's set
transaction isolation level to read
uncommitted okay and now let's go ahead
and execute this and look at this when
we execute transaction two it executes
right away and look at what value we get
nine that is the uncommitted data and
here transaction one is still processing
eventually it's going to rule the
transaction back and when that happens
you know the items in stock will be
reverted to ten because you know we roll
the transaction back look at that
transaction one is looking at ten but
transaction two is looking at mine which
is dirty data which is uncommitted data
so read uncommitted transaction
isolation level is the only isolation
level that has dirty read side effect no
other isolation level has the side
effect and this is the least restrictive
of all the isolation levels when this
transaction isolation level is set it is
possible to read uncommitted data
uncommitted data is also called as dirty
data that's because you know there is a
possibility of that being rolled back
and read uncommitted transaction
isolation level is the only isolation
level that has dirty read side effect
now here to read the dirty data we are
setting the transaction isolation level
to read uncommitted another option to
read dirty data is by using no log table
hint and if you look at the query right
here look at that it's using no log
table hand and this is equivalent to the
query that we have here in transaction 2
vendor so let's look at that quickly in
action so I'm going to execute just this
piece of code right here begin
transaction and update this so we begin
the transaction but we didn't either
commit or roll it back so now let us go
ahead and change the isolation level to
read committed and now if we execute
this statement what is going to happen
this will be blocked why because this
isolation cell level is saying only a
love reading committed data now one way
to read that uncommitted data is by
using read uncommitted isolation level
the other way is by using no log table
hint so on this table TBL inventory I'm
going to use no log table hint and that
means it is going to allow us to read
that dirty data look at that we are able
to read that 9 now if we roll the
transaction back and if we execute this
we should get 10 back ok so to read
dirty data you can either set the
transaction isolation level to read
uncommitted or you can use the no log
table hint thank you for listening and
have a great day
you