Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

SNAPSHOT

SNAPSHOT Isolation in SQL Server

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

 

 During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.

 

 In SNAPSHOT row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

 

So the plus side you’re not blocking anyone else from modifying the data whilst you run your transaction but…. You’re using extra resources on the SQL Server to hold multiple versions of your changes.

 

To use the snapshot isolation level you need to enable it on the database by running the following command

 

Syntax:

 

ALTER DATABASE DatabseName

SET ALLOW_SNAPSHOT_ISOLATION ON

 

 

Example:

 

ALTER DATABASE LocalTest 

SET ALLOW_SNAPSHOT_ISOLATION ON

 

--Query1  

BEGIN TRAN 

SELECT * FROM Employee 

WAITFOR DELAY '00:00:10' 

SELECT * FROM Employee 

ROLLBACK

 

--Query2

INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)

 

VALUES ('Anushka', 'England','English','London',5) 

 

Result:


READ UNCOMMITTED

Read Uncommitted Isolation in SQL Server

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

        1.  Does not issue shared locks to prevent other transactions from modifying data read by the current transaction.
  2.  Also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set called as dirty reads.

 

This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

 

Protecting transactions from dirty reads:

 

a. The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
b.  The SNAPSHOT isolation level. 


Example:

---Query1
BEGIN TRAN 
UPDATE Employee SET CreatedDate = getdate()
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:05' 
ROLLBACK  
 
---Query 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Employee 

 

 Result:

Actual data after transaction Completion by Query2:



Retrieved data by query 2 before Query 1 transaction completes (Dirty Read):


Employee table updated all records with CreatedDate,Actually query 1 will roll-back all changes after wait time completion. read uncommitted isolation level  allows user to read uncommitted (wrong) data updated by query 2


 

READ COMMITTED

Read committed Isolation in SQL Server

This is the default isolation level and means selects will only return committed data. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in no repeatable reads or phantom data.

 

Example:

---Query1

BEGIN TRAN 

UPDATE Employee SET CreatedDate = getdate()

--Simulate having some intensive processing here with a wait

WAITFOR DELAY '00:00:05' 

ROLLBACK   

 

---Query 2  

---Default Isolation level is Read committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM Employee

 Result:

query 2 display only committed data ,only committed data allowed to read



 If you want to check what isolation level you are running under you can run “DBCC user options”. Remember isolation levels are Connection/Transaction specific so different queries on the same database are often run under different isolation levels.