Subject How to enforce sort order?
Author firebirdsql
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?