January 9, 2013 at 7:28 pm
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.
January 9, 2013 at 8:26 pm
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
Change is inevitable... Change for the better is not.
January 9, 2013 at 8:34 pm
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.
January 10, 2013 at 3:51 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply