July 9, 2018 at 3:19 pm
ScottPletcher - Monday, July 9, 2018 10:35 AMGilaMonster - Monday, July 9, 2018 2:58 AMdebasis.yours - Monday, July 9, 2018 2:20 AMYes, actually WITH(NOLOCK) hint may return incorrect result sometimes by showing data from other uncommitted transactions (possibly by other users).That really is the least of its problems. There are way more correctness concerns with Nolock than just dirty reads. You really shouldn't be recommending it or using it (and it is NOT a case that you must use Nolock to get good performance)
You've also used SQL Server 2017 specific code in a SQL 2016 forum. (and dropping the procedure before doing a CREATE OR ALTER is an odd choice, the whole point of CREATE OR ALTER is that you don't need to drop the procedure or check for existence first)
I thought the correctness issue with NOLOCK was dirty reads, because nonrepeatable reads and phantom reads can also occur with normal default isolation level of READ COMMITTED.
The main problem is the duplicate rows and missed rows that can occur because of page splits. Not an ANSI isolation level phenomenon, a SQL Servfer-specific one because of the allocation order scan permitted under read uncommitted.
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
July 9, 2018 at 3:45 pm
GilaMonster - Monday, July 9, 2018 3:19 PMScottPletcher - Monday, July 9, 2018 10:35 AMGilaMonster - Monday, July 9, 2018 2:58 AMdebasis.yours - Monday, July 9, 2018 2:20 AMYes, actually WITH(NOLOCK) hint may return incorrect result sometimes by showing data from other uncommitted transactions (possibly by other users).That really is the least of its problems. There are way more correctness concerns with Nolock than just dirty reads. You really shouldn't be recommending it or using it (and it is NOT a case that you must use Nolock to get good performance)
You've also used SQL Server 2017 specific code in a SQL 2016 forum. (and dropping the procedure before doing a CREATE OR ALTER is an odd choice, the whole point of CREATE OR ALTER is that you don't need to drop the procedure or check for existence first)
I thought the correctness issue with NOLOCK was dirty reads, because nonrepeatable reads and phantom reads can also occur with normal default isolation level of READ COMMITTED.
The main problem is the duplicate rows and missed rows that can occur because of page splits. Not an ANSI isolation level phenomenon, a SQL Servfer-specific one because of the allocation order scan permitted under read uncommitted.
From MS's own docs, it doesn't appear that READ COMMITTED prevents them either. And that's always been the case from my understanding, although that was from an earlier version of SQL.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 9, 2018 at 3:54 pm
ScottPletcher - Monday, July 9, 2018 3:45 PMGilaMonster - Monday, July 9, 2018 3:19 PMScottPletcher - Monday, July 9, 2018 10:35 AMGilaMonster - Monday, July 9, 2018 2:58 AMdebasis.yours - Monday, July 9, 2018 2:20 AMYes, actually WITH(NOLOCK) hint may return incorrect result sometimes by showing data from other uncommitted transactions (possibly by other users).That really is the least of its problems. There are way more correctness concerns with Nolock than just dirty reads. You really shouldn't be recommending it or using it (and it is NOT a case that you must use Nolock to get good performance)
You've also used SQL Server 2017 specific code in a SQL 2016 forum. (and dropping the procedure before doing a CREATE OR ALTER is an odd choice, the whole point of CREATE OR ALTER is that you don't need to drop the procedure or check for existence first)
I thought the correctness issue with NOLOCK was dirty reads, because nonrepeatable reads and phantom reads can also occur with normal default isolation level of READ COMMITTED.
The main problem is the duplicate rows and missed rows that can occur because of page splits. Not an ANSI isolation level phenomenon, a SQL Servfer-specific one because of the allocation order scan permitted under read uncommitted.
From MS's own docs, it doesn't appear that READ COMMITTED prevents them either. And that's always been the case from my understanding, although that was from an earlier version of SQL.
Only read uncommitted allows allocation-order scans (unless a table lock is held). You can't get duplicates/missed rows due to page splits under an index-order scan.
You can, of course, get non-repeatable reads and phantoms under read committed, since those are prevented by repeatable read and serialisable respectively, but I wasn't talking about those.
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
July 9, 2018 at 6:26 pm
Nqobilemoyo - Monday, July 9, 2018 2:15 AMHi there again sorry to be a pain but do you think you could break down your code explanation even further i really want to get a grip of it as i am new to everything
Perhaps your lecturer or another student could help. It would be a lot quicker and easier than trying to get the information from an internet site.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply