Deadlock question

Question

Deadlock question

Green Armchair

Hall of Fame

Points: 3561
More actions
February 27, 2008 at 4:58 am

#187147

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

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

Green Armchair Hall of Fame Points: 3561 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

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

Green Armchair Hall of Fame Points: 3561 More actions · Answer 6

Thanks Kenneth and Jeff, appreciate the prompt responses.

Will suggest your recommendations.

Andy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)