通过

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

  • Anonymous
    April 25, 2007
    Wow, a nice and juicy technical post after a big gap! Welcome back, Craig

  • Anonymous
    May 02, 2007
    Last week I looked at how concurrent updates may cause a scan running at read committed isolation level

  • Anonymous
    May 15, 2007
    The comment has been removed

  • Anonymous
    May 24, 2007
    Change a little,when we create a unique index on table t2 column b,a blocking operator between the scanand 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 ScanI 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.

  • Anonymous
    May 24, 2007
    Sorry,i make a mistake the comment above should be posted herehttp://blogs.msdn.com/craigfr/archive/2007/05/22/read-committed-and-updates.aspx

  • Anonymous
    February 27, 2008
    In a prior post , I introduced the notion that update plans consist of two parts: a read cursor that

  • Anonymous
    February 27, 2008
    In a prior post , I introduced the notion that update plans consist of two parts: a read cursor that

  • Anonymous
    October 22, 2008
    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).

  • Anonymous
    October 24, 2008
    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.

  • Anonymous
    February 16, 2011
    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.

  • Anonymous
    February 17, 2011
    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

  • Anonymous
    February 21, 2011
    yes craig you are right, it works same as your blog for tempdb and i got answer for my confusion. Thanks lot for reply.

  • Anonymous
    November 24, 2011
    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 ..

  • Anonymous
    November 27, 2011
    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

  • Anonymous
    September 17, 2012
    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.

  • Anonymous
    June 03, 2014
    thanx a lot sir.. nice understood..

  • Anonymous
    May 11, 2015
    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.