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]