List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 12 2000 11:12pm
Subject:Re: Locked tables while "Sending data" !?
View as plain text  
Hi!

>>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:

Tim> On Sun, Mar 12, 2000 at 12:59:50AM +0200, Michael Widenius wrote:
>> >>>>> "BAUMEISTER" == BAUMEISTER Alexandre
> <alex@stripped> writes:
>> 
BAUMEISTER> Bonjour Michael,
Tim> On Fri, Mar 10, 2000 at 05:04:09PM -0500, Thimble Smith wrote:
>> >>>> 
>> >>>> >Can you, or anyone else from TcX, comment more
> constructively on the
>> >>>> >last two paragraphs of my previous message?
>> >>>> 
>> >>>> I don't know what the answer is, because I don't know that code
>> >>>> well enough.  It seems like the server should be able to copy
> from
>> >>>> a temporary results table to the client and unlock the tables
> used
>> >>>> in the query.  It would be nice to be able to force this to
> happen,
>> >>>> either in the SQL statement or with a connection flag (or
> both).
>> >>>> 
>> >>>> >Also, Alexandre has just observed that the problem does not
> appear to
>> >>>> >be resolved by adding an ORDER BY. That doesn't match your
> explanation
>> >>>> >of the cause of the problem.
>> >>>> >
>> >>>> >I believe that there is a serious concurrency issue here
> that needs
>> >>>> >addressing, not dismissing.
>> >>>> 
>> >>>> I will look into it, and find out what it would take to make
> this
>> >>>> happen (or, maybe find out why it isn't happening and give a
> full
>> >>>> account).
>> 
MW> A last note about this thread:
>> 
MW> It would be trivial to add a new flag that will force creation of
MW> temporary sets on file.
>> 
MW> Thimbley should be able to do this for MySQL 3.23.14 :)
>> 
BAUMEISTER> And  it would be great if instead of using a temporary table on the
BAUMEISTER> hard  disk  (slow)  a memory buffer would be use if SMALL_RESULT is
BAUMEISTER> notified in the query.
>> 
>> This will of course be the default.

Tim> Just to clarify this...

Tim> a) you're proposing to add a _new_ option to SELECT to indicate that
Tim> the data should be buffered on the server before sending.

Yes;  The _new_option_  (SQL_USE_TEMPORARY_TABLE ?) will in effect
only set a flag that will force creation of a temporary table for the result.

Tim> b) The subsequent sending of the data to the client would no longer
Tim> ever hold locks preventing other activity on other threads.

This is already done for temporary tables.

Tim> c) The _existing_ SQL_BIG_RESULT and SQL_SMALL_RESULT options would
Tim> be used to determine if the buffer should be on disk or in memory.

Yes;  Exactly as before.

Note that in MySQL 3.23, the above option is not anymore that
necessary as MySQL will automaticly convert a HEAP internal temporary
table to a disk based one as soon as the temporary sets is bigger than
'tmp_table_size'.

Tim> Is that right?

Yes.

Tim> Thanks. I think this'll be a useful addition.

Tim> Two more thoughts: It might be worth having a way to enable it as a
Tim> default for a whole session (eg for clients who know they're on a slow
Tim> network or machine etc). It might also be worth thinking about some way
Tim> to automatically detect slow clients and automatically enable the option
Tim> for them.

Adding defaults is also trivial with a thread specific variable (just
as we do with SQL_BIG_RESULT). Automaticly detecting slow clients is
of course much harder...

Tim> Tim.

Regards,
Monty
Thread
Interbase vs. mySQL Opinions WantedJSC-OA)7 Mar
  • Locked tables while "Sending data" !?BAUMEISTER Alexandre8 Mar
    • Re: Locked tables while "Sending data" !?sinisa9 Mar
      • Re: Locked tables while "Sending data" !?Tim Bunce9 Mar
      • Re[2]: Locked tables while "Sending data" !?BAUMEISTER Alexandre10 Mar
        • Re: Re[2]: Locked tables while "Sending data" !?sinisa10 Mar
          • Re: Re[2]: Locked tables while "Sending data" !?Tim Bunce10 Mar
            • Re: Re[2]: Locked tables while "Sending data" !?sinisa10 Mar
              • Re[4]: Locked tables while "Sending data" !?BAUMEISTER Alexandre10 Mar
                • Re: Re[4]: Locked tables while "Sending data" !?sinisa10 Mar
              • Re: Re[2]: Locked tables while "Sending data" !?Tim Bunce10 Mar
                • Re: Re[2]: Locked tables while "Sending data" !?Thimble Smith11 Mar
                  • Re: Re[2]: Locked tables while "Sending data" !?Tim Bunce11 Mar
                    • Re: Re[2]: Locked tables while "Sending data" !?Michael Widenius11 Mar
                      • Re[4]: Locked tables while "Sending data" !?BAUMEISTER Alexandre11 Mar
                        • Re[4]: Locked tables while "Sending data" !?Michael Widenius12 Mar
                          • Re: Locked tables while "Sending data" !?Tim Bunce13 Mar
                            • Re: Locked tables while "Sending data" !?Michael Widenius13 Mar
                              • Re: Locked tables while "Sending data" !?Tim Bunce13 Mar
                                • Re: Locked tables while "Sending data" !?Michael Widenius13 Mar
                                  • Re: Locked tables while "Sending data" !?Tim Bunce13 Mar
                                • Re: Locked tables while "Sending data" !?Michael Widenius14 Mar
                                  • Re: Locked tables while "Sending data" !?Tim Bunce14 Mar
                                  • Re: Locked tables while "Sending data" !?Tim Bunce24 Apr
                                    • Re: Locked tables while "Sending data" !?Michael Widenius26 Apr
                                      • Re: Locked tables while "Sending data" !?Tim Bunce26 Apr
                                        • Re: Locked tables while "Sending data" !?Michael Widenius27 Apr
                    • Re[4]: Locked tables while "Sending data" !?BAUMEISTER Alexandre11 Mar
            • Re[4]: Locked tables while "Sending data" !?BAUMEISTER Alexandre10 Mar
              • Re[4]: Locked tables while "Sending data" !?Michael Widenius11 Mar