Writing a console app (a helper app) that basically gets fired off by a Cron job. It’s purpose is to read a database table of other servers, then go off and poll each one for some data. If all goes well I get each result back quickly, but sometimes I get a timeout. For this reason I’ve spun each query off onto a thread.
However at the end of each thread I then updated the database with the result. However first UPDATE works sell, all the others one get an database error, “A Query is already in progress”. Is there a way to make the MYSQL plugin handle these asynchronously?
For now, I do the updates when the last thread has completed, but would prefer to update each one at the end of their respective thread.
[quote=85318:@Norman Palardy]Yeah multiple threads with one connection will have this problem
Each thread should have its own & you should be off to the races[/quote]
Many thanks for the answers. WIll do.
As an aside, as I haven’t really made use of threads too much, but is there a programming limit to the number of threads spawned, or really a hardware/processing limit? Theoretically I could be hitting 100 servers or shall I best stagger? Just a practical thought.
Xojo threads are not preemptive so it probably won’t matter that much
They don’t execute on separate cores
But each app (helper or otherwise) can / will execute on a different core
[quote=85723:@Norman Palardy]Xojo threads are not preemptive so it probably won’t matter that much
They don’t execute on separate cores
But each app (helper or otherwise) can / will execute on a different core[/quote]
[quote]Is it possible to have multiple connections to a SQLiteDatabase in different threads or is it the same as with MySQL ?
[/quote]
I do for both. With SQLite I open it once in the App.Open event and use the same app.db many times in threads with the RecordSet unique per Method calling it.
With MySQL I create both the db and the RecordSet at the start of each method, whether in a thread or not. No problems so far.
Pretty much EVERY database should have multiple connections for multiple threads.
Even in other languages sharing them across threads is a recipe for problems.
Is the main issue to have only one thread using a db connection at a time, or is it required that the db connection be made by the thread that will be accessing it?
I’m trying to track down some VERY slow db updating from a thread and currently I’m opening the db in the main thread and then asking only one sub thread to do all of the updates and inserts. I do transactions of 10,000 records which are taking about 10 minutes, my app is only using about 3% of the CPU and I’ve got over a million records to insert or update.