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

Deadlock Problem

What's the best way to avoid a deadlock in the following situation:
One query deletes a row from a table i.e.,
delete MyTable
where Id = 1
Another query wants to update the same row in 'MyTable' at the same
time that the first query wants to delete the record, i.e.,
update MyTable
set SomeField = 1
where id = 1
The first query above is called from one process, the second query
above is called from a different process.
If the update fails to update the row because the row has been
deleted this is ok. So I want the delete to take priority.
The 2 processes are processing up to 30 transactions per second.
How can I guarantee that I won't get a deadlock?
Hi,
See the command SET DEADLOCK_PRIORITY in books online.
Thanks
Hari
MCDBA
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.c om...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock?
|||If there is only one table being updated it should not deadlock, it will
only block. Both the update and delete will lock the row while it is
updating or deleting the row and will only temporarily block the other. If
the update is being blocked by the delete it will simply not find the row to
delete once the delete is finished. As long as you don't update 2 or more
tables in reverse order you will most likely only block and not deadlock.
Andrew J. Kelly SQL MVP
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.c om...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock?
sql

Deadlock Problem

What's the best way to avoid a deadlock in the following situation:
One query deletes a row from a table i.e.,
delete MyTable
where Id = 1
Another query wants to update the same row in 'MyTable' at the same
time that the first query wants to delete the record, i.e.,
update MyTable
set SomeField = 1
where id = 1
The first query above is called from one process, the second query
above is called from a different process.
If the update fails to update the row because the row has been
deleted this is ok. So I want the delete to take priority.
The 2 processes are processing up to 30 transactions per second.
How can I guarantee that I won't get a deadlock'Hi,
See the command SET DEADLOCK_PRIORITY in books online.
Thanks
Hari
MCDBA
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.com...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock'|||If there is only one table being updated it should not deadlock, it will
only block. Both the update and delete will lock the row while it is
updating or deleting the row and will only temporarily block the other. If
the update is being blocked by the delete it will simply not find the row to
delete once the delete is finished. As long as you don't update 2 or more
tables in reverse order you will most likely only block and not deadlock.
--
Andrew J. Kelly SQL MVP
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.com...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock'sql