Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: portable mysql auto_increment

by rr (Sexton)
on Oct 24, 2002 at 21:55 UTC ( #207862=note: print w/replies, xml ) Need Help??


in reply to portable mysql auto_increment

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://207862]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2022-07-07 08:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?