Read a partially updated row?

Question

Let say I have two queries, running in two separate sessions in SSMS:

First session:

UPDATE Person
SET Name='Jonny', Surname='Cage'
WHERE Id=42

Second session:

SELECT Name, Surname
FROM Person WITH(NOLOCK)
WHERE Id > 30

Is it possible that the SELECT statement could read a half-updated row, for instance one with Name = 'Jonny' and Surname = 'Goody'?

Queries are executed almost simultaneously in separate sessions.

Answer 1

Yes, SQL Server can, under some circumstances read one column's value from the "old" version of the row, and another column's value from the "new" version of the row.

Setup:

CREATE TABLE Person
  (
     Id      INT PRIMARY KEY,
     Name    VARCHAR(100),
     Surname VARCHAR(100)
  );

CREATE INDEX ix_Name
  ON Person(Name);

CREATE INDEX ix_Surname
  ON Person(Surname);

INSERT INTO Person
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID),
                   'Jonny1',
                   'Jonny1'
FROM   master..spt_values v1,
       master..spt_values v2 

In the first connection, run this:

WHILE ( 1 = 1 )
  BEGIN
      UPDATE Person
      SET    Name = 'Jonny2',
             Surname = 'Jonny2'

      UPDATE Person
      SET    Name = 'Jonny1',
             Surname = 'Jonny1'
  END 

In the second connection, run this:

DECLARE @Person TABLE (
  Id      INT PRIMARY KEY,
  Name    VARCHAR(100),
  Surname VARCHAR(100));

SELECT 'Setting intial Rowcount'
WHERE  1 = 0

WHILE @@ROWCOUNT = 0
  INSERT INTO @Person
  SELECT Id,
         Name,
         Surname
  FROM   Person WITH(NOLOCK, INDEX = ix_Name, INDEX = ix_Surname)
  WHERE  Id > 30
         AND Name <> Surname

SELECT *
FROM   @Person 

After running for about 30 seconds I get :

The SELECT query is retrieving the columns from the non clustered indexes rather than the clustered index (albeit due to the hints).

The update statement gets a wide update plan...

... and updates the indexes in sequence so it is possible to read "before" values from one index and "after" from the other.

It is also possible to retrieve two different versions of the same column value.

In the first connection, run this:

DECLARE @A VARCHAR(MAX) = 'A';
DECLARE @B VARCHAR(MAX) = 'B';

SELECT @A = REPLICATE(@A, 200000),
       @B = REPLICATE(@B, 200000);

CREATE TABLE T
  (
     V VARCHAR(MAX) NULL
  );

INSERT INTO T
VALUES     (@B);

WHILE 1 = 1
  BEGIN
      UPDATE T
      SET    V = @A;

      UPDATE T
      SET    V = @B;
  END   

And then in the second, run this:

SELECT 'Setting intial Rowcount'
WHERE  1 = 0;

WHILE @@ROWCOUNT = 0
  SELECT LEFT(V, 10)  AS Left10,
         RIGHT(V, 10) AS Right10
  FROM   T WITH (NOLOCK)
  WHERE  LEFT(V, 10) <> RIGHT(V, 10);

DROP TABLE T;

Straight away, this returned the following result for me

+------------+------------+
|   Left10   |  Right10   |
+------------+------------+
| BBBBBBBBBB | AAAAAAAAAA |
+------------+------------+