views:

159

answers:

2

I know that snapshot isolation would fix this problem, but I'm wondering if NOLOCK is safe in this specific case so that I can avoid the overhead.

I have a table that looks something like this:

drop table Data

create table Data
(
    Id BIGINT NOT NULL,
    Date BIGINT NOT NULL,
    Value BIGINT,
    constraint Cx primary key (Date, Id)
)

create nonclustered index Ix on Data (Id, Date)

There are no updates to the table, ever. Deletes can occur but they should never contend with the SELECT because they affect the other, older end of the table. Inserts are regular and page splits to the (Id, Date) index are extremely common.

I have a deadlock situation between a standard INSERT and a SELECT that looks like this:

select top 1 Date, Value from Data where Id = @p0 order by Date desc

because the INSERT acquires a lock on Cx (Date, Id; Value) and then Ix (Id, Date), but the SELECT acquires a lock on Ix (Id, Date) and then Cx (Date, Id; Value). This is because the SELECT first seeks on Ix and then joins to a seek on Cx.

Swapping the clustered and non-clustered index would break this cycle, but it is not an acceptable solution because it would introduce cycles with other (more complex) SELECTs.

If I add NOLOCK to the SELECT, can it go wrong in this case? Can it return:

  1. More than one row, even though I asked for TOP 1?
  2. No rows, even though one exists and has been committed?
  3. Worst of all, a row that doesn't satisfy the WHERE clause?

I've done a lot of reading about this online, but the only reproductions of over- or under-count anomalies I've seen (one, two) involve a scan. This involves only seeks. Jeff Atwood has a post about using NOLOCK that generated a good discussion. I was particularly interested in a comment by Rick Townsend:

Secondly, if you read dirty data, the risk you run is of reading the entirely wrong row. For example, if your select reads an index to find your row, then the update changes the location of the rows (e.g.: due to a page split or an update to the clustered index), when your select goes to read the actual data row, it's either no longer there, or a different row altogether!

Is this possible with inserts only, and no updates? If so, then I guess even my seeks on an insert-only table could be dangerous.


Update:

I'm trying to figure out how snapshot isolation works. It seems to be row-based, where transactions read the table (with no shared lock!), find the row they are interested in, and then see if they need to get an old version of the row from the version store in tempdb.

But in my case, no row will have more than one version, so the version store seems rather pointless. And if the row was found with no shared lock, how is it different to just using NOLOCK?

A: 

You should be safe with the NOLOCK in this case. One additional thought: Adding Value as an included column on the Ix index should eliminate the seek on Cx.

create nonclustered index Ix on Data (Id, Date) include (Value)
Joe Stefanelli
Could you elaborate on why you think it is safe, in light of the concerns above? Also, I have considered including the non-key columns in the index, but in reality they're rather wide (there's more than one of them) and it would use a significant amount of space. And the duplication might force more useful stuff out of the cache.
Ben Challenor
+5  A: 

Using NOLOCK or READ UNCOMMITTED means you give up any guarantee of consistency. Period.

If you need consistency, don't do dirty reads. Your whole explanation relies on undocumented behavior subject to change in future releases and, far worse, on specific access plans you expect for your query. The Query Optimizer is free to choose any plan it sees fit and any assumption you make it may be broken in production. So is back to square one: don't do dirty reads if you're not prepared to face the consequences.

Not sure if this applies, is not clear what you try to achieve with your query/table, but perhaps this article may help: Using tables as Queues.

Updated
Where a NOLOCK read would read an inconsistent state (eg. read a stale non-clustered index key and chase it to a missing row in the clustered index) a snapshot read would find the 'missing' row in the version store. For stable data, a snapshot read is identical with a nolock read. The magic of the version store comes into play whenever data is changed (uncommitted updates), because the snapshot read goes into the version store and finds the 'old' values (stable and consistent) where the nolock read would go haywire and chase pointers into lala land.

Remus Rusanu
Ah, the OUTPUT clause is very cool, thanks. I don't need it on this particular table but I can think of some future code that I could use it in.And I take your point. I've already experienced the impact of different query plans - the deadlock doesn't occur until there are enough rows to make the two seeks better than a full scan. To be honest I was already leaning towards snapshot isolation. But documenting the undocumented behaviour is interesting to me even if I don't take advantage of it in production. :)
Ben Challenor
Updated original post with another question, thanks.
Ben Challenor
OK, so I agree that we don't want to "read a stale non-clustered index key and chase it to a missing row in the clustered index". The question is, would this actually occur without UPDATEs on any rows? All I'm doing is inserting, so only the metadata will be updated, not the data in any rows. I'd have thought that SQL Server would have some low level consistency mechanism in place to prevent metadata corruption, even when using NOLOCK (which is quite a high level concept)..?
Ben Challenor
NOLOCK will read a consistent row from any rowset. What it cannot guarantee are consistency between different rowsets of the same table (ie. non-clustered vs. clustered), consistency within a rowset (eg. missing rows or duplicate rows) and consistency between repeated reads of the same row (eg. self joins).
Remus Rusanu
"Using NOLOCK or READ UNCOMMITTED means you give up any guarantee of consistency. Period."Another way to phase the question would be 'what are the limits of a correct implementation'. In the extreme, the answer could be 'the behaviour is undefined', in which case corrupting the database would be within the specification. I'm sure the specification of SQL Server is more restrictive than that, although the OPs option #3 is still pretty bad.
Phil