Yves,
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
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
