List:Internals« Previous MessageNext Message »
From:Dmitry Lenev Date:January 18 2013 12:03pm
Subject:Re: detecting select SQL statements that are functions
View as plain text  
Hello Sergei!

* Sergei Golubchik <serg@stripped> [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
Thread
detecting select SQL statements that are functionsZardosht Kasheff18 Jan
  • Re: detecting select SQL statements that are functionsDmitry Lenev18 Jan
  • Re: detecting select SQL statements that are functionsSergei Golubchik18 Jan
    • Re: detecting select SQL statements that are functionsDmitry Lenev18 Jan
      • Re: detecting select SQL statements that are functionsSergei Golubchik18 Jan
        • Re: detecting select SQL statements that are functionsDmitry Lenev18 Jan
Re: detecting select SQL statements that are functionsZardosht Kasheff18 Jan