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.