Solved

Using  WITH (NOLOCK)

Posted on 2014-01-19
17
624 Views
Last Modified: 2014-01-21
If I have sql queries (ADO) that are merely returning data for display, and if large numbers of queries are being requested at a given time, will adding  WITH (NOLOCK) to the query minimize the deadlock issues that I am having?  Is there a better way?

Here is an example of my recordset open code: rs.Open sql, conn, 1, 2

Basically I run a site that displays fitness event results and when we post results lots of folks are online trying to see their results.  We get lots of deadlock errors at these times.  I am trying to minimize that.  I am avoiding do-while loops, using getrows() instead, whenever possible.  

What else can I do?
0
Comment
Question by:Bob Schneider
  • 5
  • 4
  • 3
  • +3
17 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 84 total points
ID: 39792787
yes it will avoid the deadlocks...

at the expense of concurrency, but looking at your application concurrency may not be a big issue here so just add WITH (NOLOCK).
0
 

Author Comment

by:Bob Schneider
ID: 39792797
I will add it...what do you mean concurrency?  Concurrent changes?  No that is not an issue (except the below situation).  Let me know if that is not what you mean.

What happens if I try to upload a new set of results while they are being viewed online?
0
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 126 total points
ID: 39792802
Simple answer is yes, adding nolock will reduce the deadlocks. However it shouldn't be your first choice of solution because lots of deadlocks usually indicates other issues which should be addressed.

Also before I go on, you should be aware of the risks of using nolock, basically you cannot fully trust any data that you obtain using nolock, because inserts or updates could have affected the data which you will have missed using nolock.

Now there are a lot of things which causes deadlocks, the first thing I would check is that you have the correct indexes created on the tables you are using. Lack of good indexes is a common cause of deadlocks. Remember with indexes you can 'include' columns without indexing on them which when done correctly can mean that all the data required is available via the index lookup.

Other causes of deadlocks are badly defined, or overcomplex queries. Writing good straightforward queries is always good.

Ultimately you want to obtain the deadlock information and find out what is causing them and directly address that.

This will assist with that http://troubleshootingsql.com/2011/09/28/system-health-session-part-4/
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 84 total points
ID: 39792821
it is better to take an example

let us say

1) 6:00 AM --- Customer view the results
2) 6:01 AM --- New results are being uploaded
3) 6:02 AM --- Customer refreshs his page / requests new results from the uI
4) 6:03 AM --- New results are completely loaded
5) 6:04 AM --- Customer refreshes his page again  / requests new results from the UI

in the step 1 -- he will see old results
in the step 2 -- he might see old results or some mix of old and new results or no results at all

in the step 5 -- he will see the new results

in the earlier case when you don't use the WITH (NOLOCK), your customer request at step 3 will wait until step 4 is completed and returns proper results.
0
 

Author Comment

by:Bob Schneider
ID: 39792828
Good information.  Do you have a good resource for setting up indexing?  Is it about primary key/foreign key relationships or more than that?

Here is an example of a  query that causes deadlocks...any advice on the structure, where the indexes should be, etc?

sql = "SELECT r.LastName, r.FirstName, t.TeamName, r.RosterID, r.Gender, ir.RaceTime, ir.Excludes, ir.TeamPlace, ir.Bib "
sql = sql & "FROM Roster r INNER JOIN Grades g ON r.RosterID = g.RosterID INNER JOIN IndRslts ir ON r.RosterID = ir.RosterID "
sql = sql & "INNER JOIN Teams t ON t.TeamsID = r.TeamsID WHERE ir.RacesID = " & lRaceID & " AND ir.Place <> 0 AND ir.RaceTime <> '00:00' "
sql = sql & "AND ir.Excludes = 'n' ORDER BY ir.Place"

Open in new window

0
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 126 total points
ID: 39792837
Primary Keys and Foreign Keys should always be indexed. As should common search columns.

As always google is your friend

http://technet.microsoft.com/en-us/library/jj835095.aspx
https://www.simple-talk.com/sql/database-administration/brads-sure-guide-to-indexes/
0
 
LVL 13

Assisted Solution

by:magarity
magarity earned 42 total points
ID: 39793201
It's hard to tell what indexes you might need to help cut down on the locks without knowing what indexes are already there.  Or are all these tables without indexes?  If they're all completely non indexed, start with making clustered indexes on those fields in the join clauses. I wouldn't index the fields in the WHERE clause unless it was clearly needed.

Using NOLOCK should be your last resort.  SQL Server has thee levels of locking to do an update or insert: table lock, page lock, row lock.  It tends to prefer them in that order.  As you can guess by the names, table locks the whole table, page locks a clump of rows, and row locks just one. So I suggest that your update statements be re-written to specify WITH ROWLOCK.  This will cut down on updates locking more than they really need to.  The reason it does locking the other way around is just because the overhead is higher to track down and lock an individual row but the downside is you find it can cause the problem you describe when there are a lot of users.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 166 total points
ID: 39793375
Hi,

Are multiple people updating the database? Ie Take a football tournament: there are five games at once (say), so five scorers are entering results at the same time, and everyone in each of ten teams is looking at the results at the same time ...

Otherwise I can't see why one user updating results would get deadlocks. Timeouts, poor performance, but why a deadlock?

Do check that the code might be deadlocking itself, and checking the degrees of parallelism might be helpful.

Are you getting/seeing error messages from SQL that say "Deadlock"?

Regards
  David
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 126 total points
ID: 39793387
Couple of additional points:

1/ Only use clustered primary key indexes if you have integer PKs - never with uniqueidentifier PKs.

2/ You can get deadlocks just with select queries if the indexes aren't optimal - it doesn't require anyone updating.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 82 total points
ID: 39795048
>> 2/ You can get deadlocks just with select queries if the indexes aren't optimal - it doesn't require anyone updating. <<

Not true -- SELECTs alone cannot cause a deadlock, as they take only Shared locks.  You must have some type of unshared lock, such as an UPDATE lock, or a page lock to do an INSERT, to get a deadlock.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 166 total points
ID: 39795064
Hi

Thanks Scott:

Back to my question: Are you seeing real deadlocks, or merely timeouts from blocking? There is a difference. That is, do you see any error messages from SQL that say deadlock?

The reason for harping on about this is that as humans our language is often very inexact, whereas the cures for these similar but different events - timeouts and blocking vs deadlocks - are quite different.

Regards
   David
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 82 total points
ID: 39795071
As noted, NOLOCK will reduce the number of deadlocks, but it may lead to incorrect results.

If you have high concurrency needs, a better option would be snapshot isolation.  It ensures that readers don't block writers, and that incorrect results are not returned.

The easiest way to implement snapshot isolation is to set the READ_COMMITTED_SNAPSHOT database option to ON.  But, before you do that, take note of the following:
1) the connection altering the database must be the only connection active at that time; this is just for the initial activation of r_c_s being set on
2) r_c_s requires 14 bytes per row to implement.  Therefore, before you turn that option on, make sure you already have that much free space available in (almost) every page or that you can rebuild the table immediately after you enable the option.
0
 

Author Comment

by:Bob Schneider
ID: 39795779
Let me address a bunch of these things at once:

1) Except when I upload the results to the server, no one is changing any data.  The errors happen as folks try to view what is already there.

2) Based on what i found on another post regarding a login issue, I changed Auto-Close to False.  Since I did that I have had no issues.  We haven't had any events yet either so the probably could still exist but we usually get a few every couple of hours even during down times and so far, nothing.

3) My concern about cursors was probably mis-placed since I am using an ADO recordset object.  Still not sure what the best lock and cursor choice would be but, again, I don't think that is the problem,

4) I believe I was seeing real deadlocks.  Here is the error message that I was getting: Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

5) All of my primary keys ans foreign keys are indexes.  Beyond that I am not sure what other indexes I should create.  I am looking into this.  Clearly I am not someone that would be considered a db admin.

Thanks so much for all you folks have done.  I will read with interest any future comments/suggestions.  I will leave the question open for a day or so and then close it and award points.  I wish I could award 1000.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 166 total points
ID: 39795879
Hi,

Thanks for confirming the real deadlocks.

Just to check on your point 5 above. Have you explicitly indexed the columns that have foreign key constraints?

That is, the primary key is implemented as an index. The foreign key isn't. In joining two tables for a select, indexing the foreign key column can improve performance. The foreign key constraint prevents data being inserted or updated to 'incorrect' values.

If the queries to retrieve results are fairly simple, consider changing the degrees of parallelism on the query to

select t.col1
from dbo.table1 t
where
    somewherecondition
option( maxdop 1 )

This can stop self dead-locking.

And the last question:
When was the last time index maintenance was performed on this database?

HTH
  David
0
 

Author Comment

by:Bob Schneider
ID: 39795893
I will go through and look at all my foreign keys tomorrow to ensure that they are indexed.  

I don't believe I have ever done any index maintenance.  Do you have a resource where I can look at what that entails?

I don't understand this:

If the queries to retrieve results are fairly simple, consider changing the degrees of parallelism on the query to

select t.col1
from dbo.table1 t
where
    somewherecondition
option( maxdop 1 )
0
 
LVL 35

Accepted Solution

by:
David Todd earned 166 total points
ID: 39795908
Hi

Index maintenance.
The thought here is too improve your performance which improves concurrency.
Be aware that this is a fairly IO heavy activity, and should be done out of peak hours. Also requires free disk space.

Resource: Google SQL index maintenance. I use Ola Haalegren's script myself but there are others.

Deadlock
I'm suggesting that based on your scenario that some queries might be self-dead locking when parallel execution is encountered. Removing parallel execution may eliminate those deadlocks.

Regards
  David
0
 

Author Closing Comment

by:Bob Schneider
ID: 39796598
One of the most enlightening discussions ever.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now