SQL Server : Row Versioning-based Isolation Levels - Read_Committed_Snapshot

--An user began a transaction and update few records in a table and won't commit that transaction.

--The user of this session can see the updated uncommited records of this table.

Begin Tran

Update StamTable Set asdsadsad = 999 where asdsadsad is not null

--------------------------------------------------------

--About users from other sessions:

Alter Database ItaiDWSource Set Read_Committed_Snapshot Off

Go

-- Other session won't get result from the table until the lock will be release or transaction will be commit or rollback.

-- The query will be Stuck!

Select * From StamTable

GO

-- also in those queries:

Select * From StamTable With (ReadCommitted) where asdsadsad is not null;

GO 

Select * From StamTable where asdsadsad is not null;

GO

-- but, in those queries, we will get the updated uncommited records, and we won't be Stuck.

Select * From StamTable With (NoLock) where asdsadsad is not null;

GO 

Select * From StamTable With (ReadUnCommitted) where asdsadsad is not null;

GO

-- NOTE: the risk is, that maybe the transaction will be rollback

Alter Database ItaiDWSource Set Read_Committed_Snapshot on

Go

-- NO query will be Stuck!

-- In those queries we will get the last updated version of the data (before the update query):

Select * From StamTable

GO

Select * From StamTable With (ReadCommitted) where asdsadsad is not null;

GO 

Select * From StamTable where asdsadsad is not null;

GO

-- but, in those queries, we will get the updated uncommited records:

Select * From StamTable With (NoLock) where asdsadsad is not null;

GO 

Select * From StamTable With (ReadUnCommitted) where asdsadsad is not null;

GO

-- the risk is, that maybe the transaction will be rollback

Read_Committed_Snapshot (RCSI) isolation level:
RCSI save a version to changed data in order to prevent from waits for uncommitted transactions.
RCSI goal is to reduce blocking and deadlocking issues caused by lock contention.

When using RCSI:

• Readers DO NOT block Writers.
• Writers DO NOT block Readers.
• Writers DO block Writers.

Advantages:

• SELECT statements do not lock data during a read operation.
• As written above, RCSI can reduce blocking and deadlocking issues caused by lock contention.
• --> Can improve performance.
• Read operations retrieve a consistent snapshot of the database and not uncommitted version (not like in READ UNCOMMITTED that retrieve a not-consistent snapshot).

Disadvantages:

• A transaction can be rolled back, and that the version of the data that was received is not true.
• Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent, read about it here: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2011/08/02/reads-involving-udfs-under-read-committed-snapshot-may-seem-inconsistent.aspx
• The versioned rows for RCSI are kept in tempdb, so there is affect on tempdb performance when using RCSI.

Read committed	Read uncommitted	RCSI
Yes	No	No	Locks while SELECT
No	Yes	No	Corrupted Data
Yes	No	No	The most updated data
Wait for commit\rollback	Get the current (and maybe the dirty) data in the table, before commit\rollback.	Get the last updated version of the data (before the first transaction)	In case of change in transaction, and in another transaction there is a Select on the updated data
Yes	Yes	Yes	Locks while UPDATE

-- Get the Read_Committed_Snapshot status:

Select name, is_read_committed_snapshot_on

From sys.databases Where database_id = DB_ID()

-- On = 1, Off = 0