Hi,

>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
http://www.upscene.com

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