Sort order sequences does not work because it cannot have gaps. Do you have a link to the specific faq article that shows how to deal with gaps?

--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:
>
> On Fri, Jul 13, 2012 at 8:26 AM, firebirdsql <firebirdsql@...> wrote:
>
> > 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);
> >
>
> Aside from the problem that you're reading every record in the table to
> store a new record which will be tedious when you get a lot of records in
> the table, that procedure will fail consistently if run in a concurrent
> environment. The problem is that Firebird's concurrency control mechanism
> is MVCC, under which a transaction does not see the changes made by
> concurrent transactions. So if two concurrent transactions count the
> number of rows in a table and store a new row containing the value returned
> by the count, they will produce duplicate results from time to time.
> Firebird provides an alternate mechanism for producing unique values called
> sequences (new name) or generators (old name). It's cheap and reliable.
>
> >
> > If I call this stored procedure 1000 times asynchronously, some of the
> > numbers on sort_order are duplicates.
> >
>
> Yes.
>
> >
> > How do I enforce the sort_order so that there are no gaps and no
> > duplicates?
> >
>
> A unique key will prevent duplicates. Preventing gaps is harder, but is
> covered in the Firebird FAQ.
>
> >
> > BTW, I thought the stored procedure was supposed to execute in a
> > transaction which locks the table until it completes?
> >
>
> You can start a transaction in a mode which will lock the table
> (SERIALIZABLE), but the default transaction mode provides record level
> concurrency control.
>
> Good Luck,
>
> Ann
>
> >
> >
>
>
> [Non-text portions of this message have been removed]
>