Deadlocks

We have a deployed website with many concurrent users who are mostly
reading from the database although there are frequent inserts/updates
as well. At scheduled intervals, we run multiple matching queries
against a table with around 120,000 rows. We used to run it WITH
(NOLOCK), but we decided that the default behavior of skipping over
noncommited transactions was acceptible. However, whereas before it
might get a deadlock once or twice a day, after taking out the WITH
(NOLOCK) we are getting up to 9 deadlocks every time it is run! Does
anyone know why these (read-only) queries are deadlocking so much? Oh,
if it helps, the scheduled queries are sequential so they are not
interfering with each other. Here is some (renamed) DDL if it helps:
CREATE PROCEDURE [mycompany].[my_sp] (
@.id int,
@.age int)
AS
SELECT
f.code_alpha,
f.code_beta,
f.low,
f.high
FROM foo AS f
JOIN bar AS b ON f.site = b.site
WHERE
DATEDIFF(hh, f.created, getdate()) <= @.age AND
DATEDIFF(hh, f.created, getdate()) > 0 AND
b.id = @.id AND
((b.category1 = 1 AND f.category = 1) OR
(b.category2 = 1 AND f.category = 2) OR
(b.category3 = 1 AND f.category = 3) OR
(b.category4 = 1 AND f.category = 4) OR
(b.category5 = 1 AND f.category = 5) OR
(b.category6 = 1 AND f.category = 6)) AND
(b.low <= f.high AND
b.high >= f.low) AND
((b.policy = 1 AND f.policy_alpha IN (1, 3)) OR
(b.policy = 2 AND f.policy_beta IN (1, 3)) OR
(b.policy = 3 AND f.policy_alpha IN (1, 3) AND f.policy_beta IN (1,
3)) OR
b.policy = 0) AND
f.code_alpha >= b.code_alpha AND
f.code_beta >= b.code_beta AND
f.confirmed = 1 AND
f.valid = 1
GO(steve.edison@.gmail.com) writes:
> We have a deployed website with many concurrent users who are mostly
> reading from the database although there are frequent inserts/updates
> as well. At scheduled intervals, we run multiple matching queries
> against a table with around 120,000 rows. We used to run it WITH
> (NOLOCK), but we decided that the default behavior of skipping over
> noncommited transactions was acceptible. However, whereas before it
> might get a deadlock once or twice a day, after taking out the WITH
> (NOLOCK) we are getting up to 9 deadlocks every time it is run! Does
> anyone know why these (read-only) queries are deadlocking so much? Oh,
> if it helps, the scheduled queries are sequential so they are not
> interfering with each other. Here is some (renamed) DDL if it helps:
It's about impossible to tell why queries we know little about deadlock.
I would guess, though, that they clash with some updating process.
Have you look at the deadlock trace? If you have not enabled this, you
should do that. From Enterprise Manager, specify -T 1204 and -T 3605 as
startup parameters, and restart the server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx