On 13.11.2007 01:04 CE(S)T, Perrin Harkins wrote:
> On Nov 12, 2007 6:47 PM, Yves Goergen <nospam.list@stripped> 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" <nospam.list@stripped>
Visit my web laboratory at http://beta.unclassified.de
