Deadlocks and use of nolock

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_idint,
@.product_codechar(4),
@.iso_currency_codechar(3),
@.denominationmoney
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
GOAKS (database_database2001@.yahoo.com) writes:
> 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.

NOLOCK requires careful understanding of what your application is
doing. You may increase reponse time, and you may resolve deadlocks.
But what if the price is incorrect results leading to incorrect decisions?

The procedure you post looks innocent (save that it starts with the
letters sp_, which is a prefix reserved for system procedure; SQL
Server first looks in master for these), but there may be more issues
in involved.

Have you enabled deadlock logging? You do this, by specifying trace
flag 1204 as a startup parameter to SQL Server (use Enterprise Manger).
The output is not that terribly easy to interpret, but if you don't
understand why your deadlocks arise, you cannot address them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp