List:General Discussion« Previous MessageNext Message »
From:mark addison Date:November 13 2007 3:37pm
Subject:Re: Transactions and locking
View as plain text  
Baron Schwartz wrote:
> 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@unclassified.de> 
>>> 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.
>
As your using InnoDB, which has row level locking a SELECT ... FOR 
UPDATE should work.
http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html
e.g.

BEGIN TRANSACTION
new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1
 -- some more work here
INSERT INTO table (id, ...) VALUES (new_id, ...)
COMMIT

mark
--
 





MARK ADDISON
WEB DEVELOPER

200 GRAY'S INN ROAD
LONDON
WC1X 8XZ
UNITED KINGDOM
T +44 (0)20 7430 4678
F 
E Mark.Addison@itn.co.uk
WWW.ITN.CO.UK

P  Please consider the environment. Do you really need to print this email?
Please Note:

 

Any views or opinions are solely those of the author and do not necessarily represent 
those of Independent Television News Limited unless specifically stated. 
This email and any files attached are confidential and intended solely for the use of the individual
or entity to which they are addressed. 
If you have received this email in error, please notify postmaster@stripped 

Please note that to ensure regulatory compliance and for the protection of our clients and business,
we may monitor and read messages sent to and from our systems.

Thank You.

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