Thread Still Blocking UI

Clearly I am missing something, but here is the setup:

subclass of thread (call it mThread) takes sql parameters and a query to run as arguments.
It creates its own connection using MySQLCommunityServer object, then runs the query. It stores the result in a property, when the query is done the isFinished flag of the mThread class is set to true.

A window has a an mThread property and a timer checking the isFinished flag.

As a point of testing I never actually access the results of the mThread property or do any UI updating.
The window creates a new mThread with a long running sql query, and starts the timer. Now the UI locks up and I get the beach ball (using MAC). Changing the timer period does nothing (as expected). Changing the query to a faster query is the only thing stops the UI from locking.

What is going on? This seems like the opposite behavior of a thread. Now to be clear, the thread has its own connection the the database, it does not share that with anything else.

Make sure that you’re performing the query in the Thread.Run event. Only code called from within the event is run on a separate thread.

the database queries are NOT threaded.
you must use the limit and offset keywords to reduce the length of your query
or
use MBS database plugins that are thread friendly.

This is the run event:

dim rs as RecordSet = connection.SQLSelect(searchQuery)
dim resultString as string
if rs <> NIl then
  while not rs.EOF
    for ii as integer = 1 to rs.FieldCount
      if ii <> 1 then
        resultString = resultString + "," + rs.IdxField(ii).StringValue
        
      else
        resultString = rs.IdxField(ii).StringValue
        
      end if
      
    next
    
    results.Append(resultString)
    rs.MoveNext()
    
  wend
  
else
  errorMessage = connection.ErrorMessage
  
end if

connection.Close()
isfinished = true

@Jean-Yves Pochez, You are saying that even if a thread has its own connection it will still block the main execution thread?

yes if you make a long sql query within a thread, this blocks the UI. this is true if you use xojo build in database plugins.

Since the threading is cooperative, it’s up to the threads to yield time to other threads. In the case of normal Xojo code the threads yield every time there is a loop boundary and in many other places too I think. Once you call out to some library or other function, such as the running of the query, unless those functions are specifically written to yield time back to the other threads then it will block the entire app just as if you hadn’t bothered to put it into a thread at all. Unfortunately the database stuff is mostly like this. If using the MBS plugins is not an option then you should break your query up into blocks as Jean-Yves Pochez suggested. Then run it in a loop to get through all the data and the very act of returning control to some Xojo code and looping will make sure the other threads get serviced, though you can specifically perform a yieldToNextThread in your loops if you want to experiment.

And I think you should definitely consider putting in a feature request for the built in Xojo plugins to add some proper support for threads as they make such things very difficult right now. There might even already be one…

In MBS Xojo SQL Plugin, we do have ConnectMT, SQLSelectMT and SQLExecuteMT function to run thread friendly.

For the record I’ve been using Christians plugins since forever and can whole heartedly recommend them as a solution to this problem and many others. Well worth the money if it’s a viable option. That doesn’t mean you shouldn’t bug Xojo with an official feature request to properly support their own threading model in the database plugins though :wink:

hm - IIRC years ago the built in Database Plugins all had a MultiThreaded property to control if they shall yield. As this costs performance, they then were refactored so that this behaviour is automatically detected. Just PostgreSQLDatabase has kept it’s MultiThreaded property. The documentation for MySQLCommunityServer even states [quote]“SQLSelect and SQLExecute statements do not block when called from within Threads.”[/quote] If they do, I’d count this as an Bug that should be reported. I did so with PostgreSQLPreparedStatement which did block despite MultiThreaded in feedback://showreport?report_id=37806 and got it fixed within a comparable short period of time.

yes there is a MultiThreaded property, but it is not used it seems.
I already made a feedback some times ago, then as it was not corrected at all I programmed differently …
it is definitely a bug, and one that will certainly never be corrected …

are we talking about MySQLCommunityServer here? According to the Docs the property was in use from 2011r1 to 2013r3 only.

[quote]I already made a feedback some times ago, then as it was not corrected at all I programmed differently …
it is definitely a bug, and one that will certainly never be corrected …[/quote]
That sounds quite disappointed. To my experience, if a bug is really a problem for you, Xojo will fix them in the end, even if relatively specific, but lobbying for it takes some time and effort. If you need a quick solution, you can head over to MBS, but this possibility should not be an excuse for not fixing upstream bugs in the end :wink:

I thought the issue is with Connecting to the Server only, because i tend to prefer short/quick querrys. And i created a FR for the connect issue in January 2019: feedback://showreport?report_id=54692

the code posted by the OP did not contain the db.Connect within the Thread.Run so I assumed this is done earlier. But yes - as of current there is no promise about db.Connect being thread friendly.

I programmed the thing differently (using limit and offset) and it worked well without threads …
so I did not hassle Xojo to correct it !

I overcome this actually by using

this code in the threaded process

[code]Dim param(-1) as variant
param.Append(DataToUpdate)
param.Append(DataToUpdate2)

Xojo.Core.Timer.CallLater(1, AddressOf AddToList, param)[/code]

and create new method with this to update UI
AddToList(data as auto)

[code] dim ListDetail() as variant = data

listbox1.addrow(cstr(ListDetail(0))
etc…)[/code]