Urgent plz Problem with with(Nolock)

Question

Urgent plz Problem with with(Nolock)

Virender Sihag

Say Hey Kid

Points: 688
More actions
May 3, 2011 at 4:31 am

#237443

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 ???????
[font="Arial Narrow"]Failure is not the worst thing. The worst Thing is not to try[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

ricer Right there with Babe Points: 722 More actions · Answer 3

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

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.

- Craig Farrell

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