List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 12 2007 6:39pm
Subject:Re: Transactions and locking
View as plain text  
Hello Yves,

> there's very much information about how transactions and locking works
> in InnoDB, but maybe there's also a simple and understandable answer to
> my simple question:
>
> When I start a transaction, then find the maximum value of a column and
> use that + 1 to write a new row into the table, how do transactions
> protect me from somebody else doing the same thing so that we'd both end
> up writing a new row with the same value?

They won't, a "constraint" protects you from inserting a new row with
the same value.

> Here's a description:
>
> BEGIN TRANSACTION
> new_id := (SELECT MAX(id) FROM table) + 1
> -- some more work here
> INSERT INTO table (id, ...) VALUES (new_id, ...)
> COMMIT
>
> What happens if another user does the same in that "more work" region?

You will end up with the same "new_id" value, but the primary key
constraint - if you have one - will reject the insert.

Transactions come in multiple flavors, have a look at the different
isolation
levels:
http://dev.mysql.com/doc/refman/5.0/en/commit.html
http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

Depending on your isolation level, for example, you will see new rows
in the table between your "begin transaction" and "select max..." or
between two "select max" statements. Other isolation levels will give
you the same max value when reading the value twice, even though
someone else inserted a new row.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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