Update Trn_GJobDet set CompletedDate=getdate() , CompletedEmpId=4335
Where QueueId < 2 and JobCardId=116111 and StageId=53 and
SubJobId=971218 and CompletedDate is Null and IsActive=1
If this table contains only 50 rows i dont think the table scan hurts performance and lead to a dead lock.
We really need to understand what triggers this update statement.
Probably some transaction holds the lock of this table and it is not commited and this update statement triggered leads to the dead lock.
Please analyze more what triggers this update statement.