Read Committed Isolation Level


SQL Server 2000 supports four different isolation levels: read uncommitted (or nolock), read committed, repeatable read, and serializable.  SQL Server 2005 adds two new isolation levels: read committed snapshot and snapshot.  These isolation levels determine what locks SQL Server takes when accessing data and, therefore, by extension they determine the level of concurrency and consistency that statements and transactions experience.  All of these isolation levels are described in Books Online.

In this post, I’m going to take a closer look at the default isolation level of read committed.  When SQL Server executes a statement at the read committed isolation level, it acquires short lived share locks on a row by row basis.  The duration of these share locks is just long enough to read and process each row; the server generally releases each lock before proceeding to the next row.  Thus, if you run a simple select statement under read committed and check for locks (e.g., with sys.dm_tran_locks), you will typically see at most a single row lock at a time.  The sole purpose of these locks is to ensure that the statement only reads and returns committed data.  The locks work because updates always acquire an exclusive lock which blocks any readers trying to acquire a share lock.

Now, let’s suppose that we scan an entire table at read committed isolation level.  Since the scan locks only one row at a time, there is nothing to prevent a concurrent update from moving a row before or after our scan reaches it.  The following graphic illustrates this point:

Let’s try an experiment to see this effect in action.  We’ll need two server sessions for this experiment.  First, create a simple table with three rows:

create table t (a int primary key, b int)
insert t values (1, 1)
insert t values (2, 2)
insert t values (3, 3)

Next, in session 1 lock the second row:

begin tran
update t set b = 12 where a = 2

Now, in session 2 run a simple scan of the table:

select * from t

This scan will read the first row and then block waiting for session 1 to release the lock it holds on the second row.  While the scan is blocked, in session 1 we can swap the first and third rows and then commit the transaction and release the exclusive lock blocking session 2:

update t set a = 4 where a = 1
update t set a = 0 where a = 3
select * from t
commit tran

Here are the new contents of the table following these updates:

a           b
———– ———–
0           3
2           2
4           1

Finally, here is the result of the scan from session 2:

a           b
———– ———–
1           1
2           2
4           1

Notice that in this output the first row was scanned prior to the updates while the third row was scanned following the updates.  In fact, these two rows are really the same row from before and after the update.  Moreover, the original third row that had the value (3, 3) is not output at all.  (We could claim that changing the primary key effectively deleted one row and created a new row, but we could also achieve the same effect on a non-clustered index.)

Finally, try repeating this experiment, but add a unique column to the table:

create table t (a int primary key, b int, c int unique)
insert t values (1, 1, 1)
insert t values (2, 2, 2)
insert t values (3, 3, 3)

You’ll get the same result, but you’ll see “duplicates” in the unique column.

If the above results are not acceptable, you can either enable read committed snapshot for your database or you can run at a higher isolation level (albeit with somewhat lower concurrency).

CLARIFICATION 8/26/2008: The above example works as I originally described if it is executed in tempdb.  However, the SELECT statement in session 2 may not block as described if the example is executed in other databases due to an optimization where SQL Server avoids acquiring read committed locks when it knows that no data has changed on a page.  If you encounter this problem, either run this example in tempdb or change the UPDATE statement in session 1 so that it actually changes the value of column b.  For example, try “update t set b = 12 where a = 2”.

UPDATE 2/17/2011:  I changed the example from “update t set b = 2 where a = 2” to “update t set b = 12 where a = 2” to avoid the issue described in the above clarification.

Comments (17)

  1. arun.philip says:

    Wow, a nice and juicy technical post after a big gap! Welcome back, Craig

  2. Anonymous says:

    Last week I looked at how concurrent updates may cause a scan running at read committed isolation level

  3. Anonymous says:

    Please read the following posts by Craig Freedman, member of the SQL Server query execution team: [link]

  4. caoyexun says:

    Change a little,when we create a unique index on table t2 column b,a blocking operator between the scan

    and update of table t1 becomes tale spool(Eager Spool) operator ,but not sort operator.

    The following query plan:

                            StmtText                    LogicalOp
    

    —————————————————————————–      ——————–

    |–Clustered Index Update(OBJECT:([t1].[t1a]), SET:([t1].[a] = [t1].[a]))   Update

      |–Table Spool      Eager Spool

           |–Top(ROWCOUNT est 0)                           Top

                |–Nested Loops(Left Semi Join, WHERE:([t2].[a]=[t1].[b]))      Left Semi Join

                     |–Clustered Index Scan(OBJECT:([t1].[t1a]))      Clustered Index Scan

                     |–Table Scan(OBJECT:([t2]))     Table Scan


    I come from China and my English is not good.

    I hope you can understand what I say.

    What you wrote in your blog is very helpful to me.

    Thanks a lot.

  5. caoyexun says:

    Sorry,i make a mistake the comment above should be posted here

    http://blogs.msdn.com/craigfr/archive/2007/05/22/read-committed-and-updates.aspx

  6. Anonymous says:

    In a prior post , I introduced the notion that update plans consist of two parts: a read cursor that

  7. Anonymous says:

    In a prior post , I introduced the notion that update plans consist of two parts: a read cursor that

  8. coleydog says:

    Craig, I know this article has been around for a while however I have found that if a transaction is started with isolation level of read committed then shared locks remain in place until a transaction commit or rollback. Means that session 2 transaction will fail until session 1 read(s) within that transaction complete(s) (commit) or fail(s) (rollback/timeout/deadlock).

  9. I’m not entirely sure I understand your comment.  At the read committed isolation level, share locks are only held while processing a row (except in some cases where they can be held until the end of the statement as I described in this and other posts http://blogs.msdn.com/craigfr/archive/2007/05/22/read-committed-and-updates.aspx).  However, in my above example, I deliberately acquire an exclusive lock in session 1 (which will be held until the end of the transaction) so as to block session 2 and allow session 1 to modify the table while session 2 is in the middle of its scan.

  10. jay says:

    hey craig,

    it's really helpful material, but i found something strange while practicing this example codes. In session 1 as you said to write following commands :

    begin tran

    update t set b = 2 where a = 2

    and then in session 2 run a simple scan of the table:

    select * from t

    it supposed to wait for transactions in session 1 to get committed, but it is showing me the result set.

    yes, if i use

    begin tran

    update t set b = 23 where a = 2

    then its fine.

    so can you explain that diffrent behavior of sql server.

  11. Craig Freedman says:

    I believe you are running into the issue that I noted in the clarification at the end of the post:

    CLARIFICATION 8/26/2008: The above example works as I originally described if it is executed in tempdb.  However, the SELECT statement in session 2 may not block as described if the example is executed in other databases due to an optimization where SQL Server avoids acquiring read committed locks when it knows that no data has changed on a page.  If you encounter this problem, either run this example in tempdb or change the UPDATE statement in session 1 so that it actually changes the value of column b.  For example, try "update t set b = 12 where a = 2".

    Perhaps I should just change the original example to avoid any further confusion …  🙂

    Craig

  12. jay says:

    yes craig you are right, it works same as your blog for tempdb and i got answer for my confusion.

    Thanks lot for reply.

  13. Vicky Koul says:

    I reckon for this behaviours it necessary to have  READ_COMMITTED_SNAPSHOT  set to OFF (default setting )…

    If READ_COMMITTED_SNAPSHOT is set to ON the read will get a snapshot of the data when the transaction in session 2 begin ..

  14. Craig Freedman says:

    That's correct.  Note the last sentence of the post:  "If the above results are not acceptable, you can either enable read committed snapshot for your database or you can run at a higher isolation level (albeit with somewhat lower concurrency)."

    Craig

  15. Hi Craig, good post and whilst I know this was written over 5 years ago, since it has very recently been featured in a newsletter and I wanted to point out that the part that states "SQL Server 2005 adds two new isolation levels: read committed snapshot and snapshot" is incorrect.

    SQL 2005 and above provide only one new isolation level AND an optimistic implementation of READ COMMITTED. SNAPSHOT isolation *is* a new isolation level and READ COMMITTED SNAPSHOT is actually the same isolation level as READ COMMITTED but is the optimistic implementation of it (as opposed to the default pessimistic implementation). I am sure by now you will be aware of this inaccuracy but wanted to leave the comment for new people coming to this post.

    Regards,

    Mark.

  16. kadhambari says:

    thanx a lot sir.. nice understood..

  17. Z.G. says:

    When I ran the test, the results were as follows:

    Session 1 results:

    a b

    1 1

    2 12

    4 1

    Session 2 results:

    a b

    0 3

    2 12

    4 1

    I think after the sql for the modification had been updated, it was forgetten to update the results accordingly.

    Anyway, good post. Thank you very much.