From: Zardosht Kasheff
Date: February 26 2011 12:51am
Subject: Re: transaction commit/rollback and handler::external_lock(F_UNLCK)
List-Archive: http://lists.mysql.com/internals/38253
Message-Id: <AANLkTi==5a-3c7n1a7v+ALD4vf4k+eKRLWV4YsNAdGVU@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I now see where InnoDB is maintaining the count in 5.1. So I guess it
is probably necessary, although I cannot reproduce a case where
handlerton->commit/rollback is not being called.

-Zardosht

On Fri, Feb 25, 2011 at 7:06 PM, Zardosht Kasheff <zardosht@stripped> wrot=
e:
> Hello Konstantin,
>
> Thanks for your reply. It is the 5.1 case that I am particularly
> interested in. I do not see where InnoDB is maintaining this count and
> auto committing the transaction.
>
> Also, I always see handlerton->commit/rollback being called at the end
> of statement. This is what I did for the simplest example:
> create temporary table foo (a int);
> Lock tables foo read;
> select * from foo;
> unlock tables;
>
> I saw innobase_commit get called during "lock tables foo read" and
> during "select * from foo;"
>
> So I guess I still dont see an example where
> handlerton->commit/rollback is not called. Is there a chance this has
> been fixed in 5.1 as well?
>
> Thanks
> -Zardosht
>
> On Fri, Feb 25, 2011 at 6:57 PM, Konstantin Osipov
> <kostja.osipov@stripped> wrote:
>> * Zardosht Kasheff <zardosht@stripped> [11/02/26 02:41]:
>>> In ha_ndbcluster::external_lock, in mysql 5.1.52, I see the following
>>> comment under the case where lock_type =3D=3D F_UNLCK:
>>> =A0 =A0 =A0 =A0 =A0 /*
>>> =A0 =A0 =A0 =A0 =A0 =A0 Unlock is done without a transaction commit / r=
ollback.
>>> =A0 =A0 =A0 =A0 =A0 =A0 This happens if the thread didn't update any ro=
ws
>>> =A0 =A0 =A0 =A0 =A0 =A0 We must in this case close the transaction to r=
elease resources
>>> =A0 =A0 =A0 =A0 =A0 */
>>>
>>> But I do not see this anywhere else in any other handler.
>>>
>>> Is there a scenario where a transaction that has been registered via
>>> trans_register_ha does NOT have handlerton->commit or
>>> handlerton->rollback getting called, and therefore requireing
>>> ha_ndbcluster::external_lock to execute:
>>> =A0 =A0 =A0 =A0 =A0 ndb->closeTransaction(thd_ndb->trans);
>>> =A0 =A0 =A0 =A0 =A0 thd_ndb->trans=3D NULL;
>>
>> In 5.5 handlerton->commit/rollback is guaranteed to be eventually
>> called for any registered handler.
>>
>> However, this doesn't always happen in order.
>>
>> One example, when ha->external_lock(F_UNLCK) is called before
>> handlerton->commit/rollback is mysql_unlock_some_tables and
>> mysql_unlock_read_tables().
>>
>> MySQL can call these functions before end of statement,
>> (commit/rollback is done at end of statement), and they
>> in turn call handler::external_lock.
>>
>>> My guess is that no, this is not required, otherwise other storage
>>> engines would do this, but I would like to confirm.
>>
>> InnoDB counts calls to external_lock(F_LOCK) and
>> external_lock(F_UNLOCK). When it drops to zero, it auto-commits
>> the statement transaction. This is necessary in 5.0 and 5.1,
>> where handlerton->commit/rollback is not guaranteed to be called
>> at end of statement (try, for the simplest example, to create a
>> temporary table and lock it with LOCK TABLES, and then execute
>> some statements).
>>
>>
>>
>>
>> --
>>
>