Page 1 of 2
Is it safe to do a DatabaseQuery() within a thread?
Posted: Tue Mar 21, 2023 8:55 pm
by jacdelad
Hello,
I have a program with a lot of database requests. Some take a long time (the database is not the fastest one...); however, I want to give the user an option to abort this request. Is it safe to put the DatabaseQuery() into a thread and terminate it if requested? Do I have to reconnect afterwards? Or is it a very bad idea at all?
Edit: Can I define a timeout? I'm sure I can with MySQL. This wouldn't solve my user-request-question, but I could limit the query time.
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Tue Mar 21, 2023 9:18 pm
by infratec
You can do it, if you also open the database in the same thread.
If you have multiple accesses to the same connection from different threads, you get into trouble.
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 2:55 pm
by jacdelad
Aye, thanks!
So, preopening the database in the main thread won't work or will casue trouble? I'm using one connection for 5 different queries (one after another). One can take very long and isn't necessary, so that's why I want to have an abort option.
This way I'd have to establish another connection for each request.
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 3:04 pm
by infratec
Yes, that's right, else PB mixes up the results.
You need several connections.
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 3:16 pm
by Marc56us
Database
Query() does not modify data / structure (it's a 'SELECT' SQL command) unlike DataBase
Update() which will modify the data or the structure.
So there is no reason for it to block even with multiple accesses.
That said, the easiest thing to do is to try. Be adventurous!
(a SQL database is only one file)
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 3:20 pm
by infratec
That's not the problem.
As I remebere correctly, I get corrupted results when I run 2 SELECTs parallel with the same DB connection.
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 3:32 pm
by Caronte3D
Maybe these things (threaded actions) could do with MySQL or similar, but not with SQLite (I think).
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 3:44 pm
by jacdelad
No, I maybe expressed my question wrongly (I'M NOT DOING SOMETHING SIMULTANIOUSLY!), here's some pseudo-code:
This is the normal query:
Code: Select all
OpenDatabase(...)
;Code...
If DatabaseQuery(...) ; <- this sometimes takes ages and is not always necessary
While NextDatabaseRow(...)
;More Code...
Wend
FinishDatabaseQuery(...)
EndIf
This is what I mean:
Code: Select all
Global GlobalVar
Procedure DatabaseQueryASync_(Parameters)
GlobalVar=DatabaseQuery(...)
EndProcedure
Procedure DatabaseQueryASync(...)
CreateThread(@DatabaseQueryASync_(),Parameters)
;EventLoop which may terminate the thread and otherwise waits for the thread
ProcedureReturn GlobalVar
EndProcedure
OpenDatabase(...)
;Code...
If DatabaseQueryASync(...)
While NextDatabaseRow(...)
;More Code...
Wend
FinishDatabaseQuery(...)
EndIf
My question is, if it's safe to terminate the thread and do another request afterwards. All requests are done serially, not simultaniously.
I basically only want to put DatabaseQuery() into a thread, in case I want to terminate it before the result is present.
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 6:12 pm
by idle
modify your async thread with an abort flag to skip the for each row so if time out happens the dB engine will finish its query and you can still clean up. If you just killed thread you'd cause memory leak in dB engine. There shouldn't be any issue from simultaneous look ups.
Query
If not abort
For each
Next
Endif
Finish query
Sorry on phone.
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 6:18 pm
by jacdelad
Guys, please, it's not fetching the rows, it's the DatabaseQuery()-command that takes forever! That's why I want to call it in a separate thread.
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 6:34 pm
by skywalk
SQLite has made many strides in threaded queries.
And added timeout/busy signals to accommodate long running queries.
And added query planner functions to predict complexity of results.
Best answer I can give is test your scenario thoroughly.
As long as your database file is not on a network drive, you can proceed safely.
Each thread must have its own connection.
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Wed Mar 22, 2023 6:38 pm
by jacdelad
The database is a servercluster at my work. The queries that sometimes take forever for the DatabaseQuery()-call usually work fast, but sometimes hang for no reason (of course there is a reason, but it's beyond my power to change or analyze that). However, given with the information from this thread I'll try make this call within a thread with its own connection.
Thanks to everyone for their answers and time!
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Thu Mar 23, 2023 9:26 am
by Marc56us
Pass this link on to the person who chose this solution at your work..
https://www.sqlite.org/whentouse.html
[...]
Checklist For Choosing The Right Database Engine
Is the data separated from the application by a network? → choose client/server
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Thu Mar 23, 2023 4:56 pm
by jacdelad
Erm...thanks, but we cannot simply change the database engine. The database is several terabytes huge and some machines and software require an MySQL database (some other require MSSQL, which is even more annoying).
Re: Is it safe to do a DatabaseQuery() within a thread?
Posted: Thu Mar 23, 2023 5:57 pm
by skywalk
Yeah, db engine change was not suggested.
It was assumed since you are using PB, that the db engine was SQLite or Postgres/MySQL?
You are just using PB for queries and not updates?