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

Deadlock question

We are getting deadlocks when running this code from a stored procedure many
times simultaneously with 30 concurrent requests. From our understanding,
repeatable read in this case should lock the single row returned from the
SELECT TOP 1 statement for the length of this transaction and not allow othe
r
requesters to read it or update it. Can you tell us why this is deadlocking
and advise us of a better way to do this update?
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
UPDATE Pins SET PinStatus = 'RESE', HeldDate = getdate()
where Pins.PinID = (
SELECT TOP 1 PinID FROM PINS
WHERE CardTypeID = @.CardTypeID AND PinStatus = 'AVAI' AND OrderID is NULL
and HeldDate is NULL
ORDER BY CreationDate, PinID
)
COMMIT TRANSACTIONHas PinID got a clustered index on it?
Is CreationDate indexed?
Your select might need to lock more rows than necessary due to the way it
accesses the data. Verify that your select is as optimal as possible WRT
index utilization. It may lock the single row, or the page the row is on,
or even extents of pages.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Larry Herbinaux" <Larry Herbinaux@.discussions.microsoft.com> wrote in
message news:044C513A-A65C-4F56-AA88-C91266B00A25@.microsoft.com...
> We are getting deadlocks when running this code from a stored procedure
> many
> times simultaneously with 30 concurrent requests. From our understanding,
> repeatable read in this case should lock the single row returned from the
> SELECT TOP 1 statement for the length of this transaction and not allow
> other
> requesters to read it or update it. Can you tell us why this is
> deadlocking
> and advise us of a better way to do this update?
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
> BEGIN TRANSACTION
> UPDATE Pins SET PinStatus = 'RESE', HeldDate = getdate()
> where Pins.PinID = (
> SELECT TOP 1 PinID FROM PINS
> WHERE CardTypeID = @.CardTypeID AND PinStatus = 'AVAI' AND OrderID is NULL
> and HeldDate is NULL
> ORDER BY CreationDate, PinID
> )
> COMMIT TRANSACTION|||REPEATABLE READ places a shared lock on the resource, not an exclusive lock.
That's probably why you're getting deadlocks.
Change your logic:
DECLARE @.PinID int
BEGIN TRANSACTION
SELECT @.PinID = TOP 1 PinID FROM Pins WITH(UPDLOCK) WHERE...
UPDATE Pins ... WHERE PinID = @.PinID
COMMIT TRANSACTION
You don't need to set the transaction isolation level in this case.
"Larry Herbinaux" <Larry Herbinaux@.discussions.microsoft.com> wrote in
message news:044C513A-A65C-4F56-AA88-C91266B00A25@.microsoft.com...
> We are getting deadlocks when running this code from a stored procedure
many
> times simultaneously with 30 concurrent requests. From our understanding,
> repeatable read in this case should lock the single row returned from the
> SELECT TOP 1 statement for the length of this transaction and not allow
other
> requesters to read it or update it. Can you tell us why this is
deadlocking
> and advise us of a better way to do this update?
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
> BEGIN TRANSACTION
> UPDATE Pins SET PinStatus = 'RESE', HeldDate = getdate()
> where Pins.PinID = (
> SELECT TOP 1 PinID FROM PINS
> WHERE CardTypeID = @.CardTypeID AND PinStatus = 'AVAI' AND OrderID is NULL
> and HeldDate is NULL
> ORDER BY CreationDate, PinID
> )
> COMMIT TRANSACTION