Friday 10 May 2013

SQL AutoIncrement without using an IDENTITY Column Plus its Concurrent and Thread Safe with MIN - MAX



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