Consistency of row when reading with READ UNCOMMITTED  
Author Message
kristenb





PostPosted: Tue Mar 06 17:56:25 CST 2007 Top

SQL Server >> Consistency of row when reading with READ UNCOMMITTED

Hello.

I have a question about the internal consistency of a single row during an
update statement when another connection is reading the data with a READ
UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.

Let me give a small example to illustrate my question.

Say I have a table T1 with some data in it:

CREATE TABLE [T1] (
[optLockVersion] rowversion NULL,
[Name] nvarchar(256) NULL,
[Note] nvarchar(256) NULL
)
INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")

With connection #1, we perform an update statement:

UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"

The above statement will update our row in two ways: it will update the
rowversion and the Note.

Now suppose a second connection tries to read in this row with an isolation
level of READ UNCOMMITTED/NOLOCK:

select * from [T1] WITH(NOLOCK)

Is it possible that the results returned from this select statement would
have updated Notes, but the rowversion would not have been updated in the
returned results? Or perhaps that the rowversion would be updated and the
new Notes would not have been updated yet?

What I'm trying to determine is if SQL server still maintains the
consistency of a single row when reading data in another connection with the
read uncommitted isolation level.

Thanks in advance for your time,

--
Eli Tucker

SQL Server93  
 
 
David





PostPosted: Tue Mar 06 17:56:25 CST 2007 Top

SQL Server >> Consistency of row when reading with READ UNCOMMITTED



> Hello.
>
> I have a question about the internal consistency of a single row during an
> update statement when another connection is reading the data with a READ
> UNCOMMITTED (also known as WITH(NOLOCK) ) isolation level.
>

There is no guarantee about the internal consistency of a single row with
READ UNCOMMITTED/NOLOCK. None. Period.

> Let me give a small example to illustrate my question.
>
> Say I have a table T1 with some data in it:
>
> CREATE TABLE [T1] (
> [optLockVersion] rowversion NULL,
> [Name] nvarchar(256) NULL,
> [Note] nvarchar(256) NULL
> )
> INSERT INTO [T1] (Name, Note) values ("Bob", "Good guy")
>
> With connection #1, we perform an update statement:
>
> UPDATE [T1] set Note = "Decent guy" where [Name] = "Bob"
>
> The above statement will update our row in two ways: it will update the
> rowversion and the Note.
>
> Now suppose a second connection tries to read in this row with an
> isolation
> level of READ UNCOMMITTED/NOLOCK:
>
> select * from [T1] WITH(NOLOCK)
>
> Is it possible that the results returned from this select statement would
> have updated Notes, but the rowversion would not have been updated in the
> returned results? Or perhaps that the rowversion would be updated and the
> new Notes would not have been updated yet?
>
> What I'm trying to determine is if SQL server still maintains the
> consistency of a single row when reading data in another connection with
> the
> read uncommitted isolation level.>

Yes. It is possible. Why? Because there are no guarantees about the
correctness or consistency of results in READ UNCOMMITTED/NOLOCK. Whether
it actually happens depends on details of the algorithms used to update the
physical database pages, and on the details of the table structure.

It is very, very possible, for instance, for such a select query to do an
index seek on a nonclustered index followed by a bookmark lookup on the
clustered index. Some of the output columns will be read from the index and
others from the row data. Here there are two separate physical structures
used to construct one output row, and it will very often happen that a
NOLOCK query will return a row that never existed at any point in time.

Consider setting the database to READ COMMITTED SNAPSHOT ISOLATION to
provide non-blocking and correct query access to changing data.

David