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

REPEATABLE READ

Repeatable read Isolation in SQL Server

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

 

Example:

 
--Query1
 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRAN 
SELECT * FROM Employee 
WAITFOR DELAY '00:00:05' 
SELECT * FROM Employee 
ROLLBACK
 
--Query2
Update Employee set UpdatedDate=getdate()

 

Result:

Run query 1 and then query 2, from query 1 notice that  both select statement returns same data even though you ran a query 2 to modify the data before the second select run. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.


 


After Transaction completes and update query executed, Select statement returns modified record:

 


 

 

If you rerun the above Queries but change Query1 to Read Committed you will notice the two selects return different data and that Query2 does not wait for Query1 to finish.

 

For above same use case Read returns:


 

One last thing to know about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted; it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.

 

Phantom read with repeatable read:

 

--Query1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ   
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) 

 


 

 

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