List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 14 2007 11:50am
Subject:Re: Transactions and locking
View as plain text  
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/mysql?unsub=1
>

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