Craig Freedman's SQL Server Blog

A discussion of query processing, query execution, and query plans in SQL Server.

Read Committed Isolation Level

Read Committed Isolation Level

Rate This
  • Comments 14

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.

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

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

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

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

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

  • 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).

  • 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.

  • 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.

  • 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

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

    Thanks lot for reply.

  • 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 ..

  • 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

  • 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.

  • thanx a lot sir.. nice understood..

Page 1 of 1 (14 items)
Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post