From: Sergei Golubchik
Date: January 18 2013 11:14am
Subject: Re: detecting select SQL statements that are functions
List-Archive: http://lists.mysql.com/internals/38688
Message-Id: <20130118111432.GA378@meddwl.fritz.box>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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.

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.

> We have tried to do something similar in MySQL 5.5.
> 
> The problem we run into is when the select statement is a function,
> and the function performs a write. With the thd, is there a way to
> detect that the statement is not a simple select, but rather calling a
> function?
> 
Regards,
Sergei