| Subject | How to enforce sort order? |
|---|---|
| Author | firebirdsql |
| Post date | 2012-07-13T12:26:24Z |
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?
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?