From: Dmitry Lenev Date: January 18 2013 12:03pm Subject: Re: detecting select SQL statements that are functions List-Archive: http://lists.mysql.com/internals/38689 Message-Id: <20130118120315.GA8778@jubjub> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Hello Sergei! * Sergei Golubchik [13/01/18 15:19]: > Hi, Zardosht! > > On Jan 17, Zardosht Kasheff wrote: > > Hello all, > > > > In our storage engine's handler, we would like to be able to detect > > when a statement is just a simple select statement, that is, > > "read-only". > > > > 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. > > 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?. OTOH, it should work fine if one doesn't care about multi-engine statements. Regards, Dmitry -- Dmitry Lenev, Software Developer Oracle Development SPB/MySQL, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification