List:General Discussion« Previous MessageNext Message »
From:Yves Goergen Date:November 13 2007 9:55am
Subject:Re: Transactions and locking
View as plain text  
(For the record... I missed the mailing list recipient - again!!)

On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote:
> On Nov 12, 2007 5:58 PM, Yves Goergen <nospam.list@stripped> wrote:
>> 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.

>From what I've read about MySQL's table locks and InnoDB, you cannot use
LOCK TABLES with transactions. Either of them deactivates the other one.
Beginning a transaction unlockes all tables, locking tables ends a
transaction.

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

I don't understand what you mean.

>  It's not portable to SQLite, but you can use a sequence there
> instead.

To my knowledge, SQLite doesn't support sequences either, only
"auto_increment".

I've began to convert my code to evaluate error codes now, but I see the
next problem already: At one place, I insert a row where two columns
could potentially violate a uniqueness constraint. With just reading the
error code, I can't figure out which of them caused the problem. The
error message I can present to the user will be somewhat generic then.
("Either this or that of your input already exists. Find out which one.
Haha!")

Maybe I'll use error codes or table locks depending on the situation.
It's all a big hack, but so is databases (and portability) it seems. I'm
not sure yet. It's late.

-- 
Yves Goergen "LonelyPixel" <nospam.list@stripped>
Visit my web laboratory at http://beta.unclassified.de
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