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.
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.
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.