July 23, 2024 at 10:42 am
We are seeing frequent deadlocks occurring due to a particular stored procedure that is using the SERIALIZABLE transaction isolation level. The stored procedure is essentially trying to ensure that the same reference number (concatenated from multiple fields) is never returned more than once.
CREATE PROCEDURE dbo.sp_GenerateNextNumber (
@SequenceKey nvarchar(10),
@ReferenceNumber nvarchar(25) = NULL OUTPUT
)
AS
DECLARE @ReferenceNoPrefix nvarchar(20),
@NextReferenceNo int,
@MaxReferenceNo int,
@IDLength smallint;
SELECT @ReferenceNumber = NULL;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
IF NOT EXISTS
(
SELECT 1
FROM dbo.Reference_Numbers
WHERE SequenceKey = @SequenceKey
AND Active = 1
AND NextReferenceNo <= MaxReferenceNo
)
BEGIN
RAISERROR ('No more numbers are available within this sequence (%s)!', 11, 1, @SequenceKey);
ROLLBACK;
RETURN 1;
END
SELECT
@ReferenceNoPrefix = ReferenceNoPrefix,
@NextReferenceNo = NextReferenceNo,
@MaxReferenceNo = MaxReferenceNo,
@IDLength = IDLength
FROM dbo.Reference_Numbers
WHERE SequenceKey = @SequenceKey
AND Active = 1;
SELECT @ReferenceNumber = ISNULL(@ReferenceNoPrefix, N'') + RIGHT(REPLICATE('0', @IDLength) + CONVERT(nvarchar(11), @NextReferenceNo), @IDLength - LEN(@ReferenceNoPrefix));
SET @NextReferenceNo = @NextReferenceNo + 1;
UPDATE dbo.Reference_Numbers
SET NextReferenceNo = @NextReferenceNo
WHERE SequenceKey = @SequenceKey
AND Active = 1;
COMMIT TRANSACTION;
GO
The table definition and some sample data are provided below.
DROP TABLE IF EXISTS dbo.Reference_Numbers;
GO
CREATE TABLE dbo.Reference_Numbers (
SequenceKey nvarchar(10) NOT NULL,
UsageSequence smallint NOT NULL,
Active bit NOT NULL,
Description nvarchar(30) NULL,
ReferenceNoPrefix nvarchar(8) NULL,
NextReferenceNo int NULL,
MaxReferenceNo int NULL,
IDLength smallint NOT NULL,
CONSTRAINT PK_Reference_Numbers PRIMARY KEY CLUSTERED (SequenceKey, UsageSequence)
);
GO
INSERT dbo.Reference_Numbers
(
SequenceKey,
UsageSequence,
Active,
Description,
ReferenceNoPrefix,
NextReferenceNo,
MaxReferenceNo,
IDLength
)
VALUES
(
N'NEWSEQ',
1,
1,
N'Reference number',
N'A',
24,
30,
6
),
(
N'NEWSEQ',
2,
0,
N'Reference number',
N'B',
1,
30,
6
);
GO
The following is a typical example of the deadlock report XML we are seeing.
<deadlock>
<victim-list>
<victimProcess id="process19d3ca9a8c8"/>
</victim-list>
<process-list>
<process id="process19d3ca9a8c8" taskpriority="0" logused="0" waitresource="KEY: 30:72057596356984832 (23c22f825099)" waittime="949" ownerId="1628625887" transactionname="user_transaction" lasttranstarted="2024-07-21T12:18:52.190" XDES="0x1a982655310" lockMode="U" schedulerid="5" kpid="8976" status="suspended" spid="197" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-07-21T12:18:52.190" lastbatchcompleted="2024-07-21T12:18:52.183" lastattention="1900-01-01T00:00:00.183" clientapp=".Net SqlClient Data Provider" hostname="********" hostpid="7028" loginname="********" isolationlevel="serializable (4)" xactid="1628625887" currentdb="30" currentdbname="MyDatabase" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.sp_GenerateNextNumber" line="90" stmtstart="5968" stmtend="6248" sqlhandle="0x03001e00bd3589296b1a18016aaa000001000000000000000000000000000000000000000000000000000000"> UPDATE dbo.Reference_Numbers SET NextReferenceNo = @NextReferenceNo WHERE SequenceKey = @SequenceKey AND Active = </frame>
</executionStack>
<inputbuf> Proc [Database Id = 30 Object Id = 2082731118] </inputbuf>
</process>
<process id="process1930a615848" taskpriority="0" logused="0" waitresource="KEY: 30:72057596356984832 (23c22f825099)" waittime="949" ownerId="1628625886" transactionname="user_transaction" lasttranstarted="2024-07-21T12:18:52.190" XDES="0x1a969798720" lockMode="X" schedulerid="1" kpid="7456" status="suspended" spid="136" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-07-21T12:18:52.187" lastbatchcompleted="2024-07-21T12:18:52.183" lastattention="1900-01-01T00:00:00.183" clientapp=".Net SqlClient Data Provider" hostname="********" hostpid="7028" loginname="********" isolationlevel="serializable (4)" xactid="1628625886" currentdb="30" currentdbname="MyDatabase" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="MyDatabase.dbo.sp_GenerateNextNumber" line="90" stmtstart="5968" stmtend="6248" sqlhandle="0x03001e00bd3589296b1a18016aaa000001000000000000000000000000000000000000000000000000000000"> UPDATE dbo.Reference_Numbers SET NextReferenceNo = @NextReferenceNo WHERE SequenceKey = @SequenceKey AND Active = </frame>
</executionStack>
<inputbuf> Proc [Database Id = 30 Object Id = 2082731118] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057596356984832" dbid="30" objectname="MyDatabase.dbo.Reference_Numbers" indexname="PK_Reference_Numbers" id="lock19a60536a00" mode="U" associatedObjectId="72057596356984832">
<owner-list>
<owner id="process1930a615848" mode="U"/>
<owner id="process1930a615848" mode="X" requestType="convert"/>
</owner-list>
<waiter-list>
<waiter id="process19d3ca9a8c8" mode="U" requestType="convert"/>
</waiter-list>
</keylock>
<keylock hobtid="72057596356984832" dbid="30" objectname="MyDatabase.dbo.Reference_Numbers" indexname="PK_Reference_Numbers" id="lock19a60536a00" mode="U" associatedObjectId="72057596356984832">
<owner-list>
<owner id="process19d3ca9a8c8" mode="S"/>
<owner id="process19d3ca9a8c8" mode="U" requestType="convert"/>
</owner-list>
<waiter-list>
<waiter id="process1930a615848" mode="X" requestType="convert"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
We understand that the SERIALIZABLE transaction isolation level is the least concurrent of the isolation levels, but we need some way of ensuring that only one instance of the stored procedure can provide a given reference number, to avoid duplicate reference numbers from being generated.
Can anyone please suggest a better way of achieving this, whilst improving concurrency?
July 23, 2024 at 11:18 am
That first ROLLBACK appears to roll nothing back!
Also, you define @ReferenceNumber but don't use it. Are you sure that this is your current proc?
As it's written, I'd be tempted to rewrite as a single UPDATE statement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 23, 2024 at 3:03 pm
Fair point on the ROLLBACK, although it would close the BEGIN TRANSACTION (rather than leaving it open). @ReferenceNumber is used lower down.
SELECT @ReferenceNumber = ISNULL(@ReferenceNoPrefix, N'') + RIGHT(REPLICATE('0', @IDLength) + CONVERT(nvarchar(11), @NextReferenceNo), @IDLength - LEN(@ReferenceNoPrefix));
There are some other lightweight checks performed (i.e. Luhn) just before @ReferenceNumber is set, but this code is not currently being called in the scenarios where we are seeing deadlocks (and so has been removed from this post for simplicity). But performing this in one update statement would not be quite as straightforward as a result.
Are you suggesting we use an OUTPUT clause on the UPDATE statement, to advance the number and get the deleted value for use by the @ReferenceNumber output parameter in one go?
July 23, 2024 at 3:51 pm
Fair point on the ROLLBACK, although it would close the BEGIN TRANSACTION (rather than leaving it open).
Correct, though resolved by moving the BEGIN TRAN down a few rows.
@ReferenceNumber is used lower down.
Apologies, I see now that it is an OUTPUT variable.
Yes, using OUTPUT. I was thinking something like this (completely untested):
CREATE PROCEDURE dbo.sp_GenerateNextNumber
(
@SequenceKey NVARCHAR(10)
,@ReferenceNumber NVARCHAR(25) = NULL OUTPUT
)
AS
SELECT @ReferenceNumber = NULL;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
IF NOT EXISTS
(
SELECT 1
FROM dbo.Reference_Numbers
WHERE SequenceKey = @SequenceKey
AND Active = 1
AND NextReferenceNo <= MaxReferenceNo
)
BEGIN
RAISERROR ('No more numbers are available within this sequence (%s)!', 11, 1, @SequenceKey);
RETURN 1;
END;
DECLARE @RefNo TABLE
(
ReferenceNumber NVARCHAR(25) NOT NULL
);
UPDATE r
SET r.NextReferenceNo = c1.NextReferenceNo
OUTPUT CONCAT (
inserted.ReferenceNoPrefix
,RIGHT(REPLICATE ('0', c1.IDLength), CONVERT (NVARCHAR(11), c1.NextReferenceNo), c1.IDLength
- LEN (c1.ReferenceNoPrefix))
)
INTO @RefNo
FROM dbo.Reference_Numbers r
CROSS APPLY
(
SELECT r2.ReferenceNoPrefix
,NextReferenceNo = r2.NextReferenceNo + 1
,r2.MaxReferenceNo
,r2.IDLength
FROM dbo.Reference_Numbers r2
WHERE r2.SequenceKey = @SequenceKey
AND r2.Active = 1
) c1
WHERE r.SequenceKey = @SequenceKey
AND r.Active = 1;
SELECT TOP(1)
@ReferenceNumber = rn.ReferenceNumber
FROM @RefNo rn;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 23, 2024 at 6:27 pm
Using UPDLOCK and HOLDLOCK
This approach allows you to acquire the necessary locks to ensure serialization without using the SERIALIZABLE isolation level.
CREATE PROCEDURE dbo.sp_GenerateNextNumber (
@SequenceKey nvarchar(10),
@ReferenceNumber nvarchar(25) = NULL OUTPUT
)
AS
DECLARE @ReferenceNoPrefix nvarchar(20),
@NextReferenceNo int,
@MaxReferenceNo int,
@IDLength smallint;
SELECT @ReferenceNumber = NULL;
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
IF NOT EXISTS
(
SELECT 1
FROM dbo.Reference_Numbers WITH (UPDLOCK, HOLDLOCK)
WHERE SequenceKey = @SequenceKey
AND Active = 1
AND NextReferenceNo <= MaxReferenceNo
)
BEGIN
RAISERROR ('No more numbers are available within this sequence (%s)!', 11, 1, @SequenceKey);
ROLLBACK;
RETURN 1;
END
SELECT
@ReferenceNoPrefix = ReferenceNoPrefix,
@NextReferenceNo = NextReferenceNo,
@MaxReferenceNo = MaxReferenceNo,
@IDLength = IDLength
FROM dbo.Reference_Numbers WITH (UPDLOCK, HOLDLOCK)
WHERE SequenceKey = @SequenceKey
AND Active = 1;
SELECT @ReferenceNumber = ISNULL(@ReferenceNoPrefix, N'') + RIGHT(REPLICATE('0', @IDLength) + CONVERT(nvarchar(11), @NextReferenceNo), @IDLength - LEN(@ReferenceNoPrefix));
SET @NextReferenceNo = @NextReferenceNo + 1;
UPDATE dbo.Reference_Numbers
SET NextReferenceNo = @NextReferenceNo
WHERE SequenceKey = @SequenceKey
AND Active = 1;
COMMIT TRANSACTION;
GO
The use of UPDLOCK and HOLDLOCK table hints will ensure that incoming transactions wait for the currently running transaction to complete, reducing the likelihood of deadlocks
July 23, 2024 at 6:28 pm
deleted duplicate
July 24, 2024 at 2:58 pm
Jonathan AC Reports - is HOLDLOCK not equivalent to the highest isolation level of SERIALIZABLE?
July 24, 2024 at 3:25 pm
I think if you do the UPDATE and lookup all in one go, you won't have dups nor deadlocks:
...
UPDATE dbo.Reference_Numbers
SET @NextReferenceNo = NexNextReferenceNo = NextReferenceNo + 1 --<<--
WHERE SequenceKey = @SequenceKey
AND Active = 1;
SELECT @ReferenceNoPrefix = ReferenceNoPrefix,
@MaxReferenceNo = MaxReferenceNo,
@IDLength = IDLength
FROM dbo.Reference_Numbers
WHERE SequenceKey = @SequenceKey
AND Active = 1;
SELECT @ReferenceNumber = ISNULL(@ReferenceNoPrefix, N'') + RIGHT(REPLICATE('0', @IDLength) +
CONVERT(nvarchar(11), @NextReferenceNo - 1), --<<--
@IDLength - LEN(@ReferenceNoPrefix));
COMMIT TRANSACTION;
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 25, 2024 at 2:47 pm
Thank you for your responses. We will look into the suggestions posted above to see if there is a way to recode this, and will come back to you.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply