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 <[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.
As your using InnoDB, which has row level locking a SELECT ... FOR
UPDATE should work.
http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html
e.g.
BEGIN TRANSACTION
new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1
-- some more work here
INSERT INTO table (id, ...) VALUES (new_id, ...)
COMMIT
mark
--
MARK ADDISON
WEB DEVELOPER
200 GRAY'S INN ROAD
LONDON
WC1X 8XZ
UNITED KINGDOM
T +44 (0)20 7430 4678
F
E [EMAIL PROTECTED]
WWW.ITN.CO.UK
P Please consider the environment. Do you really need to print this email?
Please Note:
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 [EMAIL PROTECTED]
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.
Thank You.
