On Nov 12, 2007 5:58 PM, Yves Goergen <[EMAIL PROTECTED]> wrote: > BEGIN TRANSACTION > SELECT MAX(id) FROM table > INSERT INTO table (id) VALUES (?) > INSERT INTO othertable (id) VALUES (?) > COMMIT > > 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. > > That Perl module uses the exact technique I described to you with > > updates and LAST_INSERT_ID(). > > AUTO_INCREMENT isn't portable. You're misunderstanding. The LAST_INSERT_ID() function doesn't use AUTO_INCREMENT. That's why the perl module uses it. It just copies the value you pass to it and makes that available without another select. It's not portable to SQLite, but you can use a sequence there instead. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
