List:General Discussion« Previous MessageNext Message »
From:Yves Goergen Date:November 13 2007 9:53am
Subject:Re: Transactions and locking
View as plain text  
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
Thread
Transactions and lockingYves Goergen12 Nov
  • Re: Transactions and lockingMartijn Tonies12 Nov
  • Re: Transactions and lockingPerrin Harkins12 Nov
    • Re: Transactions and lockingYves Goergen12 Nov
      • Re: Transactions and lockingPerrin Harkins12 Nov
      • Re: Transactions and lockingYves Goergen12 Nov
        • Re: Transactions and lockingYves Goergen12 Nov
          • Re: Transactions and lockingPerrin Harkins12 Nov
            • Re: Transactions and lockingYves Goergen12 Nov
              • Re: Transactions and lockingPerrin Harkins13 Nov
                • Re: Transactions and lockingYves Goergen13 Nov
                  • mysqlhotcopyMalka Cymbalista13 Nov
                  • Re: Transactions and lockingBaron Schwartz13 Nov
                    • Re: Transactions and lockingmark addison13 Nov
                      • Re: Transactions and lockingYves Goergen13 Nov
                        • Re: Transactions and lockingBaron Schwartz13 Nov
                          • Re: Transactions and lockingYves Goergen13 Nov
                            • Re: Transactions and lockingBaron Schwartz13 Nov
                          • Re: Transactions and lockingPerrin Harkins13 Nov
                            • Re: Transactions and lockingYves Goergen13 Nov
                              • Re: Transactions and lockingBaron Schwartz13 Nov
                                • Re: Transactions and lockingYves Goergen13 Nov
                    • Re: Transactions and lockingYves Goergen13 Nov
                    • Re: Transactions and lockingYves Goergen13 Nov
                      • Re: Transactions and lockingBaron Schwartz13 Nov
                        • Re: Transactions and lockingYves Goergen13 Nov
                        • Re: Transactions and lockingYves Goergen13 Nov
                          • Re: Transactions and lockingPerrin Harkins13 Nov
  • Re: Transactions and lockingMartijn Tonies13 Nov
  • Re: Transactions and lockingMartijn Tonies14 Nov
    • Re: Transactions and lockingYves Goergen15 Nov
  • Re: Transactions and lockingMartijn Tonies15 Nov
Re: Transactions and lockingYves Goergen13 Nov
  • Re: Transactions and lockingPerrin Harkins13 Nov