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