Its been a while since I have last blogged so it here it
goes again.
I plan to do a series of blog entries on SQL concurrency safe inserts / updates and selects. Also how to trace and avoid deadlocks.
This blog entry though discusses various methods of obtaining an
autoincrementing id without creating a table and having an identiry column.
Also this approach allows you to create a pool of ids. Or specify a minium and maximum id. Why would you need that
you might ask? Well when it comes to concurrency and transactions/deadlocks
this is very handy. This method can be used to avoid deadlocks. More about this
later in this blog.
Now what do you need to do.
Well its quite simple actually and there are 2 approaches.
One way is to
DECLARE
@SettingValue as int
WHILE 1 = 1
BEGIN
UPDATE
[MicroangeloAutoIncrement].[dbo].[ClientSettings]
SET @SettingValue =
[SettingValue] = [SettingValue] + 1
WHERE
[Setting] = 'Client'
--TESTING Duplication Hence Verifying
Unique ID by inserting id in a primary table
INSERT INTO
[MicroangeloAutoIncrement].[dbo].[ClientSettingsTest]
([ClientSettingsTest])
VALUES
(@SettingValue)
PRINT @SettingValue
END
You can test this as described above by running the above
query in 2 or more windows. This it the result in one window
(1 row(s) affected)
80102
(1 row(s) affected)
(1 row(s) affected)
80104
(1 row(s) affected)
(1 row(s) affected)
80106
Another way is listed below. I like this method because you
can adapt it to control the minimum and maximum setting value.
DECLARE @Update as bit
DECLARE
@CurrentSetting as int
DECLARE
@NewSetting as int
WHILE 1=1
BEGIN
SET @Update = 0
SELECT @CurrentSetting = [SettingValue]
FROM [MicroangeloAutoIncrement].[dbo].[ClientSettings]
WHERE [Setting] = 'Client'
WHILE @Update = 0
BEGIN
UPDATE
[MicroangeloAutoIncrement].[dbo].[ClientSettings]
SET [SettingValue] =
@CurrentSetting + 1
,@Update = 1
,@NewSetting =
@CurrentSetting + 1
WHERE [Setting] = 'Client'
AND
[SettingValue] = @CurrentSetting
IF @Update = 0 -- UPDATED BY ANOTHER
THREAD
BEGIN
SELECT
@CurrentSetting = [SettingValue]
FROM
[MicroangeloAutoIncrement].[dbo].[ClientSettings]
WHERE [Setting] = 'Client'
END
END
INSERT INTO
[MicroangeloAutoIncrement].[dbo].[ClientSettingsTest]
([ClientSettingsTest])
VALUES
(@NewSetting)
PRINT @NewSetting
END
You can test this as described above by running the above
query in 2 or more windows. This it the result in one window
(1 row(s) affected)
167970
(1 row(s) affected)
(1 row(s) affected)
167972
(1 row(s) affected)
(1 row(s) affected)
167974
Ok back in the beginning of the blog post I said you might
need a pool of identities to avoid deadlocks. When will you need to do that you
might ask? Well let me give you an example.
Let say for instance you have a client who does
multithreaded calls to your database via wcf /web exposed service. This exposed
service runs in a database transaction and you may need to insert a record and
update another record within a database transaction.
If different threads access the same primary id then you can
get deadlocks. How can you fix this?
Well you can assign multiple primary ids
i.e. a pool of ids with autoincrementing on each thread and a minimum maximum ud. This gives a pool of ids for the single client and can be done by using and modify the autoincrement code above to fix
the issue.
Here is an example of what I mean.
CREATE PROCEDURE [dbo].[sp_RetrieveWebAccountNumber]
(
@WebClientName as varchar(100)
,@CurrentAccountID as int OUTPUT
)
AS
BEGIN
DECLARE @ID as int
DECLARE @MinAccountID as int
DECLARE @MaxAccountID as int
DECLARE @Update as bit
DECLARE @NewAccountID as int
SELECT @ID = [ID],
@CurrentAccountID = [WEBAccountNumber], @MinAccountID =
[WEBAccountNumberMin], @MaxAccountID = [WEBAccountNumberMax]
FROM [AccountSettings]
WHERE [WEBClientName] = @WEBClientName
IF @ID IS
NULL
BEGIN
SET @CurrentAccountID = -1
RETURN
END
SET @NewAccountID = @CurrentAccountID +
1
IF @NewAccountID > @MaxAccountID SET
@NewAccountID = @MinAccountID
SET @Update = 0
WHILE @Update = 0
BEGIN
UPDATE [AccountSettings]
SET
[WEBAccountNumber] = @NewAccountID, @Update = 1
WHERE [ID] = @ID AND
[WEBAccountNumber] = @CurrentAccountID
IF @Update = 0 -- UPDATED BY ANOTHER
THREAD
BEGIN
SELECT @ID = [ID],
@CurrentAccountID = [WEBAccountNumber], @MinAccountID =
[WEBAccountNumberMin], @MaxAccountID = [WEBAccountNumberMax]
FROM
[AccountSettings]
WHERE
[WEBClientName] = @WEBClientName
SET @NewAccountID = @CurrentAccountID +
1
IF @NewAccountID > @MaxAccountID SET
@NewAccountID = @MinAccountID
END
END
-- Return the result of the function
RETURN
END
You will need to have a pool count greater than the maximum number of threads to avoid deadlocks. Essentialy each id is serviced by each thread. Hence no more deadlocks.
So there you have it a multi threaded autoincrement stored
procedure with min and max. Plus its thread safe.
Enjoy
