Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Question Unanswered: Sql Server Deadlock in transaction

    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.

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    It makes sense to have the transactions short and to access the table in lock step one after the other - that way it reduces ( not removes ) the risk of deadlocking.

    I found using locking hints ( specifically update ( with tablockx ) ) was useful to avoid deadlocks.

    Cheers

    SG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •