From: Sergei Golubchik
Date: January 18 2013 12:07pm
Subject: Re: detecting select SQL statements that are functions
List-Archive: http://lists.mysql.com/internals/38690
Message-Id: <20130118120738.GA4623@meddwl.fritz.box>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi, Dmitry!

On Jan 18, Dmitry Lenev wrote:
> * Sergei Golubchik <serg@stripped> [13/01/18 15:19]:
> > Hi, Zardosht!
> > 
> > > In MySQL 5.6, InnoDB seems to do this with the function
> > > thd_trx_is_auto_commit.
> > 
> > I don't think so. thd_trx_is_auto_commit() (judging from the name, not
> > looking at the source) will check if the transaction will auto-commit.
> > An INSERT or UPDATE in the auto-commit mode will auto-commit too.
> 
> The name is a bit misleading. In fact this function also checks that
> this is a SELECT.

Okay :) that's why I had a disclaimer above

> > To detect read-only statements reliably, you should look at the
> > table locks. in external_lock or in the store_lock method.
> > 
> > If no table was locked for writing (yes, you downgrade locks to
> > TL_WRITE_ALLOW_WRITE, but I mean the original lock level here), then
> > no table will be modified by this statement. No TokuDB table, at
> > least.
> > 
> > This works for all statements, selects, multi-table deletes/updates,
> > insert...select, invoked stored functions, triggers, everything.
> 
> I was also tempted to advice this nice solution when answering Zardosht :)
> 
> Unfortunately, it has the following drawback - since storage engine is
> not aware about tables which are used in the same statement but belong to
> different storage engines, such approach might unable to handle statements
> using several engines correctly. E.g. think of statement that updates InnoDB
> table but also reads from TokuDB. Should TokuDB consider such a statement
> read-only?.

Yes, I think it should. As far as TokuDB is concerned the transaction is
read-only, there is nothing to commit or rollback, no need to write
anything in the redo/undo log, etc.

I've mentioned this scenario above ("no table will be modified ... No
TokuDB table, at least", "This works for ... multi-table
deletes/updates").

Regards,
Sergei