Skip to main content

SQL Server Isolation Levels with examples

Following are the different types of isolations available in SQL Server.
  • READ COMMITTED
  • READ UNCOMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • SNAPSHOT
Let us discuss about each isolation level in details.Before this, execute following script to create table and insert some data that we are going to use in examples for each isolation
    IF OBJECT_ID('Emp') is not null
    begin 
    DROP TABLE Emp
    end

    create table Emp(ID int,Name Varchar(50),Salary Int)

    insert into Emp(ID,Name,Salary)
    values( 1,'David',1000)

    insert into Emp(ID,Name,Salary)
    values( 2,'Steve',2000)

    insert into Emp(ID,Name,Salary)
    values( 3,'Chris',3000)
Test data
Note: Before executing each example in this article, reset the Emp table values by executing the above script.

Read Committed

In select query it will take only commited values of table. If any transaction is opened and incompleted on table in others sessions then select query will wait till no transactions are pending on same table.
Read Committed is the default transaction isolation level.

Read committed example 1:

Session 1

        begin tran
        update emp set Salary=999 where ID=1
        waitfor delay '00:00:15'
        commit

Session 2

        set transaction isolation level read committed
        select Salary from Emp where ID=1
Run both sessions side by side.
Output
999
In second session, it returns the result only after execution of complete transaction in first session because of the lock on Emp table. We have used wait command to delay 15 seconds after updating the Emp table in transaction.

Read committed example 2

Session1

        begin tran
        select * from Emp
        waitfor delay '00:00:15'
        commit

Session2

        set transaction isolation level read committed
        select * from Emp
Run both sessions side by side.
Output
1000
In session2, there won't be any delay in execution because in session1 Emp table is used under transaction but it is not used update or delete command hence Emp table is not locked.

Read committed example 3

Session 1

        begin tran
        select * from emp            
        waitfor delay '00:00:15'
        update emp set Salary=999 where ID=1
        commit

Session 2

        set transaction isolation level read committed
        select Salary from Emp where ID=1
Run both sessions side by side.
Output
1000
In session2, there won't be any delay in execution because when session2 is executed Emp table in session1 is not locked(used only select command, locking on Emp table occurs after wait delay command).

Read Uncommitted

If any table is updated(insert or update or delete) under a transaction and same transaction is not completed that is not committed or roll backed then uncommitted values will displaly(Dirty Read) in select query of "Read Uncommitted" isolation transaction sessions. There won't be any delay in select query execution because this transaction level does not wait for committed values on table.

Read uncommitted example 1

Session 1

        begin tran
        update emp set Salary=999 where ID=1
        waitfor delay '00:00:15'
        rollback

Session 2

        set transaction isolation level read uncommitted
        select Salary from Emp where ID=1
Run both sessions at a time one by one.
Output
999
Select query in Session2 executes after update Emp table in transaction and before transaction rolled back. Hence 999 is returned instead of 1000.
If you want to maintain Isolation level "Read Committed" but you want dirty read values for specific tables then usewith(nolock) in select query for same tables as shown below.
    set transaction isolation level read committed
    select * from Emp with(nolock)

Repeatable Read

select query data of table that is used under transaction of isolation level "Repeatable Read" can not be modified from any other sessions till transcation is completed.

Repeatable Read Example 1

Session 1

    set transaction isolation level repeatable read
    begin tran
    select * from emp where ID in(1,2)
    waitfor delay '00:00:15'
    select * from Emp where ID in (1,2)
    rollback

Session 2

    update emp set Salary=999 where ID=1
Run both sessions side by side.
Output
Update command in session 2 will wait till session 1 transaction is completed because emp table row with ID=1 has locked in session1 transaction.

Repeatable Read Example 2

Session 1

    set transaction isolation level repeatable read
    begin tran
    select * from emp
    waitfor delay '00:00:15'
    select * from Emp
    rollback

Session 2

    insert into Emp(ID,Name,Salary)
    values( 11,'Stewart',11000)
Run both sessions side by side.
Output
Result in Session 1.
Repeatable Read example for different count
session 2 will execute without any delay because it has insert query for new entry. This isolation level allows to insert new data but does not allow to modify data that is used in select query executed in transaction.
You can notice two results displayed in Session 1 have different number of row count(1 row extra in sectond result set).

Repeatable Read Example 3

Session 1

    set transaction isolation level repeatable read
    begin tran
    select * from emp where ID in(1,2)
    waitfor delay '00:00:15'
    select * from Emp where ID in (1,2)
    rollback

Session 2

    update emp set Salary=999 where ID=3
Run both sessions at a time one by one.
Output
session 2 will execute without any delay because row with ID=3 is not locked, that is only 2 records whose IDs are 1,2 are locked in Session 1.

Serializable

Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock. If table has index then it locks records based on index range used in WHERE clause(like where ID between 1 and 3). If table doesn't have index then it locks complete table.

Serializable Example 1

Assume table does not have index column.

Session 1

    set transaction isolation level serializable
    begin tran
    select * from emp
    waitfor delay '00:00:15'
    select * from Emp
    rollback

Session 2

    insert into Emp(ID,Name,Salary)
    values( 11,'Stewart',11000)
Run both sessions side by side.
Output
Result in Session 1.
Serializable Isolation example table with no index
Complete Emp table will be locked during the transaction in Session 1. Unlike "Repeatable Read", insert query in Session 2 will wait till session 1 execution is completed. Hence Phantom read is prevented and both queries in session 1 will display same number of rows.
To compare same scenario with "Repeatable Read" read Repeatable Read Example 2.

Serializable Example 2

Assume table has primary key on column "ID". In our example script, primary key is not added. Add primary key on column Emp.ID before executing below examples.

Session 1

    set transaction isolation level serializable
    begin tran
    select * from emp where ID between 1 and 3
    waitfor delay '00:00:15'
    select * from Emp where ID between 1 and 3
    rollback

Session 2

    insert into Emp(ID,Name,Salary)
    values( 11,'Stewart',11000)
Run both sessions side by side.
Output
Since Session 1 is filtering IDs between 1 and 3, only those records whose IDs range between 1 and 3 will be locked and these records can not be modified and no new records with ID range between 1 to 3 will be inserted. In this example, new record with ID=11 will be inserted in Session 2 without any delay.

Snapshot

Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold lock on table during the transaction so table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case of any data modification occurs in other sessions then existing transaction displays the old data from Tempdb.

Snapshot Example 1

Session 1

    set transaction isolation level snapshot
    begin tran
    select * from Emp
    waitfor delay '00:00:15'
    select * from Emp
    rollback

Session 2

    insert into Emp(ID,Name,Salary) values( 11,'Stewart',11000)
    update Emp set Salary=4444 where ID=4
    select * from Emp
Run both sessions side by side.
Output
Result in Session 1.
Snapshot isolation Session 1 result
Result in Session 2.
Snapshot isolation Session 2 result
Session 2 queries will be executed in parallel as transaction in session 1 won't lock the table Emp.

Labels:

Comments

Post a Comment

Popular posts from this blog

C# Generic class to parse value - "GenericConverter"

    public class GenericConverter     {         public static T Parse<T>(string sourceValue) where T : IConvertible         {             return (T)Convert.ChangeType(sourceValue, typeof(T));         }         public static T Parse<T>(string sourceValue, IFormatProvider provider) where T : IConvertible         {             return (T)Convert.ChangeType(sourceValue, typeof(T), provider);         }     }     public static class TConverter     {         public static T ChangeType<T>(object value)         {             return (T)ChangeType(typeof(T), value);         }         public static object ChangeType(Type t, object value)         {             TypeConverter tc = TypeDescriptor.GetConverter(t);             return tc.ConvertFrom(value);         }         public static void RegisterTypeConverter<T, TC>() where TC : TypeConverter         {             TypeDescriptor.AddAttributes(typeof(T), new TypeConverterAttribute(typeof(TC)));         }     } ----------------
Post a Comment
Read more

How to create a countdown timer in jquery

Create a countdown timer in jQuery First we need to include the jQuery library file to the HTML page to perform this task. To do that we need to understand that what exactly a jQuery library fie is ? JQuery library file is the library of JavaScript, which means this file contains the predefined functions of jQuery. We just need to call these functions to perform the task. jQuery functions reduces the lines of code and makes our task easy. As this jQuery library file contains the javascript functions so we need to call the function within <script> </script> tag. Now after including the file, we need to define a variable which will store that for how long you want the timer on the page(c=60) and now the time you set needs to be changed in hours , minutes and seconds using the code “ var hours = parseInt( time / 3600 ) % ;var minutes = parseInt( time / 60 ) % 60; var seconds = time % 60;” Now we need to put the condition if timer got finished (if (t
1 comment
Read more

Tip/Trick: Fix Common SEO Problems Using the URL Rewrite Extension

Search engine optimization (SEO) is important for any publically facing web-site.  A large % of traffic to sites now comes directly from search engines, and improving your site’s search relevancy will lead to more users visiting your site from search engine queries.  This can directly or indirectly increase the money you make through your site. This blog post covers how you can use the free Microsoft  URL Rewrite Extension  to fix a bunch of common SEO problems that your site might have.  It takes less than 15 minutes (and no code changes) to apply 4 simple  URL Rewrite  rules to your site, and in doing so cause search engines to drive more visitors and traffic to your site.  The techniques below work equally well with both ASP.NET Web Forms and ASP.NET MVC based sites.  They also works with all versions of ASP.NET (and even work with non-ASP.NET content). [In addition to blogging, I am also now using Twitter for quick updates and to share links. Follow me at:  twitter.com/scottg
Post a Comment
Read more