Please help me out here.
One of the stored procedures we have in a invoice processing system is causing a deadlock situation. The error that appears is :
Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen as the deadlock
victim. Rerun the transaction.
This happens in a stored procedure that updates a table. This stored procedure has a transaction at the beginning. After the update the stored proc calls another child proc which also has a transaction and which also tries to update the same table.
Both stored procedures handle different transactions and do a commit or rollback based on error progress.
The error is reported in the main stored proc and everything is rolled back including the child proc execution.
This error happens only once ina while not always. The situation and the execution order of the stored proc remains the same in our testing environment also but the deadlock does not happen there. Also in production the error does not always happen. It happens only once in a 1000 invoices or so.
To me it appears like merging the stored procs into one could be easy solution. But strucurally they are two different procedures and merging would look ugly. Could there be a possibility to make these stored procs share the same transaction and do a commit/rollback in the main stored proc? I want to know if I can use a transaction, have two stored procs but still be able to update the same table in both procedures.
Reply please. Thanks.