From: Zardosht Kasheff Date: January 18 2013 1:30pm Subject: Re: detecting select SQL statements that are functions List-Archive: http://lists.mysql.com/internals/38692 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Resending to internals, because I don't think the original made it On Fri, Jan 18, 2013 at 8:17 AM, Zardosht Kasheff wrote: > Hello Dmitry and Sergei, > > Thanks for the feedback. Tricks with transaction management is what we have > in mind. I am hesitant to look at the lock in external_lock or store_lock > because then I will not catch select... for update statements. So checking > uses_stored_routines() sounds good. > > I understand InnoDB has tricks with transaction management in 5.6. How does > InnoDB handle the case of simple select statements with autocommit on? > > Thanks > -Zardosht > > > On Fri, Jan 18, 2013 at 7:51 AM, Dmitry Lenev > wrote: >> >> Hello Sergei, >> >> * Sergei Golubchik [13/01/18 16:10]: >> > > > 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. >> >> IMO it depends. For example, such check can be used to relax locking/ >> do other tricks with transaction management, and in such cases treating >> such transaction as read-only can be not-so-good idea. >> >> I guess only TokuDB developers know the answer for TokuDB :) >> >> -- >> Dmitry Lenev, Software Developer >> Oracle Development SPB/MySQL, www.mysql.com >> >> Are you MySQL certified? http://www.mysql.com/certification > >