Did you read this reply I send earlier? I think it does what you
want without needing to "lock" anything, thus making it portable.
> > >> Damn, I found out that I need table locking *and* transactions.
> > >
> > > What makes you say that?
> > BEGIN TRANSACTION
> > SELECT MAX(id) FROM table
> > INSERT INTO table (id) VALUES (?)
> > INSERT INTO othertable (id) VALUES (?)
> > COMMIT
> > First I find a new id value, then I do several INSERTs that need to be
> > atomic, and especially roll back completely if a later one fails.
> > > That Perl module uses the exact technique I described to you with
> > > updates and LAST_INSERT_ID().
> > AUTO_INCREMENT isn't portable. Now I only "support" MySQL and SQLite.
> > But I also did PostgreSQL (until it failed one of the more complex
> > queries, maybe it comes back one day) and maybe Oracle or whatever will
> > be compatible, too, so that I then stand there with my AUTO_INCREMENT
> > and can't use it.
> I would suggest the following --
> create a table called "SEQUENCES":
> create table SEQUENCES
> ( table_name varchar(128/maxlength of tablename) not null primary key,
> sequence_value largeint not null) ;
> Create a row for each table, eg:
> insert into sequences values('CUSTOMERS', 0);
> Next, whenever you want to get a new value, do:
> select sequence_value as current_value
> from sequences
> where table_name = 'CUSTOMERS';
> Next, do this:
> update sequences
> set sequence_value = sequence_value + 1
> where sequence_value = <<your current value you just got>>
> and table_name = 'CUSTOMERS'
> Now, repeate the above sequence until the UPDATE statement
> above says that it's updated 1 row. If it updated 0 rows, it means
> someone else did it just before you.
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
> MS SQL Server
> Upscene Productions
> My thoughts:
> Database development questions? Check the forum!
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1