Deadlocks....What is going on?

We have background processes that constantly insert into and update
table A. When we do selects from table A from our end user
application (we have many selects that hit this table), we quite
frequently get deadlocks. Is it not safe to select from a table that
is being updated? Surely we do not have to put (updlock) hint on
all of our queries... Is thier not some way to control this from the
update/insert routines (as opposed to chaning all of our selects)
TIAA few tips that may help:
On your INSERT and UPDATE routines do the following:
1. Make the transactions as fast as possible. For example, do your data
scrubbing and cleaning and error checking first, then issue the transaction
portion.
2. Use the tables and views in the same order (if possible) within those
routines. This will make other parts of your application wait to acquire
locks and should lessen the deadlocks.
3. If you know that your update is going to affect a significant portion of
the table, you may wish to consider a table lock hint in the query itself.
This would keep your SELECTs from even beginning to view the table while it
was under a large and lengthy update.
If you don't mind your SELECTS looking at data that is under modification,
you may wish to set your ANSI Transaction Isolation Level to READ
UNCOMMITTED. This will allow for dirty reads and so forth. You wouldn't
have to modify all of your SELECT queries, just SET your session for READ
UNCOMMITTED.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Tommy" <talfano@.ncpsolutions.com> wrote in message
news:86ecf3f7.0410051431.54114c50@.posting.google.com...
> We have background processes that constantly insert into and update
> table A. When we do selects from table A from our end user
> application (we have many selects that hit this table), we quite
> frequently get deadlocks. Is it not safe to select from a table that
> is being updated? Surely we do not have to put (updlock) hint on
> all of our queries... Is thier not some way to control this from the
> update/insert routines (as opposed to chaning all of our selects)
> TIA|||your update uses not the same index on the table as the
select does. so you access data in different directions
which could cause a deadlock.
two choices:
- either put a NOLOCk hint on the select
- use the same index for the where-clause on the select &
update
>--Original Message--
>A few tips that may help:
>On your INSERT and UPDATE routines do the following:
>1. Make the transactions as fast as possible. For
example, do your data
>scrubbing and cleaning and error checking first, then
issue the transaction
>portion.
>2. Use the tables and views in the same order (if
possible) within those
>routines. This will make other parts of your application
wait to acquire
>locks and should lessen the deadlocks.
>3. If you know that your update is going to affect a
significant portion of
>the table, you may wish to consider a table lock hint in
the query itself.
>This would keep your SELECTs from even beginning to view
the table while it
>was under a large and lengthy update.
>If you don't mind your SELECTS looking at data that is
under modification,
>you may wish to set your ANSI Transaction Isolation Level
to READ
>UNCOMMITTED. This will allow for dirty reads and so
forth. You wouldn't
>have to modify all of your SELECT queries, just SET your
session for READ
>UNCOMMITTED.
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Tommy" <talfano@.ncpsolutions.com> wrote in message
>news:86ecf3f7.0410051431.54114c50@.posting.google.com...
>> We have background processes that constantly insert
into and update
>> table A. When we do selects from table A from our end
user
>> application (we have many selects that hit this table),
we quite
>> frequently get deadlocks. Is it not safe to select
from a table that
>> is being updated? Surely we do not have to put
(updlock) hint on
>> all of our queries... Is thier not some way to control
this from the
>> update/insert routines (as opposed to chaning all of
our selects)
>> TIA
>
>.
>|||Rick,
Thanks for your suggestions. We are trying these out right now. I
don't think locking the entire table is an option, but can try dirty
reads, as the transactions should not take that long to complete.
Thanks again,
Tommy
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message news:<#lVO4KzqEHA.3840@.TK2MSFTNGP10.phx.gbl>...
> A few tips that may help:
> On your INSERT and UPDATE routines do the following:
> 1. Make the transactions as fast as possible. For example, do your data
> scrubbing and cleaning and error checking first, then issue the transaction
> portion.
> 2. Use the tables and views in the same order (if possible) within those
> routines. This will make other parts of your application wait to acquire
> locks and should lessen the deadlocks.
> 3. If you know that your update is going to affect a significant portion of
> the table, you may wish to consider a table lock hint in the query itself.
> This would keep your SELECTs from even beginning to view the table while it
> was under a large and lengthy update.
> If you don't mind your SELECTS looking at data that is under modification,
> you may wish to set your ANSI Transaction Isolation Level to READ
> UNCOMMITTED. This will allow for dirty reads and so forth. You wouldn't
> have to modify all of your SELECT queries, just SET your session for READ
> UNCOMMITTED.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Tommy" <talfano@.ncpsolutions.com> wrote in message
> news:86ecf3f7.0410051431.54114c50@.posting.google.com...
> > We have background processes that constantly insert into and update
> > table A. When we do selects from table A from our end user
> > application (we have many selects that hit this table), we quite
> > frequently get deadlocks. Is it not safe to select from a table that
> > is being updated? Surely we do not have to put (updlock) hint on
> > all of our queries... Is thier not some way to control this from the
> > update/insert routines (as opposed to chaning all of our selects)
> >
> > TIA