SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Deadlock Help


Need Deadlock Help

Author
Message
Craigmeister
Craigmeister
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 557
I have begun having problems with deadlocks in a database I have inherited and need help interpreting the deadlock info in the SQL Log and going about a resolution.

The situation is that two separate tables are being updated, then a trigger on each of the tables updates the field updated in the original tables in a "report" table. It seems to me that the attempt to get an update lock on the "report" table from the two trigger update operations is what is causing the SQL Server version of the Mexican Stand-Off.

The particular deadlock detail in question is attached.
Detail Notes:
BART_NET_TBL_REPORTING_R7 is the "reporting" table involved
UpdR14_ScheduledDate and updateBartCompleteDate are the two triggers involved.
The 2 update operations on the original tables each update a single record at a time, as do the triggers using a join to the special "inserted" table.
The resources list shows page locks.

Questions
Am I understanding this correctly?
Are the page locks in the table tripping on each other?
Would the 'With (Rowlock)' hint in the trigger code strongly suggest the use of row locks instead of page locks and prevent the deadlock?

My opinion is the best solution is to not use triggers to update this "reporting" table. However, I am in a managed environment that is having serious performance issues because of this. So do you think the rowlock hint or some other code modification in the trigger can allow us to "get a little better" now (to quiet management) so we can devise a much better solution, test and roll out on our scheduled time?

Any and all help is greatly appreciated.
Attachments
deadlock1.txt (10 views, 16.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (270K reputation)SSC Guru (270K reputation)SSC Guru (270K reputation)SSC Guru (270K reputation)SSC Guru (270K reputation)SSC Guru (270K reputation)SSC Guru (270K reputation)SSC Guru (270K reputation)

Group: General Forum Members
Points: 270777 Visits: 42205
If by "update a report table" you mean inserting into it, the trigger is just fine. Something else is going and you just need to find it. Perhaps a long winded explicit transaction is to blame.

As to "quieting managment" by taking a shortcut goes, don't do it. If you do something to make it "good enough" (and it won't be... it'll catch ya again in the future), you won't be given the time to go back and fix it correctly.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Craigmeister
Craigmeister
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 557
Oh there would be no guarantee of a fix. This is being communicated as a tourniquet or a splint - just enough to stop the bleeding and get us home. We need to get where the users can operate and we can put in the changes we want (that we have been beating the drum about for a couple of years now) into our normal change control schedule.
Craigmeister
Craigmeister
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 557
Found the issue and thought I would post her so people would not wonder.

The answer turned out to be simple. I was thinking about the reply by Jeff Moden and his comment about there being a long running query and decided to take another look at the code involved. It turns out there was no index on the "report" table that included the trackingnumber field. This is the field that the triggers in the attached file use to join to the "report" table to update the correct record.

I added a non-clustered index, just on that field for now, since I was skeptical that would have any effect. I mean, come on! Deadlock troubleshooting is supposed to be so much more complex and the solution supposed to be more complicated than this.

Nope. Those particular deadlocks just went away and the application became much more responsive in the related area.

Thanks, Jeff.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search