I have what looked to me at first glance a very simple problem. I want to be able to obtain a unique key value with a prefix. I have a table which contains 'Prefix' and 'Next_Value' columns.
So you'd think you just start a transaction, get the next value from this table, increment the next value in the table and commit, concatenate the prefix to the value, and you are guaranteed a series of unique alphanumeric keys.
However under load, with various servers hitting this stored proc via ADO.NET, I've discovered that from time to time it will return the same key to different clients. This subsequently causes an error of course when the key is used as a primary key!
I had naively assumed BEGIN TRAN...COMMIT TRAN ensured the atomicity of data accesses within the scope. In looking into this I discovered about transaction isolation levels and added SERIALIZABLE as the most restrictive - with no joy.
Create proc [dbo].[sp_get_key]
@prefix nvarchar(3)
as
set tran isolation level SERIALIZABLE
declare @result nvarchar(32)
BEGIN TRY
begin tran
if (select count(*) from key_generation_table where prefix = @prefix) = 0 begin
insert into key_generation_table (prefix, next_value) values (@prefix,1)
end
declare @next_value int
select @next_value = next_value
from key_generation_table
where prefix = @prefix
update key_generation_table
set next_value = next_value + 1
where prefix = @prefix
declare @string_next_value nvarchar(32)
select @string_next_value = convert(nvarchar(32),@next_value)
commit tran
select @result = @prefix + substring('000000000000000000000000000000',1,10-len(@string_next_value)) + @string_next_value
select @result
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN
DECLARE @ErrorMessage NVARCHAR(400);
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
SELECT @ErrorMessage = N'{' + convert(nvarchar(32),ERROR_NUMBER()) + N'} ' + N'%d, Line %d, Text: ' + ERROR_MESSAGE();
SELECT @ErrorNumber = ERROR_NUMBER();
SELECT @ErrorSeverity = ERROR_SEVERITY();
SELECT @ErrorState = ERROR_STATE();
SELECT @ErrorLine = ERROR_LINE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
END CATCH
Here's the key generation table...
CREATE TABLE [dbo].[Key_Generation_Table](
[prefix] [nvarchar](3) NOT NULL,
[next_value] [int] NULL,
CONSTRAINT [PK__Key_Generation_T__236943A5] PRIMARY KEY CLUSTERED
(
[prefix] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]