On Nov 13, 2007 4:53 AM, Yves Goergen <nospam.list@stripped> wrote:
> From that page:
> > Sometimes it would be useful to lock further tables in the course of
> > a transaction. Unfortunately, LOCK TABLES in MySQL performs an
> > implicit COMMIT and UNLOCK TABLES. An InnoDB variant of LOCK TABLES
> > has been planned that can be executed in the middle of a transaction.
I read that as saying that you can't issue a LOCK TABLES and then
another LOCK TABLES in the same transaction, because it causes a
COMMIT before locking the tables. You can use one LOCK TABLES at the
beginning of your transaction with no problems.
> > In any case, you only need to do a table lock long enough to insert a
> > row into your first table. After that, you can release the lock.
>
> And when I insert the row in the first table but cannot do so in the
> second because of some invalid data, I need to also remove the first row
> again because it doesn't make sense alone. This is what transactions are
> for.
Yes, and you will be in a transaction, and the insert will be rolled
back. But maybe UNLOCK TABLES would commit your transaction, in which
case, you do need to keep the lock until the transaction is over.
> Oh, I see from that page above:
> > All InnoDB locks held by a transaction are released when the
> > transaction is committed or aborted. Thus, it does not make much
> > sense to invoke LOCK TABLES on InnoDB tables in AUTOCOMMIT=1 mode,
> > because the acquired InnoDB table locks would be released
> > immediately.
>
> So, it seems that locking tables is *impossible* with InnoDB.
No, the text you're quoting there says that LOCK TABLES is impossible
without a transaction in InnoDB. You plan to use a transaction.
- Perrin
