February 27, 2008 at 4:58 am
Hi
Getting deadlocks in our application. Using profiler, I have isolated the stored procedure, but it is updating just one table (i.e. not the typical deadlock scenario). Here is the code:
CREATE PROCEDURE [dbo].[USP_GetCSSequenceNumber]
@sequenceNumber INT OUTPUT
AS
SET @sequenceNumber = 0
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
UPDATE dbo.GEMS_CSFileNumber WITH (ROWLOCK)
SET @sequenceNumber = SequenceNumber,
SequenceNumber = CASE WHEN SequenceNumber = 999999
THEN 1
ELSE SequenceNumber + 1
END
IF (@@ERROR <>0)
BEGIN
ROLLBACK
RAISERROR (50005,10, 1, N'The sequence number could not be determined.');
END
ELSE
BEGIN
COMMIT
END
During these deadlocks, this procedure gets called many times (1000s of times in a short period of time and its execution time goes from 5 milliseconds to 10000 milliseconds).
Why should this procedure ever get involved in a deadlock? The other deadlocked process is also calling this procedure.
Any help appreciated, as always
Andy
February 27, 2008 at 6:54 am
Are you sure that it's deadlocks, and not blocking locks..?
Anyway, you may try to remove the serialazable transaction level, and also the begin tran/commit tran things, since they're not strictly needed here anyway.
Not needed in the sense to prevent the proc from delivering duplicate numbers, should two processes execute it near-simoultanesly. It's a single update, so it's impossible for this construct to produce dupes, hence the isolation level and explicit transactions aren't needed.
(you get that functionality for free here)
/Kenneth
February 27, 2008 at 7:13 am
Thanks for the reply Kenneth
Definitely shows up as a deadlock, which is why I'm a bit confused.
I will pass your suggestions on to the developers.
Andy
February 27, 2008 at 7:38 am
We had a similar problem at work... caused an average of 640 deadlocks per day with spikes to 4,000 per day. Here's how to fix it...
Step 1... Rewrite the sequence code to the following... notice that it MUST NOT HAVE A TRANSACTION in the code and it must do the calculation, return to the variable, and the update to the row all at the same time...
CREATE PROCEDURE [dbo].[USP_GetCSSequenceNumber]
@sequenceNumber INT OUTPUT
AS
SET @sequenceNumber = 0
UPDATE dbo.GEMS_CSFileNumber
SET @sequenceNumber = SequenceNumber = CASE WHEN SequenceNumber = 999999 THEN 1 ELSE SequenceNumber +1 END
IF (@@ERROR <>0)
BEGIN
RAISERROR (50005,10, 1, N'The sequence number could not be determined.');
END
Step 2... you MUST NOT INCLUDE THE CALL TO THE SEQUENCE IN ANY TRANSACTION.
Period...
The day we implemented both steps... deadlocks on this table dropped to 0. Overall deadlocks (there were some on other tables) dropped to 12.
--Jeff Moden
February 27, 2008 at 7:41 am
By the way... and really think about this... do you ever want to allow a rollback on the sequence? The answer is no, not ever...
--Jeff Moden
February 27, 2008 at 7:41 am
Yep, what Jeff shows is basically what's left when you take out the isolation level and transaction handling code. You can also remove the (rowlock) hint, it's also dead-weight here.
/Kenneth
February 27, 2008 at 7:46 am
Thanks Kenneth and Jeff, appreciate the prompt responses.
Will suggest your recommendations.
Andy
February 27, 2008 at 7:48 am
What Jeff said should do the trick but if by any chance you get Deadlocks again, you should look at sp_getapplock. That should solve the issue once and for all.
-Roy
February 27, 2008 at 8:57 am
Kenneth Wilhelmsson (2/27/2008)
Yep, what Jeff shows is basically what's left when you take out the isolation level and transaction handling code. You can also remove the (rowlock) hint, it's also dead-weight here./Kenneth
We must've posted at about the same time... I saw that in the code and removed it... like you said... dead-weight... it's gonna do a lock no matter what.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply