On 13.11.2007 01:04 CE(S)T, Perrin Harkins wrote: > On Nov 12, 2007 6:47 PM, Yves Goergen <[EMAIL PROTECTED]> wrote: >> 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. > > I don't think that's correct. At least that's not how I read this: > http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html > > It sounds like you issue a LOCK TABLES at the beginning of your > transaction, and doing a COMMIT unlocks the tables at the end.
>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. > 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. I think I'll go for transactions and check the error code in most cases. Only where a custom check is needed, I'll lock the tables without using a transaction. I'll see how far I get with it. 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. Bad. The only thing I can do then is write the data and afterwards count if there are two of them. But this still isn't safe, in concurrency means. Any solution? May be a bug report? -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
