Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Deadlock Help Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 7:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:07 AM
Points: 49, Visits: 393
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.


  Post Attachments 
deadlock1.txt (4 views, 16.41 KB)
Post #1405126
Posted Wednesday, January 9, 2013 8:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1405141
Posted Wednesday, January 9, 2013 8:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:07 AM
Points: 49, Visits: 393
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.
Post #1405143
Posted Thursday, January 10, 2013 3:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:07 AM
Points: 49, Visits: 393
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.
Post #1405688
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse