Subject Re: [firebird-support] How to enforce sort order?
Author Martijn Tonies

>I have the following stored procedure (simplified) for adding a new record:
> SELECT COUNT(*) FROM table INTO :number;
> INSERT INTO table(sort_order) VALUES(:number+1);
> If I call this stored procedure 1000 times asynchronously, some of the
> numbers on sort_order are duplicates.
> How do I enforce the sort_order so that there are no gaps and no
> duplicates?
> BTW, I thought the stored procedure was supposed to execute in a
> transaction which locks the table until it completes?

Tables are not locked.

If you want unique numbers, use a GENERATOR:

create generator mygenerator;

insert into table(sort_order) values (gen_id(mygenerator, 1));

With regards,

Martijn Tonies
Upscene Productions

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!