In READ COMMITTED isolation, is it possible to read the old value after the update?

Question

I'm trying to solve an issue with SQL Server 2008 R2 and am grasping at straws.

Given that data is housed in leaf-nodes of the B-tree, and is also exists in indexes, that someone could read multiple values during a single UPDATE statement?

Imagine one process updates a row:

--Change status from Pending -> Waiting
BEGIN TRANSACTION
   UPDATE Transactions SET Status = 'Waiting'
   WHERE TransactionID = 12345
COMMIT TRANSACTION

Is it possible that during this BEGIN TRANS; UPDATE; COMMIT another process could read both new and old values?

I wonder this because the update does not alter a value in only one place. That value will exist in multiple places:

• leaf nodes of clustered index
• IX_Tranasctions_1
• IX_Tranasctions_2
• IX_Tranasctions_3
• IX_Tranasctions_4
• IX_Tranasctions_5
• ...
• IX_Tranasctions_n

If the update begins, it has to update values in all these locations. What happens if another process uses an index execution plan to find the value:

• IX_Clustered: Pending Waiting
• IX_Transactions_1: Pending Waiting
• IX_Transactions_2: Pending Waiting
• IX_Transactions_3: Pending
• IX_Transactions_4: Pending
• IX_Transactions_5: Pending
• ...
• IX_Transactions_n: Pending

At that instant, if a query is issued that looks at the value in the clustered index it will find Waiting.

But if a query uses IX_Transactions_4 it will find a value of Pending.

Undocumenting the locking mechanism

The lock order internally is undocumented, but i assume SQL Server takes a shared lock on the clustered and non-clustered indexes:

• IX_Clustered: Shared
• IX_Transactions_1: Shared
• IX_Transactions_2: Shared
• IX_Transactions_3: Shared
• IX_Transactions_4: Shared
• IX_Transactions_5: Shared

Then upgrades those locks to Update locks:

• IX_Clustered: Shared Update
• IX_Transactions_1: Shared Update
• IX_Transactions_2: Shared Update
• IX_Transactions_3: Shared Update
• IX_Transactions_4: Shared Update
• IX_Transactions_5: Shared Update

at this point, another process can still read the values in those 5 indexes; because selects are compatible with Update locks.

Then the update proceeds. If we take a picture at an instant in time:

• IX_Clustered: Shared Update Exclusive -> value changed
• IX_Transactions_1: Shared Update Exclusive -> value changed
• IX_Transactions_2: Shared Update Exclusive
• IX_Transactions_3: Shared Update
• IX_Transactions_4: Shared Update
• IX_Transactions_5: Shared Update

Another process can no longer read values from items with an Exclusive lock, but it can still read values from items that still have the Update lock (Shared locks are compatible with Update locks)

Unless of course that's not what happens

That only works if SQL only promotes to exclusive as needed. If, instead it goes:

• IX_Clustered: S U IX X -> value changed
• IX_Transactions_1: S U IX X -> value changed
• IX_Transactions_2: S U IX X
• IX_Transactions_3: S U IX
• IX_Transactions_4: S U IX
• IX_Transactions_5: S U IX

And i can't type anymore; my will to continue is crushed. Like i said, i'm grasping at straws.

Answer 1

You can use trace flag 1200 to print out locking info and 3916 to print logging info. (Both undocumented AFAIK)

For the following test set up

CREATE TABLE T
(
A INT IDENTITY PRIMARY KEY,
B INT
)

CREATE NONCLUSTERED INDEX IX ON T(B)

INSERT INTO T 
SELECT number
FROM master..spt_values

Then running the following twice (as the second run doesn't contain the irrelevant locks acquired during compilation)

DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

SET NOCOUNT ON;

UPDATE T 
SET B += 1
WHERE A = 1000

/*If you habitually have 3604 on then change the below*/
DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS

This returns the following output

Process 54 acquiring IX lock on OBJECT: 11:245575913:0 (class bit2000000 ref1) result: OK

Process 54 acquiring IU lock on PAGE: 11:1:127 (class bit0 ref1) result: OK

Process 54 acquiring U lock on KEY: 11:72057594039042048 (1f00de11a529) (class bit0 ref1) result: OK

Process 54 acquiring IX lock on PAGE: 11:1:127 (class bit2000000 ref0) result: OK

Process 54 acquiring X lock on KEY: 11:72057594039042048 (1f00de11a529) (class bit2000000 ref0) result: OK

XdesId 0000:00000423: m_logReserved 9162, delta 9162, op LOP_BEGIN_XACT, caller GenerateLogRec, LSN 00000027:00000159:0001.

XdesId 0000:00000423: m_logReserved 9379, delta 217, op LOP_MODIFY_ROW, caller GenerateLogRec, LSN 00000027:00000159:0002.

Process 54 acquiring IX lock on PAGE: 11:1:166 (class bit2000000 ref1) result: OK

Process 54 acquiring X lock on KEY: 11:72057594039107584 (dbf572a551f4) (class bit2000000 ref1) result: OK

XdesId 0000:00000423: m_logReserved 9597, delta 218, op LOP_DELETE_ROWS, caller GenerateLogRec, LSN 00000027:00000159:0003.

Process 54 acquiring IX lock on PAGE: 11:1:166 (class bit2000000 ref0) result: OK

Process 54 acquiring RangeI-N lock on KEY: 11:72057594039107584 (a39688da7d04) (class bit1000000 ref1) result: OK

Process 54 acquiring X lock on KEY: 11:72057594039107584 (82cbfa4b30c1) (class bit2000000 ref0) result: OK

XdesId 0000:00000423: m_logReserved 9671, delta 74, op LOP_INSERT_ROWS, caller GenerateLogRec, LSN 00000027:00000159:0004.

Process 54 releasing lock reference on KEY: 11:72057594039107584 (dbf572a551f4)

Process 54 releasing lock reference on PAGE: 11:1:166

Process 54 releasing lock reference on KEY: 11:72057594039042048 (1f00de11a529)

Process 54 releasing lock reference on PAGE: 11:1:127

XdesId 0000:00000423: m_logReserved 0, delta -9671, op LOP_COMMIT_XACT, caller GenerateLogRec, LSN 00000027:00000159:0005.

No lock is taken on the non clustered index (1:166) until after the LOP_MODIFY_ROW update on the clustered index key (on page 1:127) so yes this would be possible.

And the above is with a narrow (per row) update plan.

With a wide (per index) plan each index is updated in turn. Possibly with intervening sort operations as in the plan below.

This would make the window of opportunity wider but, whilst a transaction could potentially read the "old" value from an index yet to be updated, it would not be possible for a read committed transaction to read the "new" version of the value until the transaction was committed.

It is certainly possible for a read committed statement to read two different committed versions of the same value though.

In one connection run

WHILE ( 1 = 1 )
  BEGIN
      UPDATE T
      SET    B += 1
      WHERE  A = 1000;
  END 

And then in another run

SELECT *
FROM   T T1 WITH(READCOMMITTEDLOCK )
       INNER HASH JOIN T T2  WITH(READCOMMITTEDLOCK )
         ON T1.A + 0 = T2.A + 0
WHERE  T1.A = 1000
       AND T2.A = 1000

For me about 50% of the time this second query returns a row with different B values as the value changes between the two seeks on T.