Baron Schwartz wrote:
> Yves Goergen wrote:
>> (For the record... I missed the mailing list recipient - again!!)
>> On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote:
>>> On Nov 12, 2007 5:58 PM, Yves Goergen <firstname.lastname@example.org>
>>>> 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.
>>> From what I've read about MySQL's table locks and InnoDB, you cannot
>> LOCK TABLES with transactions. Either of them deactivates the other one.
>> Beginning a transaction unlockes all tables, locking tables ends a
> It's more complicated than that. You can use them together, you just
> have to do it like this:
> set autocommit = 0;
> lock tables;
> -- you are now in a transaction automatically begun by LOCK TABLES
> -- your tables are now unlocked.
> In fact, you *must* use a transaction for LOCK TABLES to be safe, at
> least in MySQL 5. Even if you're using non-transactional tables.
> Otherwise, you can get nasty behavior. See
> The manual isn't very clear on the interaction between LOCK TABLES and
> transactions, it's true. But this is what I've found.
As your using InnoDB, which has row level locking a SELECT ... FOR
UPDATE should work.
new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1
-- some more work here
INSERT INTO table (id, ...) VALUES (new_id, ...)
200 GRAY'S INN ROAD
T +44 (0)20 7430 4678
P Please consider the environment. Do you really need to print this email?
Any views or opinions are solely those of the author and do not necessarily represent
those of Independent Television News Limited unless specifically stated.
This email and any files attached are confidential and intended solely for the use of the individual
or entity to which they are addressed.
If you have received this email in error, please notify postmaster@stripped
Please note that to ensure regulatory compliance and for the protection of our clients and business,
we may monitor and read messages sent to and from our systems.