I am getting lot of deadlocks in my application. As it is very complex
ti avoid deadlocks at this stage of application we have done few steps
to lessen the impact.
We have added retries after deadlock is capturted.
We have added select * from TABLE with (nolock) wherever possible.
But interestingly second step is not working. I have few simple select
statements where i am using nolock criteria still I am getting
deadlock victim error. Any idead why it happening. I thought as soon
as I put nolock in the query it will ignore all the locks.
My sp is
CREATE procedure sp_Check_denomination
@supply_till_id int,
@product_code char(4),
@iso_currency_code char(3),
@denomination money
as
declare @product_id numeric(5)
select @product_id = product_id from product with (nolock) where
product_code = @product_code
if exists (select *
from transaction_inventory TI with (nolock),
product_ccy_denom PCD with (nolock)
where TI.supply_till_id = @supply_till_id
and TI.product_id = @product_id
and TI.iso_currency_code = @iso_currency_code
and TI.denomination = @denomination
and TI.product_id = PCD.product_id
and TI.iso_currency_code = PCD.iso_currency_code
and TI.denomination = PCD.denomination
and PCD.product_id = @product_id
and PCD.denomination = @denomination
and PCD.iso_currency_code = @iso_currency_code
and PCD.tradeable = 1)
begin
return(1)
end
else
begin
return(0)
end
GO
