views:

128

answers:

2

First of all I'm not asking if NOLOCK should or should not be used. Let's get past that.

I guess the question comes down to how sql server writes data? Is an entire row written at once or does it write it a column at a time?

I'm asking because the NOLOCK hint is being considered. A dirty read is fine as long as the entire row is returned (or not returned). Partially written rows are not acceptable.

+2  A: 

No, it will not return partially written rows. NOLOCK only means that this query won't create new locks. It doesn't mean it won't honor existing locks, and sql server won't do anything that writes data without obtaining a lock first.

What it might do is return rows that are out of date or no longer relevant because something else wrote to a row that would normally have been locked by this query.

Joel Coehoorn
It ignores exisiting locks "No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data." from http://technet.microsoft.com/en-us/library/ms187373.aspx
gbn
So, it neither issues or honours locks...
gbn
+4  A: 

No. Data modification operations like inserts, updates and deletes are protected by low level physical Latches. All data access operations, including lock-free SELECT, are obliged to conform to the latching protocol. The result is that partial writes are never seen by any reader.

Remus Rusanu