ReadConsistency + strictly monotonic, continuous serial number + max+1 issue

[Attila Molnár]

Hi *!

We have the following logic in many places (executed on read commited transaction)

EXECUTE...

BEGIN

  //do work

  ...

  //try to get a record lock with busy wait

  ...

  //max+1 SELECT to get a strictly monotonic serial number (lock + read commited guarantees that there is no hole in the serial)

  ...

  //write data using new serial number

  ...

END

with ReadConsistency ON, now the max+1 might return a duplicate value.

How do you write a single proc/block where you can generate serial numer which is strictly monotonic with no hole, and max+1 never generates duplicate?

Thank you!

[Svein Erling Tysvær]

Maybe the 22 year old Auditable_Series.rtf document in http://www.ibobjects.com/docs/ti_AuditableSeries.ZIP may be of some help to you. It used to be the standard answer for similar questions where law (or similar) required unbroken number sequencing.

HTH,

Set

--
You received this message because you are subscribed to the Google Groups "firebird-general" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-gener...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-general/836847de-cf7e-43ce-ae78-f874cd26f7b6n%40googlegroups.com.

[Attila Molnár]

Hi Set!

Thanks. The linked document offers no solution to tihs problem. We use a single EXECUTE command for all the steps. This is fine with ReadConsistency OFF, but not good for ReadConsistency ON because of the snapshot behaviour.

[Dmitrii Kuzmenko]

Hello!

 

How you lock record?

 

p.s. anyway, max+1 in relational databases is a bad idea for multiuser environment.

 

10.03.2023, 10:58, "Attila Molnár" <amolna...@gmail.com>:

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-general/b9299e43-4c6d-4092-aff0-3c9aacd8105bn%40googlegroups.com.

 

 

-- 

Sincerely,

Dmitry Kuzmenko

 

[Attila Molnár]

Dmitry, lock + max+1 on read commited works well, even in multiuser environment, no issue there: can't get duplicate, no missing value (from Firebird 2.0-3.0)

The mechanism below used ~10 years without issues, it's battle tested.

helper table

GL_NEXT_VALUE : table_name ~; fields : gropuing fields for the serial nuber; field_values: values for the group

helper "lock" procedure pseudo

procedure lock(table_name, fields, field_values)

select from gl_next_value where table_name = :table_name AND fields = :fields AND field_values = :field_values WITH LOCK into table_name

if (table_name is not null) then exit;

in autonomous transaction

begin

insert into gl_next_value(table_name)

end

execute procedure lock(table_name, fields, field_values);--call itself to get the lock

helper "next_value" procedure pseudo

procedure next_value(table_name, fields, field_values) returns (next_val)

rep = true;

while (rep)

do begin

  begin

    execute procedure lock(table_name, fields, group_value);

    rep = false;

  when any

  do begin

    --swallow lock and retry, wait for concurrent transaction to finish (busy wait)

  end

end

execute statement 'select (max+1) from ' || table_name || ' where ...' into next_val;

usage, e.g. in trigger

execute procedure next_value('TABLE_NAME', 'GROUP_FIELD', NEW.GROUP_FIELD) into next_val;

[Roman Simakov]

1. Consider automonous transaction as a transaction executed out of
your code. You CANNOT see it's result and should not rely on it.
2. In your case I cannot see where max is updated.
3. I reckon you have restart after an update conflict and may be it
leads to double increase. Please prepare the isolated example to be
reproduced (both sql scripts and comments with isql)

пт, 10 мар. 2023 г. в 12:18, Attila Molnár <amolna...@gmail.com>:

> To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-general/a39e47d4-334b-4061-bd5b-5d1ec3638c95n%40googlegroups.com.



--
Роман Симаков

[Attila Molnár]

1. by definition read commited isolation allows see other transactions commited data. (I unserstand the snapshot behavoiur for a statement with ReadConsistency = 1. It was unexpected that at EXEC command considered a single statement. Form my POV EXEC is a server side script, I expected that the individual command s inside the EXEC are treated as a comman.)

2. from the insert command which leads to a trigger activation what calls the next_value procedure

3. double inrease not possible beause of the max+1 select and unique constraint protection

[Attila Molnár]

3. I don't update, just insert, update conflict not possible. double max+1 was avoided by lock + conflict error catch + retry. With ReadConsistency = 0 retry was possible on server side within EXEC. With ReadConsistency = 1, you are forced to return to client side to do a restart.

On Friday, 10 March 2023 at 14:25:45 UTC+1 roman....@gmail.com wrote:

[Attila Molnár]

Attached an example database .

You can run the script concurrently (on read commited) on many threads and get a strictly monotonic and continuous serial_no for each group ('ABC', 'XYZ') for the "stuff" table.

The backing up gl_next_* procedures and table take care of the locking, lock handling and max+1.

This not works with RC ON, as you have to return to the client and restart statement to able to read the new max.

EXECUTE BLOCK
AS
DECLARE VARIABLE i INTEGER = 0;
DECLARE VARIABLE j INTEGER = 0;
BEGIN
  WHILE (i < 10)
  DO BEGIN
    IN AUTONOMOUS TRANSACTION
    DO BEGIN
      j = 0;
      WHILE (j < 1000)
      DO BEGIN
        INSERT INTO stuff(group_field)
        VALUES('ABC');
        INSERT INTO stuff(group_field)
        VALUES('YXZ');
        j = j + 1;
      END
    END
    i = i + 1;
  END
END

[Roman Simakov]

пн, 13 мар. 2023 г. в 12:24, Attila Molnár <amolna...@gmail.com>:

>
> 1. by definition read commited isolation allows see other transactions commited data. (I unserstand the snapshot behavoiur for a statement with ReadConsistency = 1. It was unexpected that at EXEC command considered a single statement. Form my POV EXEC is a server side script, I expected that the individual command s inside the EXEC are treated as a comman.)

Actually EXEC is a request but not a set of individual commands. The
request is always executed in its own data snapshot and see the same
data.

>> --
>> Роман Симаков

>
> --
> You received this message because you are subscribed to the Google Groups "firebird-general" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-gener...@googlegroups.com.

> To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-general/b76c19a7-857d-4259-9582-1c1bac19e2b5n%40googlegroups.com.



--
Роман Симаков

[Kjell Rilbe]

If it's possible for you (I didn't invest quite enough time to fully
understand your use case), you could have a separate table with the
serial number in a single record (or one record per number sequence if
that's needed). Then to reserve a new number you would do this within a
transaction with reasonable isolation level (e.g. read committed):

1. START TRANSACTION

2. UPDATE SER_NO_TABLE SET SER_NO = SER_NO +1 -- possibly adding WHERE
SER_NO_ID = :id_for_the_desired_ser_no_sequence

3. SELECT SER_NO FROM SER_NO_TABLE -- possibly adding WHERE SER_NO_ID =
:id_for_the_desired_ser_no_sequence

4. COMMIT;

In step 2 you will obtain a write lock on that record, which will
prevent any other transaction from also reserving a new number. In step
3 you will be guaranteed to get your newly reserved serial number, since
no other transaction will be able to update it in between.

This could probably be done within the same transaction as the one your
other work is being done, but in that case there will be a risk of
deadlock I suppose, if you do things in the wrong order, e.g. one
transaction trying to reserve new number for sequence A then sequence B,
while another transaction does it the other way: first B then A.

This scheme also avoids performance problems with selecting max - not a
good idea for large tables (meaning "many records"). You also have the
possibility, if needed, to use the same sequence across multiple tables.
For example, in the O/R framework from which I got the idea they use a
single sequence for object id:s for all tables in the entire database.
Since some classes are split up across multiple tables this does make
sense in a way.

Best of luck!

Kjell Rilbe

[Mark Rotteveel]

On 18-03-2023 08:29, Kjell Rilbe wrote:
> If it's possible for you (I didn't invest quite enough time to fully
> understand your use case), you could have a separate table with the
> serial number in a single record (or one record per number sequence if
> that's needed). Then to reserve a new number you would do this within a
> transaction with reasonable isolation level (e.g. read committed):
>
> 1. START TRANSACTION
>
> 2. UPDATE SER_NO_TABLE SET SER_NO = SER_NO +1 -- possibly adding WHERE
> SER_NO_ID = :id_for_the_desired_ser_no_sequence
>
> 3. SELECT SER_NO FROM SER_NO_TABLE -- possibly adding WHERE SER_NO_ID =
> :id_for_the_desired_ser_no_sequence
>
> 4. COMMIT;

Step 3 can be avoided by using UPDATE ... RETURNING SER_NO

Mark
--
Mark Rotteveel

[Attila Molnár]

Kjell, the UPDATE approach was the 1.0 version, but that become a performance issue due to the long record version chains.