Deadlock Issue

[irenedaniel]

Hi

Please see the update statements Update 1 and Update 2 below. The table "dataViewRequests" mentioned in the query is a high transactional table where people might be doing an insert, update 1 or update 2 (see queries below). All these statements are in three different SPs. Sometimes when we run Update 1 there has been the error "Transaction (Process ID 156) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

This has started occuring of late and has been frequent. Can you let me know what steps I need to take prevent this locking?

Update 1
update dbo.dataViewRequests WITH (ROWLOCK)
set EditVer = @NewStatus,
Requestor = isnull(@Requestor, Requestor),
Approver = isnull(@Approver, Approver),
APPRDNY_DATE = case when @Approver is not null then getdate() else APPRDNY_DATE end,
IBAckDate = case when @IBApprover is not null and @NewStatus='9' then getdate() else IBAckDate end,
IBClDate = case when @IBApprover is not null and @NewStatus='99' then getdate() else IBClDate end,
IBApprover = isnull(@IBApprover, IBApprover),
ContractApprover = isnull(@ContractApprover, ContractApprover),
contractCloseDate=case when @ContractApprover is not null then getdate() else contractCloseDate end
where PriKey in (select id from applications.dbo.udf_string_splitString(@RequestId ,','))

Update 2
update dataViewRequests
set Approver = tmp.RSM,
EditVer = tmp.NewStatus,
Apprdny_date = getdate()
from dataViewRequests dv
inner join (select PriKey [RequestId],
@Approver [RSM],
case when c.SiteNumber is not null and dv.actionType = 2 then 7 else 6 end [NewStatus]
from dbo.udf_string_splitString(@RequestIds, ',') requests
inner join dbo.dataViewRequests dv (nolock) on dv.PriKey = requests.id
left join dbo.dataViewContractSummary c (nolock) on c.SiteNumber = dv.equipment
) tmp on tmp.RequestId = dv.PriKey


-- After Update there is a select
select dv.equipment, dv.deInstalldate
from dbo.udf_string_splitString(@RequestIds, ',') requests
inner join dbo.dataViewRequests dv (nolock) on dv.PriKey = requests.id
where EditVer = 7