Archive for January, 2021

Thread Safe ID Allocation Across Multiple RDBMSs

Wednesday, January 13th, 2021

Believe it or Not, I have seen enterprise applications that simply perform a ‘select max(ID)+1 as NextID from <TableName>‘ in SQL against their RDBMS to get each and every ID value.  While this works, in a multi-threaded application, or on a busy SQL server this causes a lot of network traffic.

However, the biggest problem with this approach is that it doesn’t prevent a ‘primary key violation’, since two applications may query the database at the same time and be provided with the same max ID value.  When they both go to write their records, the first writer wins, and the second one gets an exception.  Unless your application traps the error and gracefully handles such a situation (retries everywhere?), the application fails.

Now ID Allocation is such a fundamental building block you would think applications built as part of a suite would use the same code for ID allocations, and that such potential problems would not be present in successful commercial apps that support both Oracle and Microsoft SQL Server.  Guess again!

Oracle has sequences so often ID values are generated on insert, which complicates establishing master/detail relationships.  It is easier to know what ID values are going to be generated for the records especially when the client applications use dynamic SQL.  Microsoft SQL Server has identity values which have their own drawbacks, so an earlier decision had been made to avoid them.  SQL Server has only recently adopted sequences, so supporting databases on older versions still necessitates a different approach to an RDBMS agnostic method of allocating IDs.

What we decided to do is provide the application with permissions to enable it to modify the next value of a sequence on Oracle, and create a table in SQL Server to manage the ID values used there with the following structure:

CREATE TABLE [dbo].[IDAllocation](
    [TableName] [varchar](30) NOT NULL,
    [LastUsedIDValue] [int] NOT NULL,
    CONSTRAINT [PK_IDAllocation] PRIMARY KEY CLUSTERED ([TableName] ASC)
)

Then I created a new interfaced class to implement the ID allocation client code. Since we needed to prove this approach would work in production, I implemented it for a subset of tables that were populated with multi-threaded code I developed, which exposed the flaws in the previous approach to ID allocation. This code creates a server side ADO query that updates the ID value and returns the next one:

unit IDAllocation;

interface

type
  IIDAllocation = interface['{53F7BA50-0D72-4AC1-AB9B-6E5210DD4543}']
    function GetNextId(const TableName: string; IncStep: integer = 1): integer;
  end;

  TIDAllocation = class(TInterfacedObject, IIDAllocation)
  private
    //an interface that provides the database connection and queries using that connection
    FDataSession: IDataSession;
    FQuery: IQuery;
    FTablesSupported: TStringList;
  public
    /// <Summary>
    /// Get the Next ID value for the table specified.
    /// </Summary>
    ///
    /// <param name="TableName">The table for which new ID(s) are needed.</param>
    /// <param name="IncStep">The number to add to the LastUsedIDValue.
    /// In order to reserve a range of IDs just set this increment to
    /// the largest value desired (ie: dataset recordcount) and then
    /// decrement the value for subsequent ID values.
    /// </param>
    ///
    function GetNextId(const TableName: string; IncStep: integer = 1): integer;
    constructor Create(DataSession: IDataSession); reintroduce;
    destructor Destroy; override;
  end;

implementation

uses
  System.SysUtils, Data.Win.ADODB;

constructor TIDAllocation.Create(DataSession: IDataSession);
begin
  inherited Create;
  FDataSession := DataSession;
  FQuery := FDataSession.CreateServerQuery(utResults, True);
  FTablesSupported := TStringList.Create;
  FTablesSupported.Sorted := True;

  // currently only some tables are supported for use with IDAllocation
  FTablesSupported.Add('Table1');
  FTablesSupported.Add('Table2');
end;

destructor TIDAllocation.Destroy;
begin
  FTablesSupported.Free;
  inherited;
end;

function TIDAllocation.GetNextId(const TableName: string; IncStep: integer = 1): integer;
const
  MSSQL_UpdateSQL
    : string = 'set nocount on; update IDAllocation set LastUsedIDValue = ' +
    '(select IsNull(IDA.LastUsedIDValue,0) + %0:d as ID from IDAllocation IDA where IDA.TableName = ''%1:s'' )'
    + 'where TableName = ''%1:s'' ' +
    ';select LastUsedIDValue from IDAllocation where TableName = ''%1:s'' ';
var
  sOwner: WideString;
  Command: TADOCommand;
  Recordset: _Recordset;
begin
  Result := -1; // compiler warning
  // since this is only available for some tables we must fallback to the "classic" generation
  if (FTablesSupported.IndexOf(TableName) = -1) then
  begin
    FQuery.SQL.Text := Format('select max(ID) as ID from %s', [TableName]);
    FQuery.Open;
    if FQuery.Fields[0].IsNull then
      Result := IncStep
    else
      Result := FQuery.Fields[0].AsInteger + IncStep;
    FQuery.Close;
  end
  else
  if FDataSession.IsOracle then
  begin
    if IncStep <> 1 then
    begin
      FQuery.SQL.Text :=
        Format('select TABLE_OWNER from user_synonyms where Table_name = upper(''%s'') ',['s_' + TableName]);
      FQuery.Open;
      assert(not FQuery.EOF, 'The owner of the sequence could not be found');
      sOwner := FQuery.Fields[0].AsString;
      FQuery.Close;

      FQuery.SQL.Text := Format('alter sequence %s.s_%s increment by %d',[sOwner, TableName, IncStep]);
      FQuery.ExecSQL;
    end;

    FQuery.SQL.Text := Format('select s_%s.NextVal from dual', [TableName]);
    FQuery.Open;
    Result := FQuery.Fields[0].AsInteger;
    FQuery.Close;
  end
  else
  if FDataSession.IsMSSQL then
  begin
    Command := TADOCommand.Create(nil);
    try
      Command.ConnectionString := FDataSession.ConnectionString;
      Command.CommandText := Format(MSSQL_UpdateSQL, [IncStep, TableName]);
      Command.CommandType := TCommandType.cmdText;
      Command.ExecuteOptions := [];
      Recordset := Command.Execute;
      if Recordset.Fields.Count > 0 then
        Result := Recordset.Fields[0].Value
      else
        raise Exception.Create('No Recordset Returned!');
    finally
      Command.Free;
    end;
  end;
end;