THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Selects under READ COMMITTED and REPEATABLE READ may return incorrect results.

Selects under READ COMMITTED may return incorrect results if the data they select is being modified at the same time. I will provide a repro script which on my laptop returns incorrect results in more than 90% cases. Also I will provide a similar repro script which runs under REPEATABLE READ but still returns incorrect results in more than 40% cases.

Note: some of the effects described in this post are very similar to the ones described in

Tony Rogerson's post "Timebomb - Consistency problem with READ COMMITTED" and in my previous post about deadlocks under high isolation levels.

I encourage you to read those posts too.

 

Prerequisites

 

The table and the initial data are as follows:

 

CREATE TABLE Data.AccountBalances(

  AccountNumber INT NOT NULL

    CONSTRAINT PK_AccountBalances PRIMARY KEY,

  Amount DECIMAL(10,2) NOT NULL,

  CustomerID INT NOT NULL,

  SpaceFiller CHAR(100) NOT NULL

);         

GO

DECLARE @i INT;

SET NOCOUNT ON;

SET @i=100000;

WHILE(@i<300000) BEGIN

  INSERT Data.AccountBalances(

    AccountNumber,

    Amount,

    CustomerID,

    SpaceFiller)

  VALUES(@i*10, 1000, @i, 'qwerty');

  SET @i=@i+1;

END;

GO

 

Imitation of OLTP activity against the table

 

The following script transfers money from one account to another, $10 at a time:

 

DECLARE @i INT, @changed INT, @rc INT, @AccountNumber INT, @AnotherAccountNumber INT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET NOCOUNT ON;

SELECT @i=0, @changed = 0;

WHILE(@i<1000000) BEGIN

  -- currently all account numbers end with zeros

  SELECT @AccountNumber = 100000 + RAND()*200000;

  SELECT @AnotherAccountNumber = 100000 + RAND()*200000;

  -- so I calculate account numbers in two simple steps

  SELECT @AccountNumber = @AccountNumber * 10,

    @AnotherAccountNumber = @AnotherAccountNumber * 10;

  IF @AccountNumber <> @AnotherAccountNumber BEGIN

    BEGIN TRAN

    UPDATE Data.AccountBalances SET Amount = Amount - 10

      WHERE AccountNumber = @AccountNumber

      AND Amount > 10;

    SELECT @rc = @@ROWCOUNT;

    IF @rc = 1 BEGIN

      UPDATE Data.AccountBalances SET Amount = Amount + 10

        WHERE AccountNumber = @AnotherAccountNumber;

      SELECT @rc = @@ROWCOUNT;

    END

    IF @rc = 1 BEGIN

      COMMIT;

    END ELSE BEGIN

      ROLLBACK;

    END

  END

  SET @i=@i+1;

END;

GO

Note that no money is withdrawn from the bank and no additional money is deposited – the total amount of money in all accounts always stays the same. Open this script in an SSMS tab.

 

 

Selecting with incorrect results under READ COMMITTED isolation level

 

Open the following script in another SSMS tab:

 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET NOCOUNT ON;

DECLARE @i INT;

SET @i=0;

DECLARE @totals TABLE(total FLOAT);

WHILE(@i<500) BEGIN

  INSERT INTO @totals (total)

    SELECT SUM(Amount)

    FROM Data.AccountBalances;

  SET @i=@i+1;

END;

SELECT COUNT(*), total

  FROM @totals

  GROUP BY total;

 

Move this script to another tab group. Run both scripts simultaneously. The correct total amount of money in all accounts is always 200000000 – note that you will get different results from different runs of your query. In fact, when I run this scripts on my laptop, I am getting correct results in less than 10% cases. Note that you incorrect total can be both below and above the correct value. You can also verify that total amount of money in all accounts is still 200000000:

 

SELECT SUM(Amount)

    FROM Data.AccountBalances;

 

 

Does REPEATABLE READ help?

 

In some cases REPEATABLE READ does ensure correct results, although it also causes deadlocks. In your select script, replace the first line:

 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

 

With the following commands which up the isolation level and deadlock priority:

 

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET DEADLOCK_PRIORITY HIGH;

 

Rerun both scripts. Whenever you modification script becomes a deadlock victim,

Msg 1205, Level 13, State 51, Line 21

Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 

rerun it. Note that this time all you selects return correct totals. The reason is simple: under REPEATABLE READ you cannot transfer any money to or from an account that was already read and included in the totals. That was discussed in more detail in my previous post about deadlocks under high isolation levels. 

However, REPEATABLE READ does not prevent inserts into the range of already selected data. So under REPEATABLE READ isolation level you can create a new account and transfer money to it. If this new account is inserted into the range that was already read, than you will get an incorrect total, which this time can only be less than the correct total amount. Replace your modification script with the following one:

 

DECLARE @i INT, @changed INT, @rc INT, @AccountNumber INT, @AnotherAccountNumber INT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET NOCOUNT ON;

SELECT @i=0, @changed = 0;

WHILE(@i<1000000) BEGIN

  -- currently all account numbers end with zeros

  SELECT @AccountNumber = 100000 + RAND()*200000;

  SELECT @AnotherAccountNumber = 100000 + RAND()*200000;

  -- so I calculate account numbers in two simple steps

  SELECT @AccountNumber = @AccountNumber * 10,

    @AnotherAccountNumber = @AnotherAccountNumber * 10;

  IF @AccountNumber <> @AnotherAccountNumber BEGIN

    SET @changed = @changed + 1;

    BEGIN TRAN

    UPDATE Data.AccountBalances SET Amount = Amount - 10

      WHERE AccountNumber = @AccountNumber

      AND Amount > 10;

    SELECT @rc = @@ROWCOUNT;

    IF @rc = 1 BEGIN

       INSERT Data.AccountBalances(

           AccountNumber,

           Amount,

           CustomerID,

           SpaceFiller)

       SELECT @AnotherAccountNumber-5, 10, @i, 'qwerty'

       WHERE NOT EXISTS(SELECT 1 FROM Data.AccountBalances

           WHERE AccountNumber=@AnotherAccountNumber-5);

      SELECT @rc = @@ROWCOUNT;

    END

    IF @rc = 1 BEGIN

      COMMIT;

    END ELSE BEGIN

      ROLLBACK;

    END

  END

  SET @i=@i+1;

END;

GO

Again, run both your modification and your selects simultaneously, restarting your modifications every time you are getting a deadlock. Note that this time you will get some incorrect totals below the correct amount, but never over the correct amount. When I run these scripts on my laptop, I am getting more than 40% incorrect results.

 

 

SERIALIZABLE isolation level guarantees correct totals

 

However, you can expect a lot of deadlocks. You can up the isolation level of your selects, rerun and see for yourself.

 

SNAPSHOT isolation level guarantees correct totals and does not cause deadlocks.

 

You can set it up for your selects, rerun and see for yourself. Further discussion of SNAPSHOT and READ_COMMITTED_SNAPSHOT isolation levels is beyond the scope of this post.

Published Friday, April 10, 2009 8:34 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Bernd Eckenfels said:

What is the second "AND Amount > 10;" in your script?

April 10, 2009 10:41 PM
 

Alexander Kuznetsov said:

Bernd,

I agree that it is redundant, I've removed it. Thanks!

April 10, 2009 11:27 PM
 

Ben Nevarez said:

SELECT is not returning incorrect results. This works as expected.

Inspect sys.dm_tran_locks just after the first UPDATE and you will notice that there is only one lock of type KEY for the record corresponding to @AccountNumber. This means that at this moment it is possible for another process to read the value of @AnotherAccountNumber (before the second UPDATE) because it is not locked yet. SELECT SUM() then will return distinct results if it reads the value of @AnotherAccountNumber before or after the second UPDATE statement.

Ben

April 11, 2009 12:24 AM
 

Linchi Shea said:

Alex;

This is expected behavior for READ COMMITTED as READ COMMITTED only guarantees that it does not read data that is not committed, and it does not guarantee that you'll see a consistent picture at the table level.

Let's use a simpler example. Assume you have a table with three rows and a single column  and all three values are set to 0.

1. Before the SUM query (i.e. SELECT SUM(Amount) FROM TheTable) begins, your OLTP script updates 1st row to -10 and 3rd row to +10, and commits. The SUM query reads the value in the first row as -10.

2. The SUM query releases the shared lock on the 1 row, and locks the 2nd row, and reads its value as 0.

3. The OLTP script updates 1st and 3rd row again, and adds 10 to the 1st row and subtracts 10 from the 3rd row, making the value of the 3rd row 0, and commits.

4. The SUM query releases the shared lock on the 2nd row, and locks the 3rd row, and gets value 0.

5. The SUM query adds all the values up (-10 + 0 + 0), and gets -10 instead of 0.

The SUM query is doing READ COMMITTED correctly because it only sees committed data, but it only sees the committed values on each row at different times.

April 11, 2009 1:28 AM
 

Uri Dimant said:

Hi Alex

Great article ,I also like reading Tony's one on the subject

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx

April 11, 2009 2:55 AM
 

Alex Kuznetsov said:

Ben and Linchi,

Of course READ COMMITTED works as designed.

Linchi,

I agree with your detailed explanation. However, from the business point of view the results are clearly incorrect! That is the whole point  - both READ COMMITTED and sometimes REPEATABLE READ cannot guarantee correct results, and in some cases the percentage of incorrect ones is very high.

April 11, 2009 8:24 AM
 

Alex Kuznetsov said:

Hi Uri,

I really like Tony's post too. Yet I do not completely agree with Tony's post. The following Tony's statement: "REPEATABLE READ will give you a point in time consistent view" is completely correct in the context of his example. However, I have provided another scenario when REPEATABLE READ does not provide a point in time consistent view.

April 11, 2009 8:37 AM
 

Armando Prato said:

Interesting post... thanks for sharing!

I would've expected these results at READ COMMITTED due the behavior of SHARE locks at that level of isolation.

Have you ever encountered this in a production environment?

April 11, 2009 11:00 AM
 

Alexander Kuznetsov said:

Armando,

I never worked for a bank, I just made up this scenario from scratch. What I did see were some cases of missing records in my result sets when I was completely sure that rows were definitely present in the table I was selecting from.

April 11, 2009 2:41 PM
 

Paul White said:

Hey Alex,

Didn't you write something similar to this somewhere else?  I seem to recall it had lots of C# code in it...or am I losing my mind? (probably).

Anyhow.  It's amazing how many people go into denial over this.  The only isolation level that obviously provides the guarantees sought in the article is serializable, as you rightly point out.  With the exception of RC-snapshot, the other levels are clearly vulnerable to unread rows moving around while an operation is in progress.

The clearest demo I have seen uses two query windows, with one query updating a row inside a transaction.  The second query then starts a seek or scan which spans the locked row.  The query blocks at the locked row.  The first query then updates a row which has not been read by the second query yet, moving it within the range already touched by the now-blocked query.  When the lock is released, the second query omits the moved row - producing a total which logically never existed.

Actually that's a lot clearer in practice than it sounds!

Read-committed snapshot avoids the problems by giving us statement-level consistency.  The second query will read the rows as they existed when the statement started - so it will always be 'correct'.

RCS is also a lifesaver for environments where readers frequently block writers or vice versa.  The tragedy of NOLOCK hints on every table may have a finite shelf-life at last!  

BTW I really dislike the 'word' NOLOCK - it sounds too much like a good thing to most people.  If MS has forced us to type READUNCOMMITTED or DIRTY_READS_PLEASE or  WITH (INACCURACY = ON), I wonder how things might have been different...</rant>

I know RCS is not a panacea - it has issues and overhead - but overall, it is pretty awesome!

April 16, 2009 6:07 AM
 

Alexander Kuznetsov said:

Paul,

You are correct - I wrote a similar piece a while ago:

"The Case of the Skewed Totals"

http://www.simple-talk.com/sql/t-sql-programming/the-case-of-the-skewed-totals/

Somehow it did not get much attention. Because I think this issue is important I gave it another try, this time without C#.

April 16, 2009 9:17 AM
 

Paul White said:

Thanks Alex, that was the one!

/P

April 16, 2009 9:29 AM
 

BOB said:

WOW

May 15, 2009 3:06 PM
 

Merrill Aldrich said:

I have just given a talk with my group at work on the basics of transaction isolation, dirty reads (nolock)

July 30, 2009 7:33 PM
 

Binh Nguyen Thanh said:

Thanks, nice post

June 2, 2014 3:18 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement