Difference between snapshot isolation and read committed snapshot Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 77 of sequel server
tutorial in this video we'll discuss the
difference between snapshot isolation
and read committed snapshot isolation
this is continuation to parts
75 and 76 so please watch these videos
before proceeding we have the
differences here in the table snapshot
isolation is vulnerable to update
conflicts
whereas read committed snapshot
isolation is not if you have a
transaction that's using snapshot
isolation and if that transaction is
trying to update a piece of data that
another transaction is already updating
at the same time then an update conflict
occurs and your transaction terminates
and rules back with an error let's look
at that in action I have two instances
of sequence level management studio here
we have this table TBL inventory which
is keeping track of how many iPhones we
have in stock at the moment items in
stock value is 10 and we have caught two
transactions here transaction 1 on the
left and transaction 2 on the right
let's prepare our sample DB database to
use snapshot isolation to do that we use
this alter database statement so let's
go ahead and execute this one now if you
look at what this transaction 1 is doing
it's updating items in stock value to 8
and if you look at transaction 2 it's
doing the same thing it's also updating
items in stock to a value of 5 notice
both of the transactions are trying to
update the same piece of data so let's
go ahead and execute part of our
transaction 1 notice we have not
executed commit transaction statement
that means transaction 1 is still in
progress now let's go ahead and execute
transaction 2 and look at what just
happened transaction 2 is blocked it
will be blocked until transaction 1
completes that's because transaction 2
is trying to update the same data that
transaction 1 is updating at the same
time and just look at what's going to
happen when we come at this transaction
so upon committing the transaction
transaction 1 has completed so that
means transaction 2 is unblocked but it
detected that another transaction was
trying to update
same data at the same time so there is
an update conflict and the transaction
terminated and it raised an error okay
so that's the behavior when we are using
snapshot isolation and when to
transaction tries to update the same
piece of data at the same time there
will be an update conflict now let's see
how the behavior is going to change when
we use read committed snapshot isolation
so let's go ahead and prepare our
database to use read committed snapshot
isolation so first of all let's turn our
off snapshot isolation and let's turn on
read committed snapshot isolation and to
do that we use this alter database
statement and look at this when we try
to execute this statement it will be
blocked because we will have to close
all the other database connections so
let's close our transaction to window
here and that statement should complete
so let's fire up sequel server
management studio again
all right now we want this transaction
to use read committed isolation level
and the same is the case for transaction
two so let's change the isolation level
to read committed and let's go ahead and
set you know read committed as the
transaction isolation level for
transaction one so if we select the data
from this table at the moment items in
stock is eight so let's go ahead and
change that to 10 and let's select the
data just to make sure it's still 10
okay so it's doing the same thing
transaction 1 is trying to update it to
8 and transaction 2 is trying to update
it to 5 and let's see what's the
behavior is going to be so let's execute
part of our transaction 1 so transaction
1 is still in progress now let's go
ahead and execute transaction to notice
as expected transaction 2 is blocked now
if we commit the transaction 1 look at
that it completed successfully and upon
transaction 1 completion transaction 2
also completed without any update
conflict so transaction 1 updated it to
8 and transaction 2 updated it to a
value of 5 so if we select the data now
it's going to be fine because
transaction 2 is the one that has
completed last so with read committed
snapshot isolation we don't have update
conflicts but we do have update
conflicts with snapshot isolation
another difference is you know read
committed snapshot isolation is going to
work with existing applications without
requiring any change to the application
code whereas if you have to use snapshot
isolation application change may be
required to use with an existing
application because if you want to use
read committed snapshot isolation if
your application is already using read
committed isolation and if you want that
to use read committed snapshot isolation
then all the configuration that you have
to do
is in the database you will have to
simply turn on read committed snapshot
option and that's it now the lead
committed isolation level will be using
read committed snapshot isolation
meaning it will be using row versioning
to read the committed data whereas if
you have to use snapshot isolation then
you will have to change the transaction
isolation level every time to snapshot
so you have to do that change within
your application code if you have begin
the transaction you know within your
application so if you want to use
snapshot isolation with your existing
application then application change may
be required depending on how you have
coded that application but if you are
using read committed you know as the
default isolation level and if you want
to use read committed snapshot isolation
then all the changes in the DB okay we
don't have to do any code change as far
as the application is concerned now
another difference is read committed
snapshot isolation can be used with
distributed transactions whereas
snapshot isolation cannot be used with
distributed transactions another
important difference is read committed
snapshot isolation provides statement
level read consistency whereas snapshot
isolation provides transaction level
read consistency let's understand what
we mean by this difference with an
example so at the moment our databases
using read committed snapshot isolation
so let's change the value back to 10 and
let's just select that to make sure and
you know let's try and change it to the
value of 8 and that's what transaction 1
is going to do and transaction 2 all
it's going to do is issue to select
statements so select star from TBL
inventory where ID equals 1 and it's
going to do the same thing ok and then
we have our transaction 1 here
and so let's go ahead and execute part
of our transaction 1 so now if we select
the data look at that item since talk is
set to 8 and then let's go ahead and
execute our transaction 2 again I'm
going to execute only part of this
transaction 2 so we are executing only
the first select statement and look at
this what we get we get a value of 10
because that's the value that's
committed in the DB at the moment and
our isolation level at the moment is
freed committed since we have turned on
read committed snapshot option it's
using read committed snapshot isolation
meaning used row versioning to read
committed data ok so the last coming to
data is 10 all right now let's go ahead
and commit this transaction 1 now when
we come at this transaction now look at
this this transaction on the right has
got 2 select statements and both of them
are part of the same transaction
so the first select statement you know
gave us the result of 10 all right after
committing our transaction 1 what is
this going to do it's going to you know
make that change permanent in the debase
so the committed data will be 8 so now
when we issue our second select
statement look at what we get we get a
value of 8 okay so these 2 select
statements are part of the same
transaction but we are getting a
different value for each select
statement so here we are getting
statement level read consistency so just
before the Select statement began
whatever is the committed data that will
be retrieved okay with read committed
snapshot isolation level now let's see
how things are going to change when we
use some snapshot isolation now to use
snapshot isolation we will have to let's
go ahead and commit our transaction to
so let's change the database to use
snapshot isolation so let's turn off
read committed snapshot and again to do
that we will have to close
close all the other vendors so let's
close sequel server management studio
instance 2 and the statement execution
completed now let's turn on snapshot
isolation at the database level and
let's fire up sequence of management
studio again and let's paste our queries
their sample DB is the database now we
want to use snapshot isolation and let's
do the same thing for our transaction -
ok so the same idea let's execute part
of our transaction first of all let's
select what we have in the inventory
it's 8 let's change it to a value of 10
select that and let's try and change it
back to a value of 8 so let's execute
part of our transaction 1 and let's
execute part of our transaction - so the
last committed data is 10 so that is
what we are going to get and let's
commit our transaction and let's see
what we get now look at that we are
still getting a value of 10 okay and
let's commit this transaction - and now
let's go ahead and issue the same select
statement so TBL inventory we're ID
equals 1 and let's see now what we get
look at that now we get a value of 8 ok
so if you look at you know the snapshot
isolation it's giving us transaction
level read consistency so these two
select statements are part of the same
transaction so whatever was the
committed value in the database before
this transaction begin that's what you
will get you know throughout that
transaction whereas with read commit a
snapshot isolation level we get
statement level
read consistency okay so that's the
fourth difference between these two
isolation levels so here we have an
example that shows statement level lead
consistency that we get with read
committed snapshot isolation level and
here a transaction level read
consistency that we get with snapshot
isolation thank you for listening and
have a great day