Yves,
> >> 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]
