Contents
When do read uncommitted and nolock hints apply?
READ UNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READ UNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table
What happens when you read a nolock table?
For the NOLOCK table hint specifically, reading uncommitted data that could be rolled back after you have read it can lead to a Dirty read, which can occur when reading the data that is being modified or deleted during the uncommitted data read, so that the data you read could be different, or never even have existed.
What’s the difference between nolock and READ UNCOMMITTED isolation?
The only difference between the two is that the READ UNCOMMITTED isolation level determines the locking mechanism for the entire connection and the NOLOCK table hint determines the locking mechanism for the table that you give the hint to. No difference in terms of their functions, like other have mentioned.
According to this article referencing Paul Randal the reason for taking this BULK_OPERATION shared HOBT lock is to prevent reading of unformatted pages. ReadUncommitted isolation level does acquire locks. Schema stability locks prevent the objects being queries from being altered while the query executes.
What does with nolock mean in SQL Server?
As mentioned previously, the WITH (NOLOCK) table hint allows you to read the data that has been changed, but not committed to the database yet.
Can a nolock query read the entire transaction?
The NOLOCK query clearly does not wait for any single statement to finish before reading the data, never mind the entire transaction. So you could get the data in any state of flux, regardless of how many rows each statement affects and regardless of how many statements are in the entire transaction.