portable mysql auto_increment

[schweini]

hey, ho!

i've grown quite fond of mysql's auto_increment thingy (even though i fear that some sql-purists will point out that that's not the best way to do things).
now i've been looking at other databases (postgresql, in particular), and even though i'm not planning on moving to one of them any time soon, i'd really like to keep my options open.
i know that it's possible to "emulate" auto_increment on, e.g. postresql, but i was just wondering whether anybody can think of an elegant (maybe even drop-in?) solution that would allow the usage of auto_increment with any DBD....
cheers,
-schweini

[mpeppler]

The proper way to do this without using database dependent extensions like auto_increment, identity columns, etc. is to create a sequence table and access it within a transaction.

In pseudo code it would look something like this:

begin transaction

update sequence_table set next_key = next_key + 1 
 where sequence_name = 'somename'
 
select next_key from sequence_table
 where sequence_name = 'somename'

commit transaction

The update acquires an exclusive lock on the row which will be held for the duration of the transaction. You then query the table to get the current value of next_key for this sequence. Once that value has been retrieved you commit the transaction, thus releasing the lock on the row.

This should (assuming the database engine handles transactions properly) guarantee that each client requesting a sequence value gets a unique value.

With Sybase (or MS-SQL) the whole sequence can be run as a single prepare()/execute() block. For other systems you probably simply want to set AutoCommit to false, do the update and then the query, followed by a $dbh->commit().

Note that this technique will create a hot-spot in your database server if you have a lot of request for new key values.

BTW - you can read a paper on the various key generation techniques for Sybase and/or MS-SQL servers at http://my.sybase.com/detail?id=860

Michael

[adrianh]

You might want to take a look at DBIx::Sequence, which does a similar thing to what you want in a vaguely cross-platform way.

From the pod...

This module is intended to give easier portability to Perl database application by providing a database independant unique ID generator. This way, an application developer is not bound to use his database's SEQUENCE or auto_increment thus making his application portable on multiple database environnements.

This module implements a simple Spin Locker mechanism and is garanteed to return a unique value every time it is called, even with concurrent processes. It uses your database for its state storage with ANSI SQL92 compliant SQL. All SQL queries inside DBIx::Sequence are pre cached and very efficient especially under mod_perl.

[mpeppler]

I took a quick look at that module and off-hand I don't see how it can guarantee to avoid race conditions.

I'd be careful of any module that doesn't use transactions to guarantee single threaded access to a sequence table.

Michael

[adrianh]

Code looks solid to me. Can you point out where you think it would fail?

I admit it's a bit of a pain to have to deal with an AutoCommit database handle, but if you're porting stuff between MySQL and other DBs I've found it a handy tool.

(not saying that the transaction approach is bad, quite the opposite - just that DBIx::Sequence is useful if you have to move between databases without decent transaction/sequence support and those with.)

[rr]

Hi there,

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