Dead lock issue RRS feed

  • Question

  • Hi

    The following Stored Proc will Update the given EMP sal & Print the DeptId of the employee if Minimum salary exceeds 10000 in the Given employees Department.

    The below SP can be executed simultaniously by different connections & getting dead lock isssue in the select Statement.

    Empid->Clustered Index

    Deptid->NonCLustered index

    My assumption: SPID-1 Got Rowlock on EMPID=1 with DEPTID=10 for Update Statement

                                        Waiting for Shared lock on select(hold by SPID-2)

                             SPID-2 Got Rowlock on EMPID=2 with DEPTID=10 for Update Statement

                                        Waiting for Shared lock on select(hold by SPID-1)

    Transaction control is required here, Please suggest  me how to avoid dead lock here.

    Crate Procedure Salup(@inempid int) as


    @inDeptid int;

    Beging Transaction 

    Update EMP Set Sal=Sal*.25,@inDeptid =deptid where empid=@inempid

    If Exists(Select top 1 * from EMP where sal>10000 and deptid= @inDeptid)


    Commit Transaction



    Thanks in Advance

    PVASN Murthy

    Friday, March 19, 2010 12:13 AM


  • Not sure if this is a design question or if we should move to the T-SQL Forum, but let's see.

    Is the empId a unique constraint or index? Also, assuming it is, your second query might be where you are getting into trouble.  Because that query will have to look at the data affected by the other update, and you haven't committed the transaction before doing the IF EXISTS, the exclusive lock will cause the failure. 

    Just moving the COMMIT befor ethe IF EXISTS will fix your deadlock if empid is unique (otherwise that table scan would be the issue, I think).  Looking at the plan would provide more information but this is my best guess.

    I guess a question is what you mean by: "Transaction control is required here, ..."  Your Begin and commit transaction statement are more or less meaningless in this object.  Every statment is in a transaction, and since you have only one data changing statement, all you are doing is forcing the client to wait for the IF EXISTS to complet and a print message to occur. so if this is just part of a larger operation perhaps there is more to the problem than meets the eye.

    One thing about deadlocks though. They are annoying, but not harmful. Just program your client to retry the operations that occurred in the transaction and your programs will still work. Not optimally, and the dba should be watching for them and your program should log when it occurs, but deadlocks cannot be 100% eliminated.


    Friday, March 19, 2010 4:24 AM