On Nov 12, 2007 1:25 PM, Yves Goergen <[EMAIL PROTECTED]> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
