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