Re: How to create "auto-increment" field WITHOUT a sequence object?

On Thu, Jun 30, 2011 at 12:40 PM, Dmitry Koterov <dmitry(at)koterov(dot)ru> wrote:
> Hello.
> I need to create an auto-increment field on a table WITHOUT using sequences:
> CREATE TABLE tbl(
>   name TEXT,
>   uniq_id INTEGER
> );
> Each INSERT to this table must generate a new uniq_id which is distinct from
> all others.

Do they need to be in order? It looks like you only need a few since
the range you mention is 1 to 100000. you could put those numbers in
another table. Then in a transaction you grab a number from the
table, try to delete it. If it's not there you lost a race condition,
exit and try it again. If you can delete it then you try the insert
transaction. If it fails things roll back and the lock on the row is
released. If the transaction works you commit the whole thing.

Are the transactions really long running?