Deadlocks - need to prevent

Plagued by deadlocks!
SQL Server 2012

I have a group of 5 tables which are used to create complex reports for a user.   The user is given his own report ID (the top table's primary key) and the child tables are filled between a few hundred and a few thousand records based on the user's search criteria and calculations.   There are a dozen SQL statements run on the tables all within the main report ID, delete records, insert and then update the records to create the information needed.   Everything works well until the tables start getting over 100,000 records or more.   At that point, I start getting deadlocks.    I should NEVER get a deadlock because user A's report updates never touch any records of User B.   So each process is only affecting their own set of records so resources are never attempting to access other records in a circular fashion; never.    The process tables about 7 seconds to run when the tables have fewer records and about 30 seconds over 200,000+ records.   Then the deadlocks...

How can I see exactly which SQL statement is causing the deadlock (so I can tune that statement)?
How can I stop deadlocks?
LVL 39
gdemariaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

G GodwinDatabase AdministratorCommented:
It sounds like your record locks might be getting escalated to page and or table locks.

Use a query hint to force row locks and see if that helps.

Also try the with (nolocks) query hint to keep the select statements from taking locks that would block other actions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdemariaAuthor Commented:
Thanks for your response.
How would I accomplish your suggestions?  
Do i alter every update and select statement that uses these tables with a specific hint or is it done at the table level or ?
G GodwinDatabase AdministratorCommented:
The first thing I would do (and have done) is to allow the database to run selects in snapshot mode.  

alter database yourDB set allow_snapshot_isolation on 

Open in new window


I'll let you read BOL to get all the details to make sure this option is right for you, but I have found that it cured some deadlock headaches for me in the past.  

Query hints for locking would look like this:

select * from myTable with (rowlock)
select * from myTable with (nolock)

Open in new window


and for an extreme measure you could try disabling lock escalation like this.

alter table myTable set (lock_escalation=disable)

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
Before going to the locking hints solution, check if you have the necessary indexes to speed up the queries. The right index can make miracles.
G GodwinDatabase AdministratorCommented:
True dat.

Vitor is right. Proper indexing is #1. Well, actually it's #2 after good database design.

-GDG
gdemariaAuthor Commented:
Thanks to both of you!   GDG, your specific recommendations are great and most helpful.
Vitor, agreed about indexes.  I was reading another post where the expert focused on using a clustered index for the most important select/update and not on the primary key.  

The question is, how do I determine, with all my updates and select statements, which I ones are most likely causing the lock issues?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Lock issues usually happens when you have a poor performance query. You can start by the WHERE clause. Mostly, the filtering columns should be indexed.
Scott PletcherSenior DBACommented:
How are the child tables clustered?  The child tables should have ReportID as the first key of the clustered index, and the ReportID should be specified in the WHERE clause and/or a JOIN clause(s) for every query.
gdemariaAuthor Commented:
Thanks Scott - that sounds very interesting.

So, currently, the clustered index is created by default on the primary key of the child tables.   You are suggesting to drop that index and recreate the clustered index as   (ReportID, ChildPkeyID),  but then I think I still need a direct index on the ChildPkeyID, right?    So, I would create another unique index on ChildPkeyID (being the primary key of the child report table).

Also, for education purposes, can you tell me what is the benefit to this approach?    I don't see it accessing the child table more directly, will it reduce the lock or what is the goal with this new clustered index?

Thanks again..
Scott PletcherSenior DBACommented:
>> You are suggesting to drop that index and recreate the clustered index as   (ReportID, ChildPkeyID), <<
Yes, exactly, 100%.

>> but then I think I still need a direct index on the ChildPkeyID, right? <<
Perhaps, if you still access it directly by that column alone.  That can still be the PK, it just won't be clustered.

You must specify ReportID in the WHERE clause when you go against the child tables to immediately limit child table lookups to only the rows for that ReportID.  Then no reports will ever block each other except if lock escalation occurs.
G GodwinDatabase AdministratorCommented:
I agree with Scott. That should gain performance benefits in read times, and lower lock contention.

However, you might have a write penalty when more than one report is writing new records into the tables at the same time by having to move rows in order to maintain the clustering.  You never re-use a ReportID do you?  If not, it should always be writing new records for the newest report at the "end" of the table.  If two are writing at the same time in an interleaved fashion, it could have to perform index maintenance, and there could be more blocking during writes.  

Just something to watch out for.

-GDG
G GodwinDatabase AdministratorCommented:
... Back to your original question...

How can I see exactly which SQL statement is causing the deadlock (so I can tune that statement)?

Go to the activity monitor.  Sort the contents by Head Blocker or Blocked By (in 2012 might be different in 2008 I can't remember),

Right click on the offending session, and select DETAILS to see the query it is running at the time.
Hint: it changes fast, so you can pause the Activity Monitor to keep it from refreshing too fast.
G GodwinDatabase AdministratorCommented:
A trace can also show deadlocks. You just have to add the events and columns you wish to see when you set up the trace.

Best Regards,
-GDG
Scott PletcherSenior DBACommented:
Set trace flag 1222 on globally.  Then SQL will list deadlock info in the SQL Server log.
gdemariaAuthor Commented:
@Scott
You must specify ReportID in the WHERE clause when you go against the child tables to immediately limit child table lookups to only the rows for that ReportID.  Then no reports will ever block each other except if lock escalation occurs.
Ok, will do.  But question - I actually have a hierarchy of tables.
     Report -> ProductCategory  -> Products -> ProductYears
So given  (for example) the ProductYears table at the end of the chain (and the able with the most records, about 3,000 per report ID),  would I create a cluster using ProductID and ProductYearID or would you place the ReportID into the ProductYear table as a way of sectioning off all records for just that report and then cluster on ReportID and ProductYearID?

However, you might have a write penalty when more than one report is writing new records into the tables at the same time by having to move rows in order to maintain the clustering.  You never re-use a ReportID do you?

Yes, we sort-of reuse a report ID in this sense...  When a user sits down to create a report, he is assigned a report ID, all child records are INSERTed and then many are UPDATEd based on calculations made.   He can view reports (SELECT) records and if he decides to change some criteria, then all child tables records, in all three child records are DELETEd.   The Report record with the report ID is not touched.   Then all child records are INSERTed and then UPDATEd again.    So, reportID is reused for that user only, a report ID is never touched by more than one user.  Once a week, I delete all child records to save space (the report record always stays as the report can be recreated from that information).
G GodwinDatabase AdministratorCommented:
Yes, we sort-of reuse a report ID in this sense...  When a user sits down to create a report, he is assigned a report ID, all child records are INSERTed and then many are UPDATEd based on calculations made.   He can view reports (SELECT) records and if he decides to change some criteria, then all child tables records, in all three child records are DELETEd.   The Report record with the report ID is not touched.   Then all child records are INSERTed and then UPDATEd again.    So, reportID is reused for that user only, a report ID is never touched by more than one user.  Once a week, I delete all child records to save space (the report record always stays as the report can be recreated from that information).

In this case, you could see some cluster maintenance overhead as reports insert, update, and delete records in child tables surrounded by records from other ReportIDs if clustered as Scott recommended.  

Just know that there are pluses and minuses (aren't there always).  
On the plus side, queries should be faster with less lock contention, because the only pages with rows from more than one ReportID will be at the beginning and ending of each cluster.  
On the minus side, the DB will have to manage that by moving records around when it needs to add records for a report where it is "bounded" by another report.  

The read advantage may be much better than any write penalty though.  I would try Scott's suggestion.  It has a lot of merit.  

However, I personally would try

ALTER DATABASE YourDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON

Open in new window


From BOL:
The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.
https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

In my case I had long running SSRS reports reading lots of data and locking horns with other transactions.   This option really helped. It was easy, and no noticeable overhead or penalty.
Scott PletcherSenior DBACommented:
>>  would I create a cluster using ProductID and ProductYearID or would you place the ReportID into the ProductYear table as a way of sectioning off all records for just that report and then cluster on ReportID and ProductYearID? <<

ReportID should go first in all related child tables and should also always be specified when reading from those table.


>> SET READ_COMMITTED_SNAPSHOT ON ... and no noticeable overhead or penalty. <<

You need to be aware that's just not true.  There is very significant overhead, particularly with the level of INSERTs, UPDATEs and DELETEs you are doing in this case.
Scott PletcherSenior DBACommented:
>> Yes, we sort-of reuse a report ID in this sense ... <<

From a purely performance standpoint, it would be better to get the next sequential report id and use it rather than deleting all the existing rows right away.  You could simply mark the old report id as "dead"/obsolete, and use a new report id number.  That would insure that inserts were at, or near, the end of the table and allow you to delay deleting the child records for a batch process later.
G GodwinDatabase AdministratorCommented:
Scott,

You need to be aware that's just not true.  There is very significant overhead, particularly with the level of INSERTs, UPDATEs and DELETEs you are doing in this case.

Clearly there is overhead.  My point is that I have not seen a negative to the overhead.  Especially when I compare the negative outcome of regularly occurring deadlocks with the positive effect of preventing the deadlocks.  Even if it takes a few clicks longer, a completed query beats a deadlocked query any day.  

If one doesn't have the resources to rewrite applications and reports - needs to resolve this situation quickly and easily - and (certainly a concern) if one's hardware can handle the added overhead - it might be worth it.   Just an option.  There are always trade-offs.

I'd appreciate it if you didn't make it sound as though I gave untrue or bad advice when I state my observation of what actually happened in my own environment as "no noticeable overhead or penalty".

-Always my best regards.
Scott PletcherSenior DBACommented:
I understand what you're saying, but we are dealing with a specific case laid out here.

Snapshot isolation won't necessarily prevent deadlocks.  Modifications -- DELETEs / INSERTs / UPDATEs -- could still deadlock against each other.  Indeed, in this case, I'd be rather surprised if deadlocks didn't continue even after snapshot isolation with the current clustering key structure.  I think the additional overhead of all the writes to tempdb would make that very likely, as SQL will still have to scan entire tables to delete or update the rows for a single, given ReportID.
G GodwinDatabase AdministratorCommented:
I'd be rather surprised if deadlocks didn't continue even after snapshot isolation with the current clustering key structure.

I was skeptical too, and very cautious about turning it on.  However, it worked for me.  

On that note (I sometimes assume, but it should not go without being said):

gdemaria,
Do not implement anyone's advice without testing it out for yourself first.

The fact is, without more information, I couldn't pretend to determine THE one correct answer to your problem in your situation.  You will need make that determination based on the information you have.  There could be more to it still.  Either way, you've gotten some good ideas to look into.  

I hope you get it resolved to your satisfaction.

All the best to you my friends.
-GDG
Scott PletcherSenior DBACommented:
To get the best result, one part of your solution must be to use the ReportID as the lead clustering key in the child tables.

You might need snapshot isolation for some other reason, for which we have no evidence presented here, but that will almost certainly not address your deadlocking issue in this particular case.

Don't get me wrong.  I have dbs with snapshot isolation on.  But you should only do so when the overhead trade-off is worth it.
gdemariaAuthor Commented:
Thank very much for providing all this great information.  I have been doing additional research to try and implement the best choices for my situation.

I have implemented:
set allow_snapshot_isolation on
I have added additional indexes based on a script that i found to show "missing indexes"
I have been changing cluster indexes to include my reportID and updating code to ensure reportID is part of the where-clause for child tables and table joins

I have not implemented:
SET READ_COMMITTED_SNAPSHOT ON  -- I don't know that I need this given the other things I am doing
SET TRANSACTION ISOLATION LEVEL SNAPSHOT  -- I don't know if I need this to turn on isolation level as I set it above?

Please let me know if you have any reactions or cautions or additional thoughts.
Scott PletcherSenior DBACommented:
>> I have added additional indexes based on a script that i found to show "missing indexes" <<

That's a useful step, but in this case I'd wait because:
1) you're changing the clustered index, which could fundamentally change lookup patterns
2) you have to review SQL's recommendations very carefully before you create any new index(es)
G GodwinDatabase AdministratorCommented:
Gdemaria,

I was just wondering if you've made any progress with your question.  Is there anything else we can assist you with?

-GDG
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.