Snapshot isolation level in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 75 of sequel server
tutorial in this video we'll discuss
snapshot isolation level in sequel
server as we can see from this table
just like serializable isolation level
snapshot isolation level also does not
have any concurrency side effects so the
obvious next question that comes to our
mind is well then watch the difference
between serializable and snapshot
isolation levels the difference is
serializable isolation is implemented by
acquiring locks which means the
resources are locked for the duration of
the current transaction this isolation
level does not have any concurrency side
effects but at the cost of significant
reduction in concurrency on the other
hand snapshot isolation doesn't acquire
locks it maintains versioning in MTB
will discuss versioning in detail in a
later video session for now understand
that snapshot isolation doesn't acquire
locks
since snapshot isolation does not lock
resources it can significantly increase
the number of concurrent transactions
while providing the same level of data
consistency as serializable isolation
does let's understand this with an
example I have two instances of sequence
server management studio running here we
have this table TBL inventory in the
sample DB database that keeps track of
how many iPhones we have in stock and if
you look at this transaction 1 we have
set the transaction isolation level of
this to serializable and all we are
doing here is updating items in stock to
5 and if you look at transaction 2 again
where we have set the transaction
isolation level to serializable and in
this case we are reading the same data
row since we are using serializable
isolation level here you know this
transaction 1 is going to lock the
resource while this transaction is in
progress
that means transaction 2 will not be
able to access their data let's look at
that in action so let's go ahead and
execute part of this transaction so we
didn't commit or roll this transaction
back yet so this transaction is in
progress now and
now when we execute transaction to look
at that the transaction is blocked right
because why transaction one has locked
the resource that transaction 2 requires
now look at what's going to happen when
I come at this transaction at that point
transaction 2 is allowed to move forward
and it retrieved you know the committed
data from the database so let's go ahead
and commit this transaction and now
let's see how things are going to change
when we use snapshot isolation to use
snapshot isolation we have to enable
that first at the database level and to
enable snap short isolation at the
database level we have to use alter
database statement and the database that
we want to alter is sample DB because
that's the database way we have our TBL
inventory table so the name of the DB is
sample DB and we want to set allow
underscore snapshot underscore isolation
on so let's execute that statement so
this is going to prepare the database
for snapshot isolation and the next
thing that we want to do is set the
transaction isolation level to snapshot
all right
now let's change this value back to 10
and let's select the data from the table
to make sure that they still have that
value 10 now let's go ahead and execute
transaction 1 let's try to update it to
a value of 5 now transaction 1 is still
in progress because we didn't commit or
roll it back now if we select data from
this table obviously we will look at a
value of 5 you know that data is not
committed yet and when we execute
transaction to look at what we are going
to get back now the first thing to
notice is the transaction is not blocked
while transaction 1 is writing to table
TBL inventory transaction 2 is not
blocked it are loved
it is allowed to continue and look at
the data it has read it has read a value
that was in the table before transaction
1 started ok so that's what snapshot
isolation does it use it uses versioning
so when this transaction started at that
point whatever you know data it had in
the table it made a copy of that in the
temp DB database and that's what this
transaction 2 is allowed to read and
that's what we see here value 10 ok so
when we come at this transaction you
know the value 5 will be permanently
stored in the DB but the point here is
that snapshot isolation does not use
locks instead it uses versioning ok and
that's why the reads are able to
continue forward without being blocked
while another transaction is updating
the same data so just now we have looked
at an example of reading the data using
snapshot isolation now let's look at an
example of using you know basically
modifying data using snapshot isolation
so here let's go ahead and update the
value back to 10 and let's select that
to make sure we still have a value of 10
and the DB
and you know here we are updating the
value to five and instead of select here
let's use update statement
so update TBL inventory set items in
stock equals let's set it to eight where
ID equals one alright so in both the
cases we are trying to change it to a
different value here it's trying to
update it to a five I mean to a value of
five is trying to update it to a value
of eight let's see what's going to
happen so let's execute part of this
transaction and now if we select what we
have in the DB you know it's five and
let's not committed yet the transaction
is still in progress
now let's go ahead and execute this
transaction now notice this now the data
is locked you know we are not able to
move forward that's because you know
both the transactions are trying to
update the same piece of data so when we
use snapshot isolation and when we try
to update data that another transaction
is updating the transaction will be
blocked and how long will it be blocked
it will be blocked until transaction one
completes and when transaction one
completes what's going to happen well
let's look at that in action so I'm
going to commit this transaction so
command completed successfully so we
committed the transaction and look at
what happened to transaction two it
failed with an error message so why did
we get this error message that's because
imagine what could have happened if
transaction two was allowed to continue
while transaction one was in progress
when transaction if transaction two was
loud it would have updated items in
stock to eight and after that
transaction one would have changed that
value weight to a value of five so it is
overwriting that that means we have lost
an update so to prevent that concurrency
side effect of lost update this
transaction is aborted
while another transaction sub you know
updating the same data and the
transaction eventually fails with an
error and if you look at this error this
error makes you know perfect sense so
let's paste it in the notepad let's copy
that from here and notice what the error
message says snapshot isolation
transaction aborted due to update
conflict you cannot use apps short
isolation to access table TBL inventory
directly or indirectly in database
sample db2 update delete or insert the
row that has been modified or deleted by
another transaction retry the
transaction or change the isolation
level for the update or delete statement
now if you want to complete the work
that this transaction is doing then you
will have to rerun this transaction and
look at this when I rerun this
transaction it's going to work without
any issues so if we select the data back
from the table we are going to get a
value of 8 thank you for listening and
have a great day