Generate Sequence Records with Clientdataset (Multiuser)

Question

Guys I have the following scenario:

Order Table with Company, Numeropedido that are primary key. Other fields like Customer, Value, ect... I have an Sqlquery, a Datasetprovider and a connected Clientdataset pointing to this table.

In the Clientdataset Onbeforepost event I have a function that returns the order sequence.

If 2 users call post at the same time, I have primary key error.

I also tried to create a Rigger in the bank to generate this sequence, but it also gives primary key error.

What is the safest way to generate this sequence?

Answer 1

Your mistake is to use select Max() to increment the key. Since transactions see different versions of data until they are committed, each transaction will return the same value in Max(). That’s why you have a primary key error. And the same goes for triggers, as they only see the version of the data within your transaction. That is, they will give primary key error sooner or later.

For self-improvement, there are two strategies: The first and easiest is using sequences/Enerator. At Rigger you can write a code similar to what comes next:

if (new.id = 0) then
  new.id = gen_id(meu_generator,1);

The only problem is that you may not have a sequential number there, since every call to gen_id, you increment the Generator and lose a number (in case the transaction results in a rollback).

The second (and more complicated) strategy is to use a control table. The use would be more or less like this:

Você trava a tabela;
Resgata um número;
Libera a tabela.

And when canceling, you would do the reverse motion:

Você trava a tabela;
Devolve um número;
Libera a tabela.

The problem is that you run the serious risk of giving deadlock and the application take to respond.

As this is a request number, I think the most interesting for your case would be to use the first strategy, increasing the Quence in the saving process. And I would say more: do it on the app. When you change banks, it will be a hell of a job to take all the triggers out of the bank or toll them.

Answer 2

Well, after a few tests, I reached a point where I was able to solve it as follows:

I use a Trigger before Insert (Firebird) to generate the sequence. The code looks like this:

  SELECT COALESCE(MAX(NUMERO_PEDIDO) + 1,1) FROM PEDIDO
   WHERE EMPRESA = NEW.EMPRESA 
    INTO NEW.NUMERO_PEDIDO;

  IF (EXISTS(SELECT NUMERO_PEDIDO FROM PEDIDO       
              WHERE EMPRESA = NEW.EMPRESA
                AND NUMERO_PEDIDO = NEW.NUMERO_PEDIDO)) THEN
  BEGIN
    SELECT COALESCE(MAX(NUMERO_PEDIDO) + 1,1) FROM PEDIDO
     WHERE EMPRESA = NEW.EMPRESA 
      INTO NEW.NUMERO_PEDIDO;
  END

Interesting that if you leave only the first block to generate the sequence without the test and two or more people include a record of the primary key error.