Isolation levels in SQL Server … part II

In previous post I explained basics of ISOLATION levels in general. This post is a sort of demo of isolation levels in SQL Server. In SQL Server, Isolation property can be configured using “SET ISOLATION” command. This command can be used to set isolation at any given transaction.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
-- Check user settings
DBCC USEROPTIONS
GO 

For this post, I will be setting up isolation level to Read Uncommitted. As I explained in previous post, this isolation level is of the lowest in group. This will give users highest concurrent access as this isolation level will cause least amount of locks on resources. And users will even see uncommitted transactional data from another transaction.

First step for this test is to create a transaction to update some field in a table, I will be using my TestDB for this demo which can be created using following script,

-- Script to copy current data from AdventureWorks2008R2
-- to some test table in test db :)
SELECT p.BusinessEntityID,p.FirstName,p.LastName,a.AddressLine1,a.City,sp.Name [State/Province],
cr.Name [Country],a.PostalCode into TestDB.DBO.TableFoo
FROM AdventureWorks2008R2.Person.Person p JOIN AdventureWorks2008R2.Person.Address a
ON p.BusinessEntityID=a.AddressID
JOIN AdventureWorks2008R2.Person.StateProvince sp ON a.StateProvinceID=sp.StateProvinceID
JOIN AdventureWorks2008R2.Person.CountryRegion cr ON sp.CountryRegionCode=cr.CountryRegionCode

--  Begin transaction
BEGIN TRANSACTION
UPDATE TableFoo
SET FirstName='UnCommited'
WHERE BusinessEntityID='19230'

-- I didn't committed transaction yet
-- COMMIT TRANSACTION

Then I opened up another connection to database using “New Query” in SSMS. Where I setup isolation level to “Read Uncommitted” which will read row even if it is uncommitted, to simulate “dirty read” situation.

-- Setup isolation level for this transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION;
GO
-- this will show me uncommited data
SELECT * FROM TableFoo WHERE City='Braunschweig'
GO
COMMIT TRANSACTION;
GO

And this will return me a data  which is still not committed, because I have not yet executed “Commit Transaction” in my first transaction. But if I run above query (by opening another connection to database in SSMS) with “Read Committed” isolation level then it will not show me any data instead it will wait for release of lock for that table !!!

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION;
GO
-- SQL Server will just wait for lock 
-- to get released for this table
SELECT * FROM TableFoo WHERE City='Braunschweig'
GO
COMMIT TRANSACTION;
GO

Now, if I commit my transaction using “Commit Transaction” in my first SSMS query tab, then this last query will return all data with newly committed values. Or if I rollback this transaction using “Rollback Transaction” then I will see my original value !!

As you can see, by changing isolation level for a transaction I can access uncommitted data or other way, I can restrict user to read only committed data and not anyone change that while someone is actually selecting that data !! But it all depends upon business requirement.

That’s it for now.

It’s Just A Thought …