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 <[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
