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