Hi
Please see the update statements Update 1 and Update 2 below. The table "dataViewReques ts" 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 "Transactio n (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.dataViewReq uests WITH (ROWLOCK)
set EditVer = @NewStatus,
Requestor = isnull(@Request or, Requestor),
Approver = isnull(@Approve r, 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(@IBAppro ver, IBApprover),
ContractApprove r = isnull(@Contrac tApprover, ContractApprove r),
contractCloseDa te=case when @ContractApprov er is not null then getdate() else contractCloseDa te end
where PriKey in (select id from applications.db o.udf_string_sp litString(@Requ estId,','))
Update 2
update dataViewRequest s
set Approver = tmp.RSM,
EditVer = tmp.NewStatus,
Apprdny_date = getdate()
from dataViewRequest s 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(@Re questIds, ',') requests
inner join dbo.dataViewReq uests dv (nolock) on dv.PriKey = requests.id
left join dbo.dataViewCon tractSummary c (nolock) on c.SiteNumber = dv.equipment
) tmp on tmp.RequestId = dv.PriKey
-- After Update there is a select
select dv.equipment, dv.deInstalldat e
from dbo.udf_string_ splitString(@Re questIds, ',') requests
inner join dbo.dataViewReq uests dv (nolock) on dv.PriKey = requests.id
where EditVer = 7
Please see the update statements Update 1 and Update 2 below. The table "dataViewReques ts" 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 "Transactio n (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.dataViewReq uests WITH (ROWLOCK)
set EditVer = @NewStatus,
Requestor = isnull(@Request or, Requestor),
Approver = isnull(@Approve r, 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(@IBAppro ver, IBApprover),
ContractApprove r = isnull(@Contrac tApprover, ContractApprove r),
contractCloseDa te=case when @ContractApprov er is not null then getdate() else contractCloseDa te end
where PriKey in (select id from applications.db o.udf_string_sp litString(@Requ estId,','))
Update 2
update dataViewRequest s
set Approver = tmp.RSM,
EditVer = tmp.NewStatus,
Apprdny_date = getdate()
from dataViewRequest s 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(@Re questIds, ',') requests
inner join dbo.dataViewReq uests dv (nolock) on dv.PriKey = requests.id
left join dbo.dataViewCon tractSummary c (nolock) on c.SiteNumber = dv.equipment
) tmp on tmp.RequestId = dv.PriKey
-- After Update there is a select
select dv.equipment, dv.deInstalldat e
from dbo.udf_string_ splitString(@Re questIds, ',') requests
inner join dbo.dataViewReq uests dv (nolock) on dv.PriKey = requests.id
where EditVer = 7