A long time ago (in a galaxy far away) I had to solve this problem and had a dickens of a time with it.

The problem is that this number comes from the server, thus must be part of the databases API. Many databases have a feature for getting globally unique identifiers, you might try using those.

Basically, so long as there are transactions and row locking for the database you can probably do something like what I did. This should also work in databases that do *real* replication (ACID). This might have to be customized for each database type BTW.

Create a table called 'counters' that has a column 'tablename' and one called 'highwater'. Create an entry for each table you need counters for and set a highwater number.

In your programs you will have to set transaction isolation to serializable and take a row lock out on the row for the table you wish to have this psuedo auto_increment on. Read the number for the table, increment, update the row in the counter table and commit, then double check the table to ensure (just in case) that the number is unused. After alla that, unlock the row in the counter table.

This is a kludge but probably will work in any database with transactions and row/table locking. There might be race conditions, you'll have to be carefull with how you code it.

Good luck.

rr