October 29, 2007 at 8:38 pm
Hi
I have a query running on my production server
SELECT * FROM table1 WITH (NOLOCK).
But I can see that this spid blocks other spids even though this having NOLOCK hint.
Can anybody please put some valuable comments on this ..
binu john
October 29, 2007 at 10:56 pm
Select cannot block any process even if you not to use (NOLOCK).
Because it applies shared locks.
Look what blocks your SELECT process. There must be some updates involved.
Or probably you've got some UPDATE just before or after that SELECT.
UPDATE won't proceed without exclusive lock. And this will block everything from accessing affected records.
_____________
Code for TallyGenerator
October 29, 2007 at 11:35 pm
Try out this examples for NO LOCK when,where & how to use the same....
sqljunkies.com/Forums/Search/default.aspx?SearchFor=1&SearchText=AspiringGeek
channel9.msdn.com/wiki/default.aspx/PerformanceWiki.SQLServer2000Guidelines
msdn2.microsoft.com/en-us/library/ms998577.aspx
classicasp.aspfaq.com/general/how-do-i-make-my-asp-pages-more-efficient.html
http://www.mssqltips.com/tipimages/1253_concurrencyIsolationLockingVersioning.txt
October 31, 2007 at 5:59 am
Hi,
After I have replaced all SELECTs with adding "WITH (NOLOCK)" into the most used stored procedures, the ERP system is working much better. Now, I noticed that people who has used to call stored procedures with these SELECTs, they did not blocked other users. Only themselves at most. So, this "WITH (NOLOCK)" is very good to use if your application is using for each connection a serialization transaction or almost.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply