List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:November 13 2007 1:01pm
Subject:Re: Transactions and locking
View as plain text  
Yves Goergen wrote:
> (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.

It's more complicated than that.  You can use them together, you just 
have to do it like this:

set autocommit = 0;
begin;
lock tables;
-- you are now in a transaction automatically begun by LOCK TABLES
.....
commit;
-- your tables are now unlocked.

In fact, you *must* use a transaction for LOCK TABLES to be safe, at 
least in MySQL 5.  Even if you're using non-transactional tables. 
Otherwise, you can get nasty behavior.  See 
http://bugs.mysql.com/bug.php?id=31479

The manual isn't very clear on the interaction between LOCK TABLES and 
transactions, it's true.  But this is what I've found.

Baron
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