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 <nospam.list@stripped> wrote:
>>> 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 use
> LOCK TABLES with transactions. Either of them deactivates the other one.
> Beginning a transaction unlockes all tables, locking tables ends a
> transaction.
It's more complicated than that. You can use them together, you just
have to do it like this:
set autocommit = 0;
begin;
lock tables;
-- you are now in a transaction automatically begun by LOCK TABLES
.....
commit;
-- 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
http://bugs.mysql.com/bug.php?id=31479
The manual isn't very clear on the interaction between LOCK TABLES and
transactions, it's true. But this is what I've found.
Baron
