August 1, 2013 at 4:04 am
Hello,
I am aware the the WITH (NOLOCK) hint should be avoided, that is not the question. Also I am aware that WITH (NOLOCK) can result in inconsistend data. (Any Non serializable isolation level can result in inconsistend data, so I am not concerned about that).
But:
Does using the WITH (NOLOCK) hint in queries allow dirty reads?
Thanks,
Ben
August 1, 2013 at 4:08 am
Does using the WITH (NOLOCK) hint in queries allow dirty reads?
It's not just "allows" dirty read. It does exactly perform what is called "dirty reads"!
August 1, 2013 at 4:20 am
Eugene Elutin (8/1/2013)
It's not just "allows" dirty read. It does exactly perform what is called "dirty reads"!
For my understanding:
1. Dirty reads is the reading of data which is not (yet) committed.
2. NOLOCK hint reads data which is not committed.
Is this a correct understanding ?
Is there example code which does demonstrate this ?
Thanks for your quick reply.
Ben
August 1, 2013 at 4:24 am
ben.brugman (8/1/2013)
For my understanding:1. Dirty reads is the reading of data which is not (yet) committed.
2. NOLOCK hint reads data which is not committed.
Correct.
Is there example code which does demonstrate this ?
Open two windows in Management Studio. In one, begin a transaction and update a table. In the other select from the table with nolock.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2013 at 4:44 am
ben.brugman (8/1/2013)
Eugene Elutin (8/1/2013)
It's not just "allows" dirty read. It does exactly perform what is called "dirty reads"!
For my understanding:
1. Dirty reads is the reading of data which is not (yet) committed.
2. NOLOCK hint reads data which is not committed.
Is this a correct understanding ?
Is there example code which does demonstrate this ?
Thanks for your quick reply.
Ben
There is another point for you to add:
3. Dirty read may not read data which was committed long ago...
August 1, 2013 at 4:53 am
Also worth noting its not just Dirty reads you could potentially be talking about duplicate data reads if your IAM pages split out, or missing data if the pages shrink during your NoLock read.
August 1, 2013 at 5:38 am
There is also a possibility of reading inaacurate data even if the table you are accessing does not change. If there is a lot of upadte activity in other tables, you could possibly get wrong data returned from a table that has not changed.
See http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/ for a fascinating breakdown.
August 1, 2013 at 5:42 am
Just remember, query "hints" are not hints, they are commandments. NO_LOCK is not a suggestion that maybe, if the possibility presents itself, where applicable, avoid taking out some locks if it can. It's basically "THOU SHALL NOT LOCK." Except, people do try to apply it to queries that modify or add data and it must take out locks there, regardless of the hint.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 1, 2013 at 7:50 am
Eugene Elutin (8/1/2013)
3. Dirty read may not read data which was committed long ago...
That's not a dirty read. The definition of dirty read is reading uncommitted data.
That nolock can miss rows and read rows twice is another side effect in addition to dirty reads.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2013 at 10:03 am
GilaMonster (8/1/2013)
Eugene Elutin (8/1/2013)
3. Dirty read may not read data which was committed long ago...That's not a dirty read. The definition of dirty read is reading uncommitted data.
That nolock can miss rows and read rows twice is another side effect in addition to dirty reads.
Ough! That is right. The proper #3 should be:
3. Using NOLOCK hint may cause lose of previously committed rows as well as double-read of existing rows.
August 1, 2013 at 10:07 am
Eugene Elutin (8/1/2013)
GilaMonster (8/1/2013)
Eugene Elutin (8/1/2013)
3. Dirty read may not read data which was committed long ago...That's not a dirty read. The definition of dirty read is reading uncommitted data.
That nolock can miss rows and read rows twice is another side effect in addition to dirty reads.
Ough! That is right. The proper #3 should be:
3. Using NOLOCK hint may cause lose of previously committed rows as well as double-read of existing rows.
Actually, as per MS BoL:
READ UNCOMMITTED
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
If READ UNCOMMITTED "implements dirty read" and "this option has the same effect as setting NOLOCK", I guess missing and double counting of rows could be part of "Dirty Read" definition.
Should it be put to referendum :-D?
August 1, 2013 at 12:31 pm
Eugene Elutin (8/1/2013)
I guess missing and double counting of rows could be part of "Dirty Read" definition.
No it's not. It's a second effect that Books Online doesn't mention and which does not fall under the definition of Dirty Reads (which has a precise definition as part of ISO/ANSI SQL-92 as it's one of the standard anomalies by which isolation levels are defined).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2013 at 9:32 am
Thanks all,
Did try the suggested code, and indeed this was different than I expected. Thank you for educating me.
Thanks,
Ben
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply