On Nov 13, 2007 4:53 AM, Yves Goergen <[EMAIL PROTECTED]> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
