October 3, 2014 at 4:49 am
Hi guys.
Here's the scenario: aspnet application, about 300 web requests per second, each request generates 10 to 20 sql server requests. In my vb6 times it was very easy to generate DEADLOCK error with even 3 users with this code:
sqlstr = "SELECT Parameter1 FROM Companies WHERE ID = " & CompanyID
rst.Open sqlstr, cnGlobal, adOpenForwardOnly, adLockOptimistic
If Not (rst.BOF And rst.EOF) Then
Parameter1 = "" & rst("Parameter1")
End If
rst.Close
Set rst = Nothing
in 2 or 3 minutes I could get deadlock error. I solved problem by using adLockReadOnly. I had no problem since years. But ado.net does not have this featıure, well it does but implementing this feature is not good for me at the moment. and its irrelevant with this question.
Instead of changing code I tried to use:
sqlstr = "SELECT Parameter1 FROM Companies WITH (NOLOCK) WHERE ID = " & CompanyID
Using cn As New SqlConnection(DBClass.ConnectionString_)
cn.Open()
Using cmd As New SqlCommand(sqlstr, cn), rst As SqlDataReader = cmd.ExecuteReader
If rst.Read Then
Return "" & rst("Parameter1")
End If
End Using
End Using
and Error is gone. Then I read about DEADLOCKs and organized indexes well and there's no DEADLOCK even I use sql sentence without WITH (NOLOCK).
but question remains in my mind:
Is it safe to use WITH (NOLOCK) if I only read something like "parameter". it will never change, maybe one or two times in a year. In this scenario can I use WITH (NOLOCK) without any doubts ? I know WITH (NOLOCK) can produce dirty resucts but in my case this value won't change often.
Also I read something like WITH (NOLOCK) can cause log files corruption. is that true ?
I also developed a routine that tries to read this value and if it encounters DEADLOCK error retries it. Do you use something like that in your applications ?
I'm very surprise that there are lots of dirty information about surprise on the web. everybody says something different.
Can anyone with DEADLOCK experience enlighten me? do you have any experience reading parameter with WITH (NOLOCK) ? Does it cause any problems in the feature. Now I test my system with my multithreaded application which generates couple of hundreds of test requests per second to web application but I have concerns about real life.
best.
October 3, 2014 at 5:37 am
A select alone cannot deadlock. There's likely more to that transaction and there's definitely another process involved.
If you can get hold of the deadlock graph, it'll be possible to fix the cause of the deadlock, not hide the symptoms.
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
October 3, 2014 at 6:37 am
actually there were index problems as I mentioned and also too many SUM(total) like operations that locks entire table and I changed them. As I said there are no deadlocks now.
but my my question is "is there anything wrong to use WITH (NOLOCK) for reading non-changing fields"
another example. SELECT SUM(Total) From TransactionsTable WITH (NOLOCK)
as I don't need exact results and I'm okay with dirty reads, Can I use NOLOCK option ? or does it cause some log file corruptions as I read from several web sites ?
Actually I'm surprised to "don't use it" comments I saw on hundreds of web sites about NOLOCK because its a command and MS put it there for some reason and if I'm happy with its functionality, its useful for me why not use it ?
I need to hear that "I used it before and nothing wrong" or "don't use it, even its okay for you it messes files etc".
best.
October 3, 2014 at 6:50 am
aykut canturk (10/3/2014)
but my my question is "is there anything wrong to use WITH (NOLOCK) for reading non-changing fields"
Yes.
as I don't need exact results and I'm okay with dirty reads, Can I use NOLOCK option ?
You don't need exact results. So if a bunch of rows appear twice in the output, that's fine? Or if a bunch of rows are missed entirely, that's fine?
If that's the case, that you and your users don't mind at all if the results returned are not correct, that two executions of the same report could return different results, then use it as you like.
The reason so many blog posts say not to use it is because most people who do don't realise what effects it can have. It's not just about reading an in-flight transaction, it's that you can see rows returned twice. So if that hint is used in a critical financial report, it could result in incorrect actions, approving transactions where there's insufficient credit, rejecting transactions where there is enough credit, mis-reporting credit ratings, incorrect financial positions sent to a regulatory body, etc.
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
October 3, 2014 at 7:20 am
no no, you got me wrong. my queries will al be same like:
SELECT Parameter1 FROM Companies WHERE ID = 25
only one or two fields at a time with ID specified. there's no rows more than one. And the row I needed is exact. And this parameter column does not change all the time. We are not talking about reports, datasets etc.
think like that. If you would not stick to programming rules you could hard code this parameter.
the answer I could not find is that. You are right, there are such problems. there are answers for those problems. but I could not find any answer for my simple "single select with ID" problem.
thanks.
October 3, 2014 at 7:34 am
But in that case, what's the point of the hint?
You said that data is not getting updated, so why would you need a hint that lets you read through exclusive locks if there won't be any exclusive locks? You said yourself, the deadlocks were caused by other queries, so you're talking about adding a hint for no reason.
Query hints are not, in general, things that should be used unnecessarily, they're not things that should be splattered into every query just because you can.
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
October 3, 2014 at 8:21 am
puff, my friend,
my question is does it cause any problems on sql server or not. every read request locks the row.
if using NOLOCK option causes no problem then I prefer to use it because I don't want slq server to lock same record 300 times in a second. that's the point of using this hint.
as I said this is a question about understanding NOLOCK option's nature. I have no problem at the moment. I want to eliminate unnecessary locks. Because I read too many parameters from different tables. every one of them have same nature. they are static but they are too many. This is by design. my logic says less lock is better. I'm seeking answer to this questions.
best
October 3, 2014 at 9:11 am
aykut canturk (10/3/2014)
my logic says less lock is better.
It's no where near that simple.
Is using the hint going to damage the database? Of course not.
That doesn't mean you use it everywhere. Locks are not bad. SQL has no problems requesting and taking locks at high volume, it's designed to do that.
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
October 3, 2014 at 10:53 am
yeap, as I said there's too much rumors about that. thanks for your answer.
By the way, as you answer my questions, here's the another related one:
what about SUM functions like SELECT SUM(total) from Transactions
Imagine you need this value on every operation and there are 300 hundred operations per second. how do you manage that. what would you do ?
I wrote stored procedures to keep totals in another table for each company so it's faster and easier to to access. but is this right way ?
SUM functions cause lots of deadlocks in this capacity. 2 things I got rid of deadlocks are add indexes (not enough alone) and eliminate SUM queries.
what would you say 🙂
best
October 3, 2014 at 10:58 am
If you need to sum the entire Transactions table 300 times a second (with no filtering or grouping), I'd say you need to either reconsider your design or your requirements. One (or both) doesn't make sense.
No, functions like SUM do not cause lots of deadlocks.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply