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

In reply to Re: portable mysql auto_increment by rr
in thread portable mysql auto_increment by schweini

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.