Queries with nolock and readuncommitted

If you run a query while the underlying data is being undated, you might be inconsistent results.  If the update is not committed and rolled back, you could get wrong data.  Therefore, update, delete and modify statements typically take a lock on the relevant tables so that you don’t query data in flight or that will never be in the physical table.

If you want, you can override the locked status of the table intentionally with a select query like

select * from table (with nolock) where id > 20

or

select * from table where id > 20 with (readuncommitted)

Both are actually table hints rather than query hints.

Readuncommitted, as a table hint is more often used as a transaction isolation level as in

Go
Select transaction isolation level read uncommitted

Notice that either the tranaction level or the nolock hint can result in bad data, and even one row being read more than once.

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.