Thread Safe ID Allocation Across Multiple RDBMSs

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;


Why I Hate WebApps

December 31st, 2020

I have avoided deep diving web application development for a long time because the segment undergoes a lot of technological churn for really nothing.  IHMO web applications are better now than 10 years ago but they still do not rival the stability, performance, and usability of desktop applications.  In short Web 2.0 is a flop if you base your conclusion on widely available web applications. It’s hard to write a web application and get it right.  Right today, may not be right tomorrow since you have no control over the vehicle on which you’re hitching a ride; namely the browser. 

I was reminded of this again today when NoFrills redirected me to their flyer page in an email.  The page asked me to enter a postal code even though I was automatically logged into my profile and my preferred store was shown in the upper right.  I might have dismissed that, except the postal code entry control flashed on the page and disappeared.  You might think it was my browser, but I am using the latest FireFox and it did the same thing under Chrome.

I bank with TD Canada and their website often displays an error message after I log in.  If I login again, it is always successful, just the first time I quite often get the following error:

I needed to install CodeSite 5.2.2 so I could compile hcOPF under D7 and the EMBT portal provided the binary installer, but there was no corresponding serial under my registered products (who needs data integrity anyway).  So I decided to contact support, and went to their web page and chose the product and got an animated response. You may notice the disclaimer in white with a red background saying use another browser if you have issues with FireFox. I guess fixing it so it works in the remaining two browsers is too hard.

So if a basic entry form is too difficult to get right in 2020, I don’t think the web has progressed all that much since the 90s. Mind you the 90s rocked! Hopefully the 2020s will be better on all fronts despite the horrific beginnings.

Testing Options for Delphi

December 3rd, 2020

Depending on the version of Delphi in use, unit/integrate testing tools include:

DUnit
-the original framework with the latest version of 9.4.  Embarcadero stills ships this with Delphi.

DUnitX
-released by VSoft Technologies (makers of FinalBuilder) and requires D2010 or higher
-uses attributes to mark test related methods

DUnit2
-a fork of DUnit started by Peter McNab
-introduces SetupOnce/TearDownOnce and other enhancements
-has been extended by tiOPF project

SmokeTest
-newer framework for Joylon Smith, recently released that uses Interfaces rather than abstract classes

DUnitLite
-an extension to DUnit offering more check methods with additional datatypes, and a fluid interface

OpenCTF
- an extension to DUnit to provide testing for components

I assume most people using newer versions of Delphi are using DUnitX, and that most people also use TestInsight to ensure their tests are executed when code changes are made.  What framework do you use and why?

If I missed any, please let me know.

hcOPF building blocks - Application Settings

November 2nd, 2020

I just checked in some additional code to provide database driven hierarchical Program Settings using the hcOPF framework for data access.  The Settings code can be found under the Source/UI/Common/Settings folder for domain objects and other non-visual code, including database scripts for Firebird.  The actual program Settings dialog is located in Source/UI/VCL and only a VCL version is available at this time.

It should be simple to incorporate this into a VCL application, especially one already using Firebird.  If you run the Application Settings.sql script it should create all the necessary database structures.  Then you need to populate the SettingsCategory and Settings tables.  To integrate the ProgramSettings dialog, use the unit and call it’s Execute method with the Settings list and current security level. Keep in mind that the settings key value must be unique across all settings. While a path like key may work, if you re-organize the setting hierarchy the key will no longer make sense.

At some point I would like to provide a “Designer” mode into the Dialog that would allow you to interactively create or modify the setting hierarchy.  For now, scripts to build it will have to suffice.  Keep in mind that this is the first release of code I wrote a long time ago.  It has worked well for the last 10 years, but it needs to be tightened up (more database constraints, a review of code for better performance/less overhead).  When time permits I intend on improving it for use on future projects.

C# features I wish Delphi Had

October 2nd, 2020

In C# 4 Named and Optional Arguments were introduced to allow developers to supply only the arguments they wish to in a method call.  Similar to Objective C, this makes the method call more descriptive and shorter.  I have often wished I could use this kind of syntax when calling a method with a Boolean parameter.  Reading the method call with a True or False argument doesn’t tell you  what the meaning of the argument is.  Something like  UpdateBusinessObject(TriggerEvents: False) is much more meaningful. The only alternative in Delphi is to define a 2 element enumerated type and pass that rather than a boolean.

The New modifier to explicitly deal with hiding of members from ancestor classes.  That includes properties which if declared public in Delphi cannot be hidden by using any trick such as re-declaring a property with only a getter.

String interpolation is also a nice feature of C# 6.  It certainly beats updating all the index values for Format() when adding an additional format specifier.

What C# features do you like that Delphi could use?

Pain Ergo Ergonomics

September 30th, 2020

As a programmer I spend far to much time in front of a screen, and as a result I’ve suffered from my share of back, neck and wrist pain.

For the last several weeks I’ve been suffering from a repetitive strain injury on my dominant wrist thanks to all the mousing I do.  Icing the wrist, and resting it as much as possible helped, but it was just a roller coaster ride in terms of the pain returning.  After all, it’s not as if you can just stop working.

I was in one of my favourite stores; MemoryExpress a local computer chain, when I noticed the sales rep was using a bizarre looking mouse.  I asked him about it, showing my wrist brace, and he said it was great and really helped prevent my kind of inquiry.  I checked it out on their website, and after the pain increased again a couple days later, I broke down and bought it.

The Logitech MX Vertical mouse is not cheap at $120 CAD, but already my wrist is feeling better and it’s only been a day using the new mouse.  You can easily spend several hundred dollars on physio, or taking time off so why not spend it on a more ergonomic mouse that helps keep you healthy.  Highly recommended!

Installing Firebird 3.06 on Ubuntu 18.04.5 LTS

September 21st, 2020

Thanks to my inexperience with the Ubuntu package manager, and my talent for breaking software, I managed to trash my Ubuntu installation recently to the point where it wouldn’t even boot.  It gave me the opportunity to test my backups, and I’m happy to say that re-installing Ubuntu with a restore of data, with some re-installation of apps got me pretty much back to where I was.  I did however, encounter an issue where Ubuntu would not login to the GUI.  Thankfully I eventually found this post, which solved the problem.

The original issue started around the installation of Firebird 3.0.5 for Linux. It appeared the installer needed a file libtommath.so.0 which was not present on my distro.  I thought I had gotten it installed properly, then I ran into the infamous “COLLATION UNICODE_CI_AI for CHARACTER SET UTF8 is not installed” error.  I managed to get past that, but when I opened the database in an FMX app using FireDAC in local mode I got an exception “N8Firebird16status_exceptionE”.   FlameRobin would open it without issue.  If I chose Break in the exception dialog it looked for the source to libstdc++.so.6. The exception was in a call to isc_attach_database or isc_dsql_fetch.  I assumed it was an incompatibility between FireDAC and the standard C++ library.

It turns out I had gotten myself into a mess trying to get the libtommath file.  I broke some package references and when I tried to fix them, a slew of packages were removed.  I was warned not to continue unless I knew what I was doing, but when has that stopped me?  Also there didn’t seem to be any alternative to fix the package manager’s complaint that things were broken even if everything still seemed functional.  Ah live and learn…

This time I tried installing Firebird 3.06 thinking it was newer and might address some of the problems I had encountered….nope.  This time I used a symlink ( sudo ln -sf /usr/lib/x86_64-linux-gnu/libtommath.so.1 /usr/lib/x86_64-linux-gnu/libtommath.so.0 ) to resolve the missing libtommath.

Then I got a completely new error in the FMX deployment code.  It attempted to create a hidden folder “/home/<username>/.fmxlinux” which contains “libfmux-1.48.so”.  I did not invoke paserver with sudo so I didn’t have adequate permissions and got an EFCreateError exception with an empty message triggered by line 9345 of System.Classes.  I only mention that because the message should contain the filename being created and the OS error message but it did not.

After resolving the permission issue, I re-confirmed that even on my fresh Linux/Firebird install, I still get an exception when attempting to access my database, which of course pertains to the missing collation.  Since I didn’t make note of how I resolved the collation error when I installed 3.05 I can’t be sure what I did, or whether the “resolution”  did not truly work.

Reading the “Release notes for Firebird 3.06″ I saw they had upgraded the ICU libraries to v52.1 so I downloaded the package and installed it.  Re-starting Firebird the collation error was resolved and neither FireDAC or FlameRobin reported the “N8Firebird16status_exceptionE” error any longer.  Obviously whatever I did to fix the collation error when I installed Firebird 3.05 was not correct despite the fact I was no longer getting the collation exception.

I hope this helps someone else install FireBird 3.06 on Ubuntu/Debian and saves them all the time I spent searching for solutions to the myriad of issues I encountered including my own folly.

I Just Don’t GetIt

September 17th, 2020

In case you were wondering, GetIt appears to be down at the moment.  I went to take 5 minutes to update a bug report, and I needed to install FmxLinux to do so, only to see:

GetIt Not Accessible

GetIt Not Accessible

I guess it will have to wait.  This is the problem of automating everything.  You become reliant on services that need to be up 24×7 or the odds are they will impede your work at some point.

UPDATE:  GetIt is back up now.

Ubuntu is better with WINE

September 15th, 2020

It’s been a couple of years since I made the jump to Ubuntu 18.04 LTS as my primary operating system, thanks in no small part to Windows 10 updates and BODs.

It all started when I loaded my Toshiba Satellite L70D laptop with an SSD and Ubuntu to try to extend its usable lifetime. To give you an idea how old it is, the laptop shipped with Windows 8.1 on it, and I bought it because it was one of the few 17″ laptops still available at the time.  I would have bought a 17″ Macbook Pro after my 15″ died, but it seemed Apple

and other manufacturer’s were no longer making 17″ laptops.  Now you can find numerous 17″ PC laptops aimed at the gaming market.  Anyway, the laptop ran well and I discovered I could run VMs using VirtualBox reliably.  I had been using VMWare Fusion before, and had tried earlier versions of VirtualBox with no success.

I had been looking for a lean OS on which to run numerous VMs.  I was considering OS/X, but there is definitely a cost premium for the hardware, and the choices available are more limited than the clone market.  I even thought about a Hackintosh but didn’t really want to explore EULA violations and support issues for updates.

After discovering all the major software I was using had Ubuntu supported versions, I decided to take the jump and load it on my new (at the time) desktop.  While I ran into a few VirtualBox issues along the way I have never lost any data so far, and would have to say that my experience rivals the commercial VM software I have used.  I’ve also had no hardware support issues like I had experienced previously when trying out Linux distros.

Linux seems to have matured enough that even a noob with a little Googling can find the solution to any issue or question I have had thus far.  Of course I chose Ubuntu because it is one of the largest distros, so it’s a relatively safe choice, but other distros I played with seemed just as viable.

Recently I wanted to see if a personal VCL project would run natively under Ubuntu.  I was contemplating making an FMX version just to have it natively on Linux without using a VM.  The app uses a dynamic plug-in architecture with run-time packages.  It is based on code I originally received from Mark Miller; the author of Coderush for Delphi and DevExpress’ Coderush for .NET.  The project also uses Firebird 3.0 embedded, and the VirtualTreeView in grid mode, since it’s open source and lightning fast.

I tried CrossVCL’s VTreeView demo only to find out that it is indeed a work in progress.  It’s VTreeView support has some painting issues and run-time exceptions were thrown so I knew I needed to find another way to get my VCL app onto Ubuntu without using VirtualBox.  The other night I thought I would see if I could get it to work under WINE.  I installed Q4Wine from the Ubuntu software app, and added my application EXE with the default settings.  Much to my surprise, it fired right up.  I even managed to make an Ubuntu shortcut and add it to my favorites.

CompositeApp running on Ubuntu 18.04LTS with WINE

Embedding Firebird 3.0X

September 1st, 2020

With Firebird 3.0X releases there is no longer a separate install for embedded deployments.  In fact the guidance essentially is to use the ZIP package and customize it as desired.  If you want a minimal deployment, it’s difficult to know what files are absolutely necessary, unless you are very familiar with the project.

I recently had a need to distribute an application for testing, and didn’t want the users to have to install the Firebird server.  The target audience is also well suited to an embedded install, so I thought I would try it out.  With the help of ProcessMonitor and the aforementioned guidance documentation I discovered that the following files seem to be the bare requirements:

firebird.conf
databases.conf
plugins.conf
msvcr100.dll
msvcp100.dll
icuuc52.dll
icuin52.dll
icudt52l.dat
icudt52.dll
ib_util.dll
fbclient.dll
plugins\engine12.dll
plugins\fbtrace.dll

I am using Local DB access via FireDAC with Delphi 10.4 and tested this configuration by simply unzipping the files including my app, and the database into a Win7 VM and running it.  Firebird weighs in at about 16Mb where my app and it’s supporting DLLs are 40Mb with Debug info.