SQL Server Performance

Deadlock issue

Discussion in 'SQL Server 2005 General DBA Questions' started by murthy_pvasn@yahoo.com, Mar 18, 2010.

  1. 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
    Begin
    @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)
    Print@inDeptid
    Commit Transaction
    End
    Thanks in Advance
    PVASN Murthy
  2. Adriaan New Member

    (You are aware that you are dividing employee's salary by 4?)
    (1) You probably should not be setting a variable within an UPDATE statement.
    (2) You probably should not be using PRINT in a production environment.
    UPDATE EMP Set Sal = Sal * .25 WHERE empid = @inempid

    IF EXISTS
    (SELECT t1.* FROM EMP t1
    WHERE t1.sal > 10000 and t1.deptid = (select t2.deptid from emp t2 where t2.empid = @inempid))
    BEGIN
    --.... do something other than PRINT ...
    END

Share This Page