List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 13 2007 1:56pm
Subject:Re: Transactions and locking
View as plain text  
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

Thread
Transactions and lockingYves Goergen12 Nov
  • Re: Transactions and lockingMartijn Tonies12 Nov
  • Re: Transactions and lockingPerrin Harkins12 Nov
    • Re: Transactions and lockingYves Goergen12 Nov
      • Re: Transactions and lockingPerrin Harkins12 Nov
      • Re: Transactions and lockingYves Goergen12 Nov
        • Re: Transactions and lockingYves Goergen12 Nov
          • Re: Transactions and lockingPerrin Harkins12 Nov
            • Re: Transactions and lockingYves Goergen12 Nov
              • Re: Transactions and lockingPerrin Harkins13 Nov
                • Re: Transactions and lockingYves Goergen13 Nov
                  • mysqlhotcopyMalka Cymbalista13 Nov
                  • Re: Transactions and lockingBaron Schwartz13 Nov
                    • Re: Transactions and lockingmark addison13 Nov
                      • Re: Transactions and lockingYves Goergen13 Nov
                        • Re: Transactions and lockingBaron Schwartz13 Nov
                          • Re: Transactions and lockingYves Goergen13 Nov
                            • Re: Transactions and lockingBaron Schwartz13 Nov
                          • Re: Transactions and lockingPerrin Harkins13 Nov
                            • Re: Transactions and lockingYves Goergen13 Nov
                              • Re: Transactions and lockingBaron Schwartz13 Nov
                                • Re: Transactions and lockingYves Goergen13 Nov
                    • Re: Transactions and lockingYves Goergen13 Nov
                    • Re: Transactions and lockingYves Goergen13 Nov
                      • Re: Transactions and lockingBaron Schwartz13 Nov
                        • Re: Transactions and lockingYves Goergen13 Nov
                        • Re: Transactions and lockingYves Goergen13 Nov
                          • Re: Transactions and lockingPerrin Harkins13 Nov
  • Re: Transactions and lockingMartijn Tonies13 Nov
  • Re: Transactions and lockingMartijn Tonies14 Nov
    • Re: Transactions and lockingYves Goergen15 Nov
  • Re: Transactions and lockingMartijn Tonies15 Nov
Re: Transactions and lockingYves Goergen13 Nov
  • Re: Transactions and lockingPerrin Harkins13 Nov