List:General Discussion« Previous MessageNext Message »
From:Perrin Harkins Date:November 13 2007 1:09pm
Subject:Re: Transactions and locking
View as plain text  
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
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