List:General Discussion« Previous MessageNext Message »
From:Perrin Harkins Date:November 12 2007 11:30pm
Subject:Re: Transactions and locking
View as plain text  
On Nov 12, 2007 5:58 PM, Yves Goergen <nospam.list@stripped> wrote:
> 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.

If you use a table lock on the first table where you get the ID, you
know that ID is safe to use.  Using a table lock when you get the ID
and then trusting transactions to roll back all the inserts in the
event of a later failure should work fine.

> > That Perl module uses the exact technique I described to you with
> > updates and LAST_INSERT_ID().
>
> AUTO_INCREMENT isn't portable.

You're misunderstanding.  The LAST_INSERT_ID() function doesn't use
AUTO_INCREMENT.  That's why the perl module uses it.  It just copies
the value you pass to it and makes that available without another
select.  It's not portable to SQLite, but you can use a sequence there
instead.

- Perrin
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