Is it safe to use MS SQL's WITH (NOLOCK) option for select statements and insert statements if...

Question

Is it safe to use MS SQL's WITH (NOLOCK) option for select statements and insert statements if you never modify a row, but only insert or delete rows?

I..e you never do an UPDATE to any of the rows.

Answer 1

If you're asking whether or not you'll get data that may no longer be accurate, then it depends on your queries. For example, if you do something like:

SELECT
     my_id,
     my_date
FROM
     My_Table
WHERE
     my_date >= '2008-01-01'

at the same time that a row is being inserted with a date on or after 2008-01-01 then you may not get that new row. This can also affect queries which generate aggregates.

If you are just mimicking updates through a delete/insert then you also may get an "old" version of the data.

Answer 2

If you are never doing any UPDATEs, then why does locking give you a problem in the first place?

If there are referential integrity or trigger-firing issues at play, then NOLOCK is just going to turn those errors into mysterious inconsistencies.

Answer 3

Not sure how SELECT statements could conflict if you're limiting yourself to INSERTs and DELETEs. INSERT is problematic because there may have been conflicting primary keys inserted during your query, for instance. Both INSERTs and DELETEs both expose you to the conditions expressed in your WHERE clause, or JOINs, etc. may become invalid during your statement's execution.

Answer 4

Not in general. (i.e. UPDATE is not the only locking issue)

If you are inserting (or deleting) records and a select could potentially specify records which would be in that set, then yes, NOLOCK will give you a dirty read which may or may not include those records.

If the inserts or deletes would never potentially be selected (for instance the data read is always yesterday's data, wheras today's data coming in or being manipulated is never read), then yes, it is "safe".

Answer 5

Well 'safe' is a very generic term; it all depends on the context of your application and its use. But there is always a chance of skipping and double-counting previously committed rows when NOLOCK hint is used.

Anyways, have a read of this: http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx