List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:November 13 2007 4:39pm
Subject:Re: Transactions and locking
View as plain text  
Yves Goergen wrote:
> On 13.11.2007 16:37 CE(S)T, mark addison wrote:
>> 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
> 
> Row level locking can only lock rows that exist. Creating new rows (that
> would have an influence on my MAX value) are still possible and thus row
> level locking is not what I need. I really need locking an entire table
> for every other read or write access.

InnoDB can also lock the gap, which will prevent new rows that would 
have been returned by the SELECT.  The manual has more info on this in 
the section on consistent reads in InnoDB.  FOR UPDATE will do what you 
need.
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