WITH (NOLOCK) question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

WITH (NOLOCK) question

Author

Topic

kdyer
Yak Posting Veteran

53 Posts

Posted - 2006-08-10 : 11:56:10

If we look at the following query, we can see one WITH (NOLOCK)


SELECT     dbo.gen.loan_num, dbo.gen.borrow_ln, dbo.gen.site_id, dbo.gen.loan_amt, dbo.fun.funded, dbo.doc.drawn, dbo.doc.drawn_by, 
                      dbo.programs.prog_name, dbo.programs.short_desc, dbo.programs.long_desc, dbo.defaults.region
FROM         dbo.gen WITH (NOLOCK) INNER JOIN
                      dbo.fun ON dbo.gen.file_id = dbo.fun.file_id INNER JOIN
                      dbo.doc ON dbo.gen.file_id = dbo.doc.file_id INNER JOIN
                      dbo.programs ON dbo.gen.programs_id = dbo.programs.programs_id LEFT OUTER JOIN
                      dbo.defaults ON dbo.gen.site_id = dbo.defaults.site_id

I have been advised that we need actually more WITH (NOLOCK) statements. This question is because we do "real time" reporting against our OLTP environment.


SELECT     dbo.gen.loan_num, dbo.gen.borrow_ln, dbo.gen.site_id, dbo.gen.loan_amt, dbo.fun.funded, dbo.doc.drawn, dbo.doc.drawn_by, 
                      dbo.programs.prog_name, dbo.programs.short_desc, dbo.programs.long_desc, dbo.defaults.region
FROM         dbo.gen WITH (NOLOCK) INNER JOIN
                      dbo.fun WITH (NOLOCK) ON dbo.gen.file_id = dbo.fun.file_id INNER JOIN
                      dbo.doc WITH (NOLOCK) ON dbo.gen.file_id = dbo.doc.file_id INNER JOIN
                      dbo.programs WITH (NOLOCK) ON dbo.gen.programs_id = dbo.programs.programs_id LEFT OUTER JOIN
                      dbo.defaults WITH (NOLOCK) ON dbo.gen.site_id = dbo.defaults.site_id

What are the arguments for and against this?

Thanks,

Kent

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 11:59:13

Generally, optimizer hints should be avoided...however in some cases, you need them...use them carefully.

Regarding NOLOCK hint, it can be used for reporting purpose...but beware it can cause dirty reads also.

Harsh Athalye
India.
"Nothing is Impossible"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-10 : 12:51:43

>> This question is because we do "real time" reporting against our OLTP environment.
I would advise against doing this.
Nolock hints will cause the reports to be unreliable and can still cai=use problems with the oltp function.

It's an architectural problem rather than a coding problem - sounds like you need a reporting system.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kdyer
Yak Posting Veteran

53 Posts

Posted - 2006-08-10 : 15:05:41

Interesting.. Thanks for the responses. We already have a Reporting DB as well as a prod DB. It is the way the vendor has built the system.

Kent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources