August 18, 2010 at 1:26 pm
when declaring a transaction scope we are using a IsolationLevel.ReadCommitted (C#) option for every transaction, some stored procedures have hard coded the NOLOCK hint that is equivalent to READUNCOMMITTED, is this wrong?
August 18, 2010 at 2:28 pm
My understanding is that NOLOCK in a query overrides the global setting for that session.
The question isn't whether the inclusion of NOLOCK is wrong overall, but rather whether NOLOCK makes sure the stored procedure can do its job properly. The danger is that you will get "dirty" uncommitted data in the result set. However, NOLOCK will make sure that the stored procedure has unblocked, and unblocking, access to the tables.
If you remove NOLOCK from the stored procedure, does it still perform as expected? Or even better, do you have access to the person who created the proc? If so, you could ask them if there was a specific need for it, or if it is a habit of his/hers. In our shop, most sprocs are written with NOLOCK to make sure nothing hangs.
August 23, 2010 at 12:29 am
pepe-274551 (8/18/2010)
when declaring a transaction scope we are using a IsolationLevel.ReadCommitted (C#) option for every transaction, some stored procedures have hard coded the NOLOCK hint that is equivalent to READUNCOMMITTED, is this wrong?
You need to be aware that procedure that is using NOLOCK will induce Dirty Read. So as long as you are ok with dirty read there is nothing wrong from programming point of view.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply