15

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.

22

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 :

enter image description here

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

enter image description here

The update statement gets a wide update plan...

enter image description here

... 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 |
+------------+------------+
  • 1
    Am I right that If I have a table CREATE TABLE Person ( Id INT PRIMARY KEY, Name VARCHAR(100), Surname VARCHAR(100) ) (without any indexes on Name and Surname) and two queries like in the question, which are executed in separate sessions, then I will get an updated row or an old row, but not some intermediate result of updating the row? – Tesh Mar 14 '15 at 23:02
  • @Tesh yes I don't think it would be possible to get any other result as that would all be on the same page and protected by a latch during the write. – Martin Smith Mar 14 '15 at 23:05
  • Anything unexpected you get with a WITH (NLOCK) hint is your own fault. Can this happen without a NOLOCK hint? – Ross Presser Mar 17 '15 at 20:53
  • 2
    @RossPresser - Yes to the first example, see the index intersection piece here blogs.msdn.com/b/craigfr/archive/2007/05/02/…. For the second I guess potentially it could if two different committed versions were available. Not sure it would be possible to engineer in practice. – Martin Smith Mar 17 '15 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.