Clever Geek Handbook

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

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

I never do an UPDATE on any of the rows.

+1


source to share


5 answers


If you are asking if you can get data that can be more accurate, it depends on your requests. 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 the row is inserted with or after the date 2008-01-01, you may not get this new row. It can also affect queries that generate aggregates.

If you just mimic updates with delete / insert, you can also get the "old" version of the data.

+5


source


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

If you are inserting (or deleting) records, and the selection can potentially indicate the records that will be in that set, then yes, NOLOCK will give you a dirty read that may or may not include those records.



If inserts or deletes are never potentially fetching (for example, reading data is always yesterday's data, since data arriving or being manipulated today is never read), then yes, it is "safe".

+1


source


If you never do any UPDATEs, then why is locking giving you a problem in the first place?

If the game has referential integrity or trigger firing issues then NOLOCK will simply turn those errors into mysterious inconsistencies.

0


source


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

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

0


source


Not sure how you can get into trouble with SELECT statements if you restrict yourself to INSERT and DELETE. INSERT is problematic because there might have been conflicting primary keys inserted during your query, for example. Both INSERT and DELETE both provide you with conditions expressed in your WHERE clause, or JOINs, etc. May become invalid during your statement.

-1


source






More articles:


All Articles