Is it safe to do a DatabaseQuery() within a thread?
Is it safe to do a DatabaseQuery() within a thread?
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.
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.
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Re: Is it safe to do a DatabaseQuery() within a thread?
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.
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?
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.
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.
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Re: Is it safe to do a DatabaseQuery() within a thread?
Yes, that's right, else PB mixes up the results.
You need several connections.
You need several connections.
Re: Is it safe to do a DatabaseQuery() within a thread?
DatabaseQuery() does not modify data / structure (it's a 'SELECT' SQL command) unlike DataBaseUpdate() 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)
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?
That's not the problem.
As I remebere correctly, I get corrupted results when I run 2 SELECTs parallel with the same DB connection.
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?
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?
No, I maybe expressed my question wrongly (I'M NOT DOING SOMETHING SIMULTANIOUSLY!), here's some pseudo-code:
This is the normal query:
This is what I mean:
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.
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
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
I basically only want to put DatabaseQuery() into a thread, in case I want to terminate it before the result is present.
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Re: Is it safe to do a DatabaseQuery() within a thread?
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.
Query
If not abort
For each
Next
Endif
Finish query
Sorry on phone.
Re: Is it safe to do a DatabaseQuery() within a thread?
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.
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Re: Is it safe to do a DatabaseQuery() within a thread?
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.
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.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Re: Is it safe to do a DatabaseQuery() within a thread?
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!
Thanks to everyone for their answers and time!
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Re: Is it safe to do a DatabaseQuery() within a thread?
Pass this link on to the person who chose this solution at your work..
https://www.sqlite.org/whentouse.html
[...]
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?
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).
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Re: Is it safe to do a DatabaseQuery() within a thread?
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?
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?
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum