December 4, 2012 at 2:57 am
Hi,
I have a requirement to return a sequential integer value based upon a prefix. The set of prefix values is dynamic. So, for example, the first time I'm called with the prefix 'XXXX' I should return 1, the second time 2, etc, but if I'm then called with 'YYYY' I should return 1, and so on. It is essential that I never return a duplicate index for a given prefix. Will the following stored proc do what I want:
ALTER PROC net.AllocateJobReference
@BaseRef CHAR(4),
@Index INT OUTPUT
AS
BEGIN TRANSACTION --WITH TABLOCKX
SELECT @Index=LastUsed FROM JobRefAllocation WITH (TABLOCKX, HOLDLOCK) WHERE RefBase=@BaseRef
IF @Index IS NULL BEGIN
INSERT JobRefAllocation ( RefBase, LastUsed ) VALUES ( @BaseRef, 1)
SET @Index=1
END ELSE BEGIN
SET @Index=@Index+1
UPDATE JobRefAllocation SET LastUsed=@Index WHERE RefBase=@BaseRef
END
COMMIT TRANSACTION
I realise there are scalability issues with locking the entire table like this, but volume of calls is never going to go beyond a thousand or so a day. I've tested it myself and it appears to work fine, but I just want to make sure I've not missed any holes!
December 4, 2012 at 3:05 am
December 4, 2012 at 3:28 am
SQL Kiwi (12/4/2012)
http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx
Is that more complicated than it needs to be for my scenario? I can guarantee that the transaction volume will never increase to the point performance will be impacted, and that my proc will never be called from within another transaction. I'm not concerned about holes in the sequence, so if an index is allocated but never used then there is not a problem. I've not had much experience with explicit locking to date, so if you're telling me it won't do what I think, or there are repercussions I'm not aware of, then I'm all ears
December 4, 2012 at 3:43 am
Kevin O'Donovan (12/4/2012)
Is that more complicated than it needs to be for my scenario? I can guarantee that the transaction volume will never increase to the point performance will be impacted, and that my proc will never be called from within another transaction. I'm not concerned about holes in the sequence, so if an index is allocated but never used then there is not a problem. I've not had much experience with explicit locking to date, so if you're telling me it won't do what I think, or there are repercussions I'm not aware of, then I'm all ears
Just take whatever you need. From what you say, it seems you can simplify your code using the example in that blog post:
ALTER PROCEDURE net.AllocateJobReference
(
@BaseRef char(4),
@Index integer OUTPUT
)
AS
BEGIN
UPDATE
dbo.JobRefAllocation WITH (READCOMMITTEDLOCK)
SET
@Index = LastUsed + 1,
LastUsed = LastUsed + 1
WHERE
RefBase = @BaseRef;
END;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 4, 2012 at 3:52 am
Cheers Paul. I went for a table lock because the base reference which determines which sequence to use is dynamic, so there's no guarantee that a row for that sequence exists when this is called. My concern was that two processes might simultaneously request a value for the same brand new sequence. It's highly unlikely to happen, but theoretically possible.
For reference, these values are used externally to the database, as booking identifiers sent to customers.
December 4, 2012 at 4:50 am
I see. Well, the original code would certainly work, but perhaps consider:
SET @Index = 1;
MERGE
dbo.JobRefAllocation WITH (HOLDLOCK) AS JRA
USING
(SELECT @BaseRef) AS SRC (BaseRef)
ON JRA.RefBase = SRC.BaseRef
WHEN MATCHED THEN
UPDATE SET
@Index = JRA.LastUsed + 1,
LastUsed = LastUsed + 1
WHEN NOT MATCHED THEN
INSERT
(RefBase, LastUsed)
VALUES
(@BaseRef, @Index);
(The HOLDLOCK is to prevent a race condition).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 4, 2012 at 11:22 am
Thanks Paul, I will give that a go
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply