Snapshot isolation level in sql server

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

Channel: kudvenkat

Views: 95,700

Rating: 4.9622641 out of 5

Keywords: snapshot isolation example, snapshot isolation level example, sql server snapshot isolation level, update conflict snapshot isolation level, snapshot isolation in dbms

Id: 9NVu17LjPSA

Length: 9min 15sec (555 seconds)

Published: Wed Aug 19 2015