February 10, 2011 at 1:23 am
Hi. We have 3 applications that are constantly reading and writing to the
same 4 tables in one DB. The information that the applications require from
the DB is completely separated ie App1 update or insert on the 4 tables will
have no effect on App2 or 3.
For example an query from App1 would be something like:
Insert INTO tblTest VALES(1,2,3) where AppKey = 1
or Select * FROM tblTest where AppKey = 1
While App2 would at the same time also be doing and insert or a select:
Insert INTO tblTest VALES(1,2,3) where AppKey = 2
or
Select * FROM tblTest where AppKey = 2
So my question is, is it safe to use NO LOCK option with all the select queries seeing that any updates happening at the same time is related to other applications. The updates and inserts for App1 will be completed before the select query for app 1 is executed.
February 10, 2011 at 1:54 am
no, I will not suggest for nolock.
It will affect your performance very badly and you will see lot of blocking and deadlock as well.
----------
Ashish
February 10, 2011 at 2:35 am
Sorry but your answer does not make sense. No lock is suppose to speed up your queries because the overhead of locking is not happening. Also because nolock is specified deadlocks shouldn't happen. Please can you let me know where I'm misinterpreting.
February 10, 2011 at 2:35 am
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
There are also several threads throughout SSC relating to this question, and could be worth a look at.
BrainDonor.
February 10, 2011 at 2:44 am
No lock is suppose to speed up your queries because the overhead of locking is not happening. Also because nolock is specified deadlocks shouldn't happen. Please can you let me know where I'm misinterpreting.
Please familiarise yourself about lock escallation and isolation level. You will know your answer yourself and then my reply will also make sense. 🙂
----------
Ashish
February 10, 2011 at 2:51 am
In SQL Server 2005 there is a new isolation level-snapshot.
I would go by Ashis's input on reading more on isolation levels so that we are fully aware of the consequences of any chances we make.
M&M
February 10, 2011 at 3:11 am
Carlo,
Are you experiencing problems with your application? If separate processes are affecting different rows, I don't see where locking can be an issue. Unless there is a table lock taking place. And if you are just inserting a bunch of data, go without nolock.
Snapshot Isolation would be very nice if you have a process reading the table while others are modifying. Like a report never returning in a heavy OLTP database. But be careful, because Snapshot Isolation demands from tempdb.
Regards,
DV
February 10, 2011 at 3:38 am
crazy4sql (2/10/2011)
Please familiarise yourself about lock escallation and isolation level. You will know your answer yourself and then my reply will also make sense. 🙂
I've never heard of nolock causing blocking and deadlocks. Can you explain your understanding a bit more, please, as there must be a gap in my understanding.
February 10, 2011 at 3:49 am
As per my understanding NOLOCK table hint will not put shared lock so will be good to avoid deadlocks and will increase query performance. But have to make sure that return resultset will contain dirty-data. So if you are ok to accpet dirty data, i would suggest you to go with NOLOCK hint.
February 10, 2011 at 4:08 am
I've never heard of nolock causing blocking and deadlocks. Can you explain your understanding a bit more, please, as there must be a gap in my understanding.
I have experienced it so shared it here but have no proof (I know u not looking for proof 🙂 ) I am not saying it will be always the case but you might see it in your busy system.
I have found one thread here discussing on same topic.
http://www.sqlservercentral.com/Forums/Topic842499-146-1.aspx
Thought worth to share with
----------
Ashish
February 10, 2011 at 4:16 am
crazy4sql (2/10/2011)
I have found one thread here discussing on same topic.http://www.sqlservercentral.com/Forums/Topic842499-146-1.aspx
Thought worth to share with
If you read through the whole of the thread and read Gail's (GilaMonster) last post, you will see that NOLOCK cannot be escalated. The blocking in that instance was caused by a sub query without the NOLOCK hint
February 10, 2011 at 4:20 am
Thanks for all the comments. I will definitely have to do more research and reading.
But let me also be a bit more specific
If the statements are executed sequentially from App1 within a transaction:
(order of a and b can change)
a. Select * FROM tbltest WITH NOLOCK...where Appkey = 1
b.Insert or Update.... where Appkey = 1
And at the same time from App2within a transaction
(order of a and b can change)
a. Select * FROM tbltest WITH NOLOCK...where Appkey = 2
b.Insert or Update.... where Appkey = 2
Then surely there is no chance of dirty data,phantom rows etc? The 2 applications do not select based on the same AppKey and there will also only be one session each from App1 and App2.
February 10, 2011 at 4:33 am
My train of thought is that NOLOCK is something to be used carefully...VERY carefully.
It can cause some unforeseen, extremely prickly issues.
One of which being trying to get NOLOCK out once it's been put into a live production system.
Basically you're telling the optimiser that you know better, hence be very careful using it.
Carlton.
February 10, 2011 at 4:55 am
carlo 47463 (2/10/2011)
Then surely there is no chance of dirty data,phantom rows etc? The 2 applications do not select based on the same AppKey and there will also only be one session each from App1 and App2.
Although the data has a different Appkey, there's a good chance that the data for different Appkeys will live on the same data page within the database. If you insert more data or update existing data such that it causes a page split you could get inconsistent data.
E.G. IF, at the same time as reading through the table using NOLOCK, the other application causes a page split that moves some data from the end of the table to the beginning, you could end up never reading the data. Conversely, if it causes the data to move from beginning to the end, you could read it twice.
February 10, 2011 at 5:46 am
crazy4sql (2/10/2011)
no, I will not suggest for nolock.It will affect your performance very badly and you will see lot of blocking and deadlock as well.
How will nolock (which results in selects not taking or honouring locks) result in blocking and deadlocking?
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply