Is it safe to do a DatabaseQuery() within a thread?

Just starting out? Need help? Post your questions and find answers here.
User avatar
jacdelad
Addict
Addict
Posts: 1432
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Is it safe to do a DatabaseQuery() within a thread?

Post 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.
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
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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.
User avatar
jacdelad
Addict
Addict
Posts: 1432
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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.
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
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Is it safe to do a DatabaseQuery() within a thread?

Post by infratec »

Yes, that's right, else PB mixes up the results.
You need several connections.
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: Is it safe to do a DatabaseQuery() within a thread?

Post by Marc56us »

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! :lol: (a SQL database is only one file)
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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.
User avatar
Caronte3D
Addict
Addict
Posts: 1027
Joined: Fri Jan 22, 2016 5:33 pm
Location: Some Universe

Re: Is it safe to do a DatabaseQuery() within a thread?

Post by Caronte3D »

Maybe these things (threaded actions) could do with MySQL or similar, but not with SQLite (I think).
User avatar
jacdelad
Addict
Addict
Posts: 1432
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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.
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
User avatar
idle
Always Here
Always Here
Posts: 5042
Joined: Fri Sep 21, 2007 5:52 am
Location: New Zealand

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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.
User avatar
jacdelad
Addict
Addict
Posts: 1432
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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.
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
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
jacdelad
Addict
Addict
Posts: 1432
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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!
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
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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
User avatar
jacdelad
Addict
Addict
Posts: 1432
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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).
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
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Is it safe to do a DatabaseQuery() within a thread?

Post 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?
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Post Reply