We want to maintain ACID properties, meaning we want data consistency. there are different ways to implement that, previously in this series we talked about how to do that using locking, in optimistic Concurrency we use row-versioning, meaning we keep older versions of the same rows before the last update and let the readers read it ( either in a transaction or in a statement ) so no S locks on the table but we still need to keep X locks on data modifications( we could not get rid of that) so what happened here? we increased concurrency. how? we don’t have S locks( we still have Sch-S locks but they are compatible with everything so no problem) so readers don’t need to wait for anybody, and we only acquired X locks( and other data modification locks Like U). so now you can access data however you want and maintain data consistency. how? through keeping the older versions of the same row stored. what is the downsides? you still have writer-writer conflict. now readers won’t block writes since there is no S locks on the resources but X lock are still acquired what is the other downside? you need a high-speed and huge storage since you need to store every version of the row point to the last consistent one and access it, the cleanup does not happen until the end of the transaction. what is the upside? you don’t READ UNCOMMITTED to increase Concurrency and you still maintain ACID properties where do you keep the row versions? in the row-store where is the row-store? depends the default is tempdb but if you enable accelerated recovery it will be in the database itself what do you mean by a snapshot? a valid set of rows that was there before the last statement or transaction what kind of isolation levels do you have in this mode? 2 RCSI ( READ COMMITTED Snapshot isolation)= READ COMMITTED Data consistency but instead of locking it uses row-versioning = no dirty reads( reading while the data is being modified) since you can’t access uncommitted data only the last committed viable data (the last row-version) but it does not prevent phantom ( when 2 transactions has tries to read the same data twice and in each result it gets a different set of rows) or non-repeatable reads(when the same transaction queries the same set of rows twice and gets different results ) now how does it work? on a statement level so if you had BEGIN TRAN with a SELECT,INSERT,SELECT, and a COMMIT each one will have a different row-version to access. how many versions do you keep? as much as the statements, 2 deletes two statements what about the other isolation level? snapshot isolation what is the big difference here? this keeps the consistency at the transaction level so instead of a different version after each select you get it at each transaction So if you had BEGIN SELECT INSERT SELECT COMMIT one version

What Is The Mechanism

We will create a database that has an ID increasing from one as a primary key and a clustered index in the table creation. Add columns Col1 and Col2 as VARCHAR(50). The values in Col1 should be 'C1' followed by the ID number (e.g., when ID = 1, Col1 = 'C11'), and the values in Col2 should be 'C2' followed by the ID number (e.g., when ID = 2, Col2 = 'C22'). Name the table 'MyTable' and drop it if it exists. we will create on ContosoRetailDW.

USE ContosoRetailDW;
GO
IF OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL
DROP TABLE dbo.MyTable;
GO
CREATE TABLE dbo.MyTable (
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Col1 VARCHAR(50),
    Col2 VARCHAR(50)
);
GO
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
    INSERT INTO dbo.MyTable (Col1, Col2)
    VALUES (CONCAT('C1', @i), CONCAT('C2', @i));
    SET @i = @i + 1;
END;
GO

now let’s enable RCSI on this database and check for it

USE master;
GO
ALTER DATABASE ContosoRetailDW
SET READ_COMMITTED_SNAPSHOT ON;
GO
SELECT name, is_read_committed_snapshot_on 
FROM sys.databases
WHERE name = 'ContosoRetailDW';
GO

then after that we will have 3 transactions:

• the first one will be a select on the original
• then we’ll have another that updates and selects but never commits
• then in the third one we will update the same row and get blocked
• then we will rollback the second
• then we will rollback the third
• in each one will query two DMVs: sys.dm_tran_version_store and sys.dm_tran_lock through Kalen Delany’s DBLocks view

this new dmv shows:

• each row version’s XSN = tranascation_sequence_number = meaning the transaction that created this row version
• database_id
• status = whether we had a page split or not while storing the version in the version store if yes then 1 if no then 0
• version_sequence_number= the id of each version in the transaction

and other stuff, dmitri says this DMV is highly inefficient and rarely useful so we will use it to demonstrate the behavior and then we will stop using it but XSN here is what each transaction use to access the last row-version that it is going to read meaning when a query requests the last version it has to go through each XSN until the last useful one to take row that it needs meaning having row-versions consumes CPU, which add overhead but increase concurrency and let's do the stuff: the first transaction:

BEGIN TRAN 
SELECT Col1 ,Col2
FROM MyTable
WHERE ID=5 ;
select*
FROM sys.dm_tran_version_store;
SELECT*
FROM DBlocks;
--- don't commit now or commit it does not make a differnce here now
COMMIT
 

nothing shows up here since we did not create any new rowversions   then the second transaction that updates and selects but never commits

begin tran 
UPDATE MyTable
SET Col1 ='new_stuff'
WHERE ID=5;
select*
FROM sys.dm_tran_version_store;

select col1
from mytable
where ID = 5 ;
select*
FROM sys.dm_tran_version_store;
select* 
from DBlocks;
--- don't commit yet 

it did take X locks since writers block writers and it did create a new row-version after the update but not for the select the first one is the XSN then in another transaction try to update the same row

begin tran 
UPDATE MyTable 
SET Col2 = 'C2newstuff'
WHERE ID ='5';
select* 
from DBlocks;
select*
FROM sys.dm_tran_version_store;

and you will get blocked query DBLocks in another transaction Now ROLLBACK the Second one and this will pop up in the third transaction now we have two XSN that created two row-versions each representing a different committed row that is stored in tempdb now ROLLBACK the last one and re-run the first and if you committed the first when we started you will see this nothing the cleanup task deleted all the row-version if you did not commit ( run the whole exercise again with no commit ) you will have the rows stored since the first transactions still need the original version now commit and you will see it cleans up afterwards each number of XSN increases monotonically 93,94,95,96 etc. now we described RCSI and how to set it on now let’s turn it of and enable just snapshot isolation

SNAPSHOT_ISOLATION

first let’s turn off RCSI

ALTER DATABASE ContosoRetailDW
SET READ_COMMITTED_SNAPSHOT OFF;
GO

then let’s turn on SNAPSHOT_ISOLATION on database and session level

USE master;
GO

ALTER DATABASE ContosoRetailDW
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = 'ContosoRetailDW';
GO

and this will pop up and then you need to enable it for each session like

USE ContosoRetailDW;
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

so what is the difference here here you get the row version before the last modifying transaction started instead of the last modifying statement, this way you prevent non-repeatable reads, phantom, and dirty reads instead of just dirty reads in RCSI. Another thing, the transaction starts at the first time the transaction uses the data instead of the BEGIN TRAN thing. let’s try some:

• first we will update a row but never commit
• then in another transaction we will select
• then we will commit the first
• and we will select from the first again
• then will commit and select again and see what kind of results we would have

so let’s start first we will update in the first session without committing

GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
begin tran 
UPDATE MyTable 
SET Col2 = 'C2newstuff'
WHERE ID ='5';
--------------

then in another session

GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRAN 
SELECT Col2 
FROM MyTable
WHERE ID =5;

now you will have the original row since the last tran has not been committed yet but we have our row version so we get it then let’s commit the first one

---- first tran 
COMMIT TRAN 

And now in the second session

SELECT Col2 
FROM MyTable
WHERE ID =5;

and you will get now this is the difference between SNAPSHOT ISOLATION AND RCSI You will get transaction level consistency like serializable with higher concurrency then let’s COMMIT this session and and select the same row again

COMMIT TRAN 

SELECT Col2 
FROM MyTable
WHERE ID =5;

and

Only in Snapshot Isolation: UPDATE conflicts ( error 3960) = lost updates

so we have two transactions both trying to update the same row, in pessimistic SQL server chooses the earlier one and let the other one wait until the locks are released, here we are trying to maintain a stable snapshot based on the last transaction, doing that makes everybody access it, helps concurrency, but at the same time decreases independency, in pessimistic each transaction lives in its own world, here transaction shares the same reads but compete in updates, so if we had two transaction reading the same data and each updating the same row before commitment the last one to update will reach to the original row and update it, and the first one will lose its update meaning, we will have no way of knowing the update the first transaction had on our row. in order to maintain consistency SQL server will just rollback the last transaction to commit. This ROLLBACK = is called a lost update let’s try some first we have our SELECT in the first Transaction

use ContosoRetailDW
go 
SET TRANSACTION ISOLATION LEVEL SNAPSHOT 
go
BEGIN TRAN 
SELECT Col2 
FROM MyTable
WHERE ID =5 ; 

then in another transaction, we will have an UPDATE that does not COMMIT at first

BEGIN TRAN 
UPDATE MyTable 
SET Col2='C2newstuff'
WHERE ID = 5 ; 

then in the first transaction, we will try to UPDATE

UPDATE MyTable
SET Col2='THE_NEW'
WHERE ID = 5 

and we will get blocked, now let’s commit the second transaction

------ the second tran first update 
COMMIT TRAN

and this will pop up in the first transaction

/*Msg 3960, Level 16, State 2, Line 10
Snapshot isolation transaction aborted due to update conflict. 
You cannot use snapshot isolation to access table 'dbo.MyTable' directly or indirectly in
 database 'ContosoRetailDW' to 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/delete statement.
 */

 

What Is My Optimistic Isolation Level?

sys.databases has two columns on snapshot isolation levels( we have used them multiple times through our series):

• snapshot_isolation_state has 4 values since it is an online operation
• and is_read_committed_snapshot _on on or off not much since you need to close or rollback every connection and acquire an X lock on the database, ALTER DATABASE
• both are not mutually exclusive

if we need to set the isolation level of a database to RCSI you can only use ALTER DATABASE and you need to do that in each database no need for that in each session now this like we said before needs an X lock on the database, meaning you need to stop each connection on the database before applying it otherwise it will not execute how do you do that:

USE master;
GO
ALTER DATABASE ContosoRetailDW
SET READ_COMMITTED_SNAPSHOT ON;

we had another connections and we were blocked, now we can go out there and kill each session manually

KILL <blocking_session_id>;

that would leave half committed transactions and would roll some completely or instead, we could set to to offline or single user

-- Set the database to offline
ALTER DATABASE ContosoRetailDW
SET OFFLINE WITH ROLLBACK IMMEDIATE;

-- Set the database to online
ALTER DATABASE ContosoRetailDW
SET ONLINE;

or

-- Set the database to single user mode
ALTER DATABASE ContosoRetailDW
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Set the database to multi-user mode
ALTER DATABASE ContosoRetailDW
SET MULTI_USER;

or instead of blocking we could set it with no wait and would not execute

USE master;
GO
ALTER DATABASE ContosoRetailDW
SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT ;

and we will get this

then SNAPSHOT ISOLATION states

now first let’s set it off

USE master;
GO
-- Disable SNAPSHOT isolation for the ContosoRetailDW database
ALTER DATABASE ContosoRetailDW
SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO
-- Verify that both SNAPSHOT isolation and RCSI are disabled
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = 'ContosoRetailDW';
GO

now this is the first state for our snapshot_isolation_state_desc it is exactly what it sounds like now it is in pessimistic concurrency mode now this will not get blocked if we had another connections, only updating connections will block us now lets start an updating one and try to set SNAPSHOT ISOLATION on first

use ContosoRetailDW
go 
begin tran 
UPDATE MyTable
SET Col2='nC2'
WHERE ID = 5 

then let’s set it on

USE master;
GO
-- Disable SNAPSHOT isolation for the ContosoRetailDW database
ALTER DATABASE ContosoRetailDW
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

you will be blocked now in a third session

use master
go
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = 'ContosoRetailDW';

and this will pop up IN_TRANSITION_TO_ON: old ones will be using pessimistic and you can’t update in optimistic concurrency until the last updating transaction commits then then it will start generating now rollback the last pessimistic transaction

rollback 

and now now anything will use row-versioning now let’s get back to the same transaction we rolled back before, execute it again then never commit it

use ContosoRetailDW
go 
begin tran 
UPDATE MyTable
SET Col2='nC2'
WHERE ID = 5 

then set snapshot isolation to off

USE master;
GO
-- Disable SNAPSHOT isolation for the ContosoRetailDW database
ALTER DATABASE ContosoRetailDW
SET ALLOW_SNAPSHOT_ISOLATION off;
GO

then in another session let’s see the state

use master
go
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = 'ContosoRetailDW';

and this will pop up then any updates or selects that have not finished will still use row-versioning and the new one will keep on using it until everything is done now rollback

rollback

and it is back to OFF again

Exploring sys.dm_tran_current_transaction With sys.dm_tran_transactions_snapshot and sys.dm_tran_active_snapshot_database_transactios another row-versioning DMVs

they share a lot of columns so we will describe all of them at once:

• transaction_id: server wise unique shows the current transactions’s ID if you are running your transaction as an auto-committed one you will receive a different tran id each time you query it but if you are in a user defined one it will give you the one you are querying
• transaction_sequence_number=XSN: if you are in snapshot each transaction will get one of those we talked about it
• transaction_is_snapshot: is it ? yes equals 1
• first_snapshot_sequence_num: in the current snapshot this is the first transaction to use row versioning
• last_transaction_sequence_num: last XSN to get created
• first_useful_sequence_num: since the cleanup task cleans up tempdb this is the first one to be found in tempdb
• snapshot_id: is the snapshot id for the current transaction
• snapshot_sequence_num: is the sequence number of the transaction beneath this specific snapshot id
• commit_sequence_num: when this particular transaction finished non-commited=null
• max_version_chain_traversed: XSN traversed to reach the result we showed you=cpu overhead
• average_version_chain_traversed: this is the average for the same thing
• is_snapshot: 0 or 1 same as transaction_is_snapshot

let’s see them in action first, let’s select and query each dmv and see the behavior

use ContosoRetailDW;
set  transaction isolation level snapshot;
go
begin tran 
select*
from MyTable;
select*
from sys.dm_tran_current_transaction;
select*
from sys.dm_tran_version_store;
select*
from sys.dm_tran_version_store_space_usage;
select*
from sys.dm_tran_transactions_snapshot;
select*
from sys.dm_tran_active_snapshot_database_transactions;
commit tran

now since we did not start row-versioning yet most of the stuff was empty our transaction is the first and the last, transaction id is ours since it is used defined and it is a snapshot so 1 then version store was empty space usage was empty since we did not use any space yet same goes for transaction snapshot last same stuff here we see how many XSN it traversed before reaching for the last committed value then now in the same session let’s run the select but never commit then update in another session

use ContosoRetailDW;
set  transaction isolation level snapshot;
begin tran 
UPDATE MyTable 
SET Col1='NEC1'
WHERE ID = 5 ;
select*
from sys.dm_tran_current_transaction;
select*
from sys.dm_tran_version_store;
select*
from sys.dm_tran_version_store_space_usage;
select*
from sys.dm_tran_transactions_snapshot;
select*
from sys.dm_tran_active_snapshot_database_transactions;
--commit tran

Let’s look at the output so our tid then the new XSN for our transaction since we created a row-version then the first transaction is the previous transaction, and it is the first_useful one too our new session has the last transaction in this series we talked about this in earlier in this blog now we have 8 pages in tempdb and 64 space used snapshot is still empty I don’t know why but the last one for the currently active ones shows how many XSN and the elapsed time now let’s update some rows in a third session and select in a 4th

use ContosoRetailDW;
set  transaction isolation level snapshot;
begin tran 
update MyTable
set Col2 ='nw'
where id between 10 and 20 
commit tran 

and the select is the same as the first but in another session let’s look at the output now it started traversing these could be helpful in the management of tempdb now we are done with optimistic concurrency, see you in the next blog.  

---

Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners by Dmitri Korotkevitch Pro SQL Server Internals by Dmitri Korotkevitch SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Technique by Dmitri Korotkevitch SQL Server Concurrency by Kalen Delaney Microsoft SQL Server 2012 Internals by Kalen Delaney