In READ COMMITTED isolation, is it possible to read the old value after the update? 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 -&gt; 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.
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.