Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but:

  1. Can a row appear as partially updated - that is, some of the columns are updated and some are not?
  2. Can a single column appear partially updated. For example, if you have a varchar(4000) column that was in the process of being completely updated and assuming it actually contains 4000 chars. Can you read say 2k chars from the previous state and 2k chars from its new state? What about varchar(max) with length >8k?

Update: After some debating, the minimal consensus is that if the column size is >8KB, dirty reads, even within the column itself, are possible.

share|improve this question

migrated from stackoverflow.com Dec 28 '11 at 12:25

This question came from our site for professional and enthusiast programmers.

1  
I would guess Yes and No but have no source or reference so won't post as an answer. Great question though. Edit: see this: stackoverflow.com/questions/4278884/… –  gbn Mar 13 '11 at 13:38
    
@gbn: I (mistakenly) thought I knew the answer because I work with highly concurrent tables often. But for rows larger than 8k, it looks like the answer is Yes and Yes. Very surprising. –  Andomar Mar 13 '11 at 20:00
add comment

1 Answer 1

EDITED after reading the MSDN forum link from the comment, very interesting.

Regardless of isolation level, two users cannot update a single page simultaneously, nor can any user read a partially updated page. Just imagine how SQL Server would deal with a page where the header says Col3 starts at byte 17. But it really starts at byte 25, because that part of the row hasn't been updated yet. There's no way a database could handle that.

But for rows larger than 8k, multiple pages are used, and that makes a half-updated column possible. Copied from the MSDN link (in case the link breaks), start this query in one window:

if object_id('TestTable') is not null
    drop table TestTable
create table TestTable (txt nvarchar(max) not null)
go
insert into TestTable select replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 10
update TestTable set txt=replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 100000

This creates a table and then updates it with a string of 100.000x the same character. While the first query is running, start this query in another window:

while 1=1 begin
 if exists (select * from TestTable (nolock) where left(Txt,1) <> right(Txt,1))
    break
end

The second query stops when it reads a column that is half updated. That is, when the first character is different from the last. It will finish quickly, proving that it is possible to read half-updated columns. If you remove the nolock hint, the second query will never finish.

Surprising result! A half-updated XML column might break a (nolock) report, because the XML would be malformed.

share|improve this answer
1  
This is apparently not always true, per social.msdn.microsoft.com/Forums/en-US/transactsql/thread/…, but what column types can be seen partially updated is still somewhat of a mystery. –  Michael Goldshteyn Mar 13 '11 at 13:47
    
@Andomar AFAIK latches would prevent reads of a partially updated page but what if some column values were read from an NCI but it did a bookmark lookup to retrieve a column from the CI. Under NOLOCK I'm sure it would be possible to engineer a situation where the NCI columns were from one version of the row but the CI from a different version. Additionally off row data and lob pages wouldn't be protected by the latch on the data page. –  Martin Smith Mar 13 '11 at 14:08
1  
@Martin: Agreed, I've seen a self join with nolock not find its original row. However, a single read of a field or row should be consistent. –  Andomar Mar 13 '11 at 14:09
1  
@Andomar, unless columns in the row span multiple pages. See my link. –  Michael Goldshteyn Mar 13 '11 at 14:14
2  
This should really be CW because the original answer was nowhere near right, Michael provided the gist of the current answer. Your comment against the question still does not agree with the edited answer. –  Richard aka cyberkiwi Mar 13 '11 at 19:37
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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