May 3, 2011 at 4:31 am
Table1 ( row_id )
table2 ( row_id )
insert into table2
select t1.* from table1 t1 with (nolock)
left outer join table2 t2 with(nolock) on t1.row_id=t2.row_id
where t2.row_id is null
table1 is trading table and so heavy amount of rows get inserted into it every second
row_id is identity column in table1
there are no rollbak happing on table1
then why i m getting duplicate records into table2 ???????
May 3, 2011 at 4:32 am
Nolock allows duplicate reads if the underlying table is changing. That's one of the risks you accept by chosing to use Nolock.
If you want consistent data without blocking, consider using one of the snapshot isolation levels.
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
May 3, 2011 at 7:08 am
You have just discovered exactly why so many people caution so strongly against using nolock. It's just not a "run fast" switch as it keeps getting used.
"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
May 3, 2011 at 11:57 am
The problem happens when a query uses allocation scans to scan number of pages in a row, and while it is scanning another process updates or inserts records causing page splits. The page split causes data to either get missed by the scan or get double counted in your case. As others have suggested you can completely remove this by using one of the snapshot isolation levels or you can reduce this occurrence by reducing the number of page splits but the latter is not going to remove this completely.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
May 3, 2011 at 4:50 pm
NOLOCK hint results in dirty reads. You should avoid NOLOCK hint in these kind of insert statements.
Follow Gail and Grant's advise. They are right.
To reduce blocking you can use one of the Isolation Levels like Read Committed Snapshot or Snapshot Isolation levels.
BTW, What's the reason that you are using NOLOCK hint ?
Thank You,
Best Regards,
SQLBuddy
May 3, 2011 at 5:23 pm
sqlbuddy123 (5/3/2011)
BTW, What's the reason that you are using NOLOCK hint ?
At a guess from the first post:
table1 is trading table and so heavy amount of rows get inserted into it every second
To avoid blocking.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply