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)
TIA
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.c om...
> 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[vbcol=seagreen]
>UNCOMMITTED.
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Tommy" <talfano@.ncpsolutions.com> wrote in message
>news:86ecf3f7.0410051431.54114c50@.posting.google. com...
into and update[vbcol=seagreen]
user[vbcol=seagreen]
we quite[vbcol=seagreen]
from a table that[vbcol=seagreen]
(updlock) hint on[vbcol=seagreen]
this from the[vbcol=seagreen]
our selects)
>
>.
>
|||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>...[vbcol=seagreen]
> 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.c om...

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

Deadlocks (I think)

Hi folks,

I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.

I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

I get:

55 860978 LCK_M_X PAG: 13:1:2573

54 AWAITING COMMAND sleeping sa 1 1499
55 UPDATE sleeping sa 2 1499

respectively. Any help would be welcome.

Thanks in advance,
DonThe queries will most likely not show you a deadlock situation. When SQL server detects a deadlock situation, one of the connections is immediately killed. Try this command, then run the deadlocking process again:

dbcc traceon (-1, 1204, 1205)|||I agree that the queries will only show hanging processes but that's what I found, if I am interpreting the results correctly. I did run the TRACEON command although only for 1204 but I cannot find any output from it in the Error log, in fact the last line in the log is:

2004-02-16 12:22:34.81 spid52 DBCC TRACEON 1204, server process ID (SPID) 52

Am I looking in the wrong spot?|||If I remember correctly, the -1 flag indicates that this flag is to be applied to all spids. If you do not specify the -1, and a deadlock occurs on a separate spid, then the deadlock is not recorded.|||I took your advice, I also discovered that flag 3605 will send that info to the error log. Every five seconds I get an entry to advise me there is no deadlock, but I am still getting the same freezing behaviour in my application.

I would make the assumption that the problem lies in the application however those queries I mentioned before are returning results to indicate my application SPID is waiting on a resource and the same code base operates on Oracle.

Any ideas?|||On the microsoft site there is a sp_blocker procedure which may help you - there is one there for sql 7 and 2000. I don't know how supported it is though!

The procedure will show you the SQL statments causing the blocking lock and the I also think is shows you the blocked SQL statement as well.

You would be wise to test it thoughly though before letting it loose on your prod sys though.|||First, thanks to MCrowley and dbabren. I appreciate your prompt and informative replies. It turns out that my problem was due to a select made just a few statements before the update that was holding a lock on that page.

Please forgive my ignorance in SQLServer but I have run accross many applications running on other DB's that perform a select as a page is entered to display the data to be modified and an update when the changes are submitted. It seems very strange that SQLServer would require me to lace the code with NOLOCK in order to prevent the initial selects from interfering with the subsequent updates. Admittedly the selects are more complicated than necessary due to an antiquated schema but I fail to see how SQLServer could remain competitive in the market with these types of oversights.

I must be missing something!|||I think you need to commit your select statements - that will release the shared locks for you. It sounds like the select and update statements are part of the same transaction - which makes sense if you are selecting for update purposes.

or

set the isolation level for each session to READ UNCOMMITTED which will allow dirty reads - it will also mean that your selects will take no shared lock. This is dangerous though, as data you are updating may be updated by another session at the same time - and one of the updates will be lost.|||This is a common problem in making the transfer from Oracle to SQL Server. In SQL Server, readers block writers. In Oracle, Readers just get outdated information. I am sure you could get a raging flame-war started on which is a better solution to the problem of concurrency.

Basic rule of thumb for all platforms, though, is get in and get out as quick as possible. With both reads, and writes.|||Originally posted by dbabren
set the isolation level for each session to READ UNCOMMITTED

No

it will also mean that your selects will take no shared lock. This is dangerous though

Yes

Basic rule of thumb for all platforms, though, is get in and get out as quick as possible.

Yes Yes

Deadlock on a select query, possible?

I have a simple select query that selects data from a view. I consistently get a deadlock exception when running this query:

Server: Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID #) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The view is a simple select statement that has WITH (NOLOCK) as a hint on all tables. I thought I understand how deadlocks worked, two threads are holding a lock and request the other's item. How does a read uncommitted select statement participate in this?

If I look at the other processes in the current activity and run sql profiler, there is no other activity and no existing locks at the time the statement is run.

Can anyone explain this? Or should I bounce my server and hope it never happens again?

Thanks,

DaveAlthough the (NOLOCK) hint is puzzling, it is possible to deadlock on a single SELECT. I replicated this behavior about 12 years ago, but can't remember the specific scenario other than it was happening on a wide table (I think there was one row to a page, 2K pages) and the deadlock happened on the index.

To see where the deadlock is occuring, you may want to try:
Connection 1:
DBCC TraceOn(1204)
go

Begin Tran
Select foo From v_bar WITH (NOLOCK)
go

Connection 2 (immediately after executing 1)
Exec sp_lock
go

Check the errorlog to see the results of the trace and read up on Troubleshooting Deadlocks in SQL BOL.

Good luck.