List:General Discussion« Previous MessageNext Message »
From:Perrin Harkins Date:November 12 2007 6:58pm
Subject:Re: Transactions and locking
View as plain text  
On Nov 12, 2007 1:25 PM, Yves Goergen <nospam.list@stripped> wrote:
> 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?

Usually you would use an auto_increment column for this.  If you want
to do it manually, you either need to lock the whole table (to prevent
rows from being added) or do the work in one statement (untested):

INSERT INTO table (id) values (SELECT MAX(id) + 1 FROM table);

You could also keep a separate table that just holds the current ID in
a single row and use an update to get it (also untested):

UPDATE counter SET id = LAST_INSERT_ID(id + 1);

Putting the LAST_INSERT_ID in there lets you grab the id afterward in
the same way you get it from an auto_increment, without doing another
select.

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