Craig Freedman's SQL Server Blog

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

Repeatable Read Isolation Level

Repeatable Read Isolation Level

  • Comments 24

In my last two posts, I showed how queries running at read committed isolation level may generate unexpected results in the presence of concurrent updates.  Many but not all of these results can be avoided by running at repeatable read isolation level.  In this post, I'll explore how concurrent updates may affect queries running at repeatable read.

Unlike a read committed scan, a repeatable read scan retains locks on every row it touches until the end of the transaction.  Even rows that do not qualify for the query result remain locked.  These locks ensure that the rows touched by the query cannot be updated or deleted by a concurrent session until the current transaction completes (whether it is committed or rolled back).  These locks do not protect rows that have not yet been scanned from updates or deletes and do not prevent the insertion of new rows amid the rows that are already locked.  The following graphic illustrates this point:

Note that the capability to insert new "phantom" rows between locked rows that have already been scanned is the principle difference between the repeatable read and serializable isolation levels.  A serializable scan acquires a key range lock which prevents the insertion of any new rows anywhere within the range (as well as the update or deletion of any existing rows within the range).

In the remainder of this post, I'll give a couple of examples of how we can get unexpected results even while running queries at repeatable read isolation level.  These examples are similar to the ones from my previous two posts.

Row Movement

First, let's see how we can move a row and cause a repeatable read scan to miss it.  As with all of the other example in this series of posts, we'll need two sessions.  Begin by creating this simple table:

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 = 2 where a = 2

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

select * from t with (repeatableread)

This scan reads the first row then blocks waiting for session 1 to release the lock it holds on the second row.  While the scan is blocked, in session 1 let's move the third row to the beginning of the table before committing the transaction and releasing the exclusive lock blocking session 2:

update t set a = 0 where a = 3
commit tran

As we expect, session 2 completely misses the third row and returns just two rows:

a           b           c
----------- ----------- -----------
1           1           1
2           2           2

Note that if we change the experiment so that session 1 tries to touch the first row in the table, it will cause a deadlock with session 2 which holds a lock on this row.

Phantom Rows

Let's also take a look at how phantom rows can cause unexpected results.  This experiment is similar to the nested loops join experiment from my previous post.  Begin by creating two tables:

create table t1 (a1 int primary key, b1 int)
insert t1 values (1, 9)
insert t1 values (2, 9)

create table t2 (a2 int primary key, b2 int)

Now, in session 1 lock the second row of table t1:

begin tran
update t1 set a1 = 2 where a1 = 2

Next, in session 2 run the following outer join at repeatable read isolation level:

set transaction isolation level repeatable read
select * from t1 left outer join t2 on b1 = a2

The query plan for this join uses a nested loops join:

  |--Nested Loops(Left Outer Join, WHERE:([t1].[b1]=[t2].[a2]))
       |--Clustered Index Scan(OBJECT:([t1].[PK__t1]))
       |--Clustered Index Scan(OBJECT:([t2].[PK__t2]))

This plan scans the first row from t1, tries to join it with t2, finds there are no matching rows, and outputs a null extended row.  It then blocks waiting for session 1 to release the lock on the second row of t1.  Finally, in session 1, insert a new row into t2 and release the lock:

insert t2 values (9, 0)
commit tran

Here is the output from the outer join:

a1          b1          a2          b2
----------- ----------- ----------- -----------
1           9           NULL        NULL
2           9           9           0

Notice that we have both a null extended and a joined row for the same join key!

Summary

As I pointed out at the conclusion of my previous post, I want to emphasize that the above results are not incorrect but rather are a side effect of running at a reduced isolation level.  SQL Server guarantees that the committed data is consistent at all times.

CLARIFICATION 8/26/2008: The above examples work as I originally described if they are executed in tempdb.  However, the SELECT statements in session 2 may not block as described if the examples are 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 these examples in tempdb or change the UPDATE statements in session 1 so that they actually change the data in the updated row.  For instance, for the first example try "update t set b = 12 where a = 2".

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

  • Great post, very illustrative.

    You said that :

    "These locks do not protect rows that have not yet been scanned from updates or deletes and do not prevent the insertion of new rows amid the rows that are already locked."

    does this apply to the Serializable level as well? I mean if transaction 1 that runs in Serializable level hasn't yet scanned a row,can another transaction 2 modify that row, or insert a row in a range that has yet to be scanned by T1? Or does Serializable locks rows ahead? Thanks

  • A serializable scan acquires key-range locks as it goes.  Thus, while rows cannot be inserted into a region of the index that has already been scanned, rows can be inserted, updated, or deleted in the range that has not yet been scanned.

    You may find the following Books Online page helpful: http://msdn.microsoft.com/en-us/library/ms191272.aspx

    HTH

    Craig

  • The link provides some good explanation.However the section "Serializable Range Scan, Singleton Fetch, Delete, and Insert" describes that "when deleting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the delete operation" but not what happens in an Update operation.For example transaction 1 in Serializable does a "UPDATE mytable SET name="xx"  WHERE name BETWEEN 'A' AND 'C';" can a transaction 2 insert a row that falls between A and C or will it block?In other words, the SELECT part of the Update modification will take range locks ?

  • The statement that you reference from Books Online is slightly confusing.  Basically, singleton fetches (i.e., equality lookups on unique indexes) need only take key locks while all other seeks and scans take key-range locks.  This is true for select, delete, and update statements.  The update statement in your question above uses a range scan due to the BETWEEN clause and, thus, uses key-range locks with the result that concurrent inserts in the range between 'A' and 'C' will block.  You may find the following post helpful: blogs.msdn.com/.../range-locks.aspx.

    HTH

    Craig

  • Craig, you mentioned that "singleton fetches (i.e., equality lookups on unique indexes) need only take key locks while all other seeks and scans take key-range locks".  Is this true for the serializable isolation level only, or all isolation levels?

    I'm seeing some unexpected key-range locks in a read committed transaction, which is why I ask.  And we're not doing any cascading updates/deletes in that transaction.

    Thanks,

    Sam Bendayan

  • My statement above refers specifically to serializable isolation.  However, there are some scenarios where SQL Server will take serializable key-range locks even when a statement or transaction is run at a lower isolation level.  These scenarios include cascading actions (which I gather does not apply in your case), materialized view maintenance, and query notifications.

    HTH

    Craig

  • Hi again.Do the isolation levels affect the INSERT, UPDATE, and DELETE  behaviour or are they only valid for SELECTS?

    thanks

  • The transaction isolation level will not affect the insert, update, or delete operator itself as these operators always take X locks and hold them for the duration of the transaction.  However, the seek and/or scan operators below an insert, update, or delete operator are affected by the transaction isolation level.

    HTH

    Craig

  • When you say the "seek and/or scan operators BELOW an insert, update, or delete operator" you are reffering to the "select * from t2 where t2.a = t1.b " part of the "update t1 set t1.b = t1.b

    where exists (select * from t2 where t2.a = t1.b)" query ? which appears at another one of your great blog posts<a href="blogs.msdn.com/.../read-committed-and-updates.aspx">Read Committed and Updates</a>

  • Actually the plan for the query you mention includes scans of both t1 and t2.  Both of these scans are affected by the isolation level.  Only the update itself is not affected by the isolation level.

    HTH

    Craig

  • Hey,

    Nice post.

    What do you mean by "Even rows that do not qualify for the query result remain locked"? And also, could you show an example of Phantom rows using an insert statement?

    Thanks!

    G.

  • My statement "even rows that do not qualify for the query result remain locked" refers to rows that are returned by a scan but are not retured by the query.  For example, if a query includes a join, rows that are returned by a scan but are not returned by the join still remain locked.

    I'm not sure I understand your question regarding phantom rows.  In the "Phantom Rows" section of the post, the example shows a row (9,0) inserted into table t2.  This row is a phantom for the outer join run in session 2.  The join initially does not see the row but later does see it.

    HTH

    Craig

  • "if a query includes a join, rows that are returned by a scan but are not returned by the join still remain locked" you mean using page level locking which locks also rows not qualified but live in the same page as a qualified row? what is the difference between the scan and a query? aren't only the qualified rows touched?

  • Actually, my statement is independent of the lock granularity (row, page, etc.).  If a scan returns a row to the query processor, the storage engine will acquire and hold a lock to ensure that the row is not updated or deleted until the end of the current transaction.  This lock (which could be a row, page, or even a table lock) will be held even if the row is subsequently "rejected" by the query processor (e.g., due to a filter or join operator).

    HTH

    Craig

Page 1 of 2 (24 items) 12
Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post