List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:January 18 2013 1:30pm
Subject:Re: detecting select SQL statements that are functions
View as plain text  
Resending to internals, because I don't think the original made it

On Fri, Jan 18, 2013 at 8:17 AM, Zardosht Kasheff <zardosht@stripped> 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 <Dmitry.Lenev@stripped>
> wrote:
>>
>> Hello Sergei,
>>
>> * Sergei Golubchik <serg@stripped> [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
>
>
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