is wxSQLite3 Resultset thread safe? Topic is solved

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

is wxSQLite3 Resultset thread safe?

Post by evstevemd »

Is ResultSet thread safe? If no what precautions need to be taken to ensure no troubles?
Thanks
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: is wxSQLite3 Resultset thread safe?

Post by utelle »

evstevemd wrote:Is ResultSet thread safe?
No. BTW I don't see any scenario where it would make sense to process the same result set in different threads. You would get absolutely unpredictable results since a result set allows to step through the row set exactly once one row after the other. Each thread would see only disjunct parts of the result set. And there is no way to predict which thread sees which rows, unless you synchronize the access in a predictable manner. In the latter you would be better off to implement a producer-consumer pattern, where only one thread accesses the result set (producer) and passes forward the data values of the current row to another thread (consumer).
evstevemd wrote:If no what precautions need to be taken to ensure no troubles?
Even if SQLite is compiled in thread safe mode (THREADSAFE=1, default for the precompiled version coming with wxSQLite3) it remains possibly dangerous to pass SQLite handles from one thread to another (see http://sqlite.org/faq.html#q6), since the statement handle associated with wxSQLite3ResultSet is inherently not finalized. Therefore it doesn't make sense to make wxSQLite3ResultSet thread safe. If you need to access the same database from different threads you should open a separate database connection for each thread, then you are safe.

Regards,

Ulrich
briceandre
Ultimate wxWidgets Guru
Ultimate wxWidgets Guru
Posts: 672
Joined: Tue Aug 31, 2010 6:22 am
Location: Belgium

Post by briceandre »

Hello Ulrich,

The question of evstevemd comes from a previous discussion in another thread where he explained that he would like to move his DB requests in secondary threads. The problem is that he shall parse his results from one thread to the other.

I explained him that he should take care because some mechanisms used by several datatypes makes objects copies not thread safe. If we take the example of wxString, the reference counting used for COW mechanism can cause troubles if a copy of a string is passed to another thread.

Evstevemd was then guessing if such a copy was problematic with the wxSQLite3ResultSet object. I don't think he wants to access this object from both threads, but simply to produce it from one thread, and consuming it from a second one.

From what I understand from your post, when a wxSQLite3ResultSet is provided, the corresponding statement is not finalised and so, the sqlite handle cannot be used until the wxSQLite3ResultSet is destroyed (which will finalize the statement).

If I am correct, the only safe way to delegate the DB request to a secondary thread would be to completely extract all results from the wxSQLite3ResultSet, place them in a thread-safe data structure and provide this data structure.

Correct me if I am wrong,
regards,
Brice
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

Hello Brice,
briceandre wrote:The question of evstevemd comes from a previous discussion in another thread where he explained that he would like to move his DB requests in secondary threads. The problem is that he shall parse his results from one thread to the other.
In general it's not a bad idea to have a dedicated worker thread for database access. But at least when using SQLite you have to be careful, because SQLite handles shouldn't be passed from one thread to another.
briceandre wrote:Evstevemd was then guessing if such a copy was problematic with the wxSQLite3ResultSet object. I don't think he wants to access this object from both threads, but simply to produce it from one thread, and consuming it from a second one.
This decreases the chance of failure but doesn't eliminate it, since still SQLite handles are passed around. As soon as the database thread accesses the database independently in parallel to the thread consuming the result set this could result in problems.
briceandre wrote:From what I understand from your post, when a wxSQLite3ResultSet is provided, the corresponding statement is not finalised and so, the sqlite handle cannot be used until the wxSQLite3ResultSet is destroyed (which will finalize the statement).
Yes, but it's not a question of reusing a handle but of concurrency. A SQLite handle possibly holds locks on the database and this could lead to unwanted race conditions.
briceandre wrote:If I am correct, the only safe way to delegate the DB request to a secondary thread would be to completely extract all results from the wxSQLite3ResultSet, place them in a thread-safe data structure and provide this data structure.
In principle this is correct although it's certainly not recommended and often not feasible to pass a complete result set in this manner. This approach would work for result sets which are known in advance to be small, but it won't work for queries which potentially return millions of data records.

Possible solutions are
a) each thread has it's own database connection, or
b) the database thread passes the values of one row of a result set at a time instead of the whole result set (and therefore implicitly SQLite handles).

Regards,

Ulrich
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

Thanks Brice for making my case clear. Actually after Querying and get the resultset, the secondary thread dies, it doesn't touch database anymore. and the GUI thread takes that resultset and updates the GUI accordingly. I will have many DBs being querried by different threads, but all of them posting to GUI thread which will then Update Notebook pages. What do you advice in such case as this?
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
briceandre
Ultimate wxWidgets Guru
Ultimate wxWidgets Guru
Posts: 672
Joined: Tue Aug 31, 2010 6:22 am
Location: Belgium

Post by briceandre »

What do you advice in such case as this?
Well, I don't know exactly what you manage to do and what are your constraints.

What is funny is that I am facing a similar problem : currently, all my db accesses are performed in the main thread. I implemented a new functionality where some db requests may take several seconds to achieve (because result sets are quite large).

So, I am currently looking for a solution for this problem. The major difference with you is that I do not use wxSQLite. I use SQLite, but with my own data binding library and my owwn data types (for historical reasons....).

I have an idea on how to solve my problem, but this has still not been implemented, so I cannot guarantee it will work. I also cannot guarantee this will solve your problem as I don't know exactly your constraints.

In your case, I suppose that if you want to delegate the DB access to a secondary thread, it's because it takes a lot of time. The problem is to know if you need all data extracted or not.

In my case, it is not the case : I display the data in a wxListCtrl, in virtual mode. So, I only need the data that will be displayed in the scrolled part of the control. What I plan to do is to split this request in sub-requests where only a part of the elements are retrieved (the ones around where the user scrolls). I plan to encapsulate everything in a smart object, and I am still not sure I will use a secondary thread.

If you really need all elements (for performing a processing on it, or something like that), I think the simplest way is to extract all data of wxSQLite3ResultSet and put them in a self-made data structure for which you are sure you can share it between threads. But, in this case, Ulrich is right. If the data is very big, it can be a problem. But, if you expect the data quantity can be too large, you can split it so that your working thread extracts, lets say, 1000 elements at a time, put them in a structure, provided it to main thread, and then process next 1000 ones.

Note that if you, or someone else, has another idea, just tell it because I am currently facing a similar problem. So, I can be highly interested...
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

Hi Telle and Andre,
I was on SQLite3 mailing list and here is the extract of Quote from Ivanov:
It seems that this explanation as well as all other statements in the
thread you linked are coming from the wrong assumption that SQLite's
handles cannot be used from any thread other than the one created that
handle. Although this was true in some earlier versions of SQLite it's
not true in the current version. So if SQLite is compiled with
THREADSAFE=1 (as mentioned in that thread) then you can do with it
whatever you want. Just beware of possible data races and potentially
uncommitted transactions because of some open statement handles. And
if as you say there's no simultaneous access to the database from
different threads then there's no difference in your usage pattern
from single-threaded one.


Pavel
So it seems there is no problem for me to go ahead as long as I adhere to stated warnings!
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

evstevemd wrote:I was on SQLite3 mailing list and here is the extract of Quote from Ivanov:
It seems that this explanation as well as all other statements in the
thread you linked are coming from the wrong assumption that SQLite's
handles cannot be used from any thread other than the one created that
handle. Although this was true in some earlier versions of SQLite it's
not true in the current version. So if SQLite is compiled with
THREADSAFE=1 (as mentioned in that thread) then you can do with it
whatever you want. Just beware of possible data races and potentially
uncommitted transactions because of some open statement handles. And
if as you say there's no simultaneous access to the database from
different threads then there's no difference in your usage pattern
from single-threaded one.

Pavel
So it seems there is no problem for me to go ahead as long as I adhere to stated warnings!
I commented on that on the SQLite mailing list as well. I didn't make a wrong assumption, I just cited from the SQLite FAQ which is absolutely valid for the current SQLite version.

Whether you run into troubles or not passing a result set from one thread to another depends on what exactly you are doing. It might work or it might not. The SQlite documentation is clear in that point that passing a handle of a not finalized statement between different threads is potentially dangerous.

If your database thread dies after delivering the result set as you said I don't understand why you bother to use a different thread at all. Just use a global database instance for your queries and you're safe for sure.

Regards,

Ulrich
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

utelle wrote: I commented on that on the SQLite mailing list as well. I didn't make a wrong assumption, I just cited from the SQLite FAQ which is absolutely valid for the current SQLite version.

Whether you run into troubles or not passing a result set from one thread to another depends on what exactly you are doing. It might work or it might not. The SQlite documentation is clear in that point that passing a handle of a not finalized statement between different threads is potentially dangerous.

If your database thread dies after delivering the result set as you said I don't understand why you bother to use a different thread at all. Just use a global database instance for your queries and you're safe for sure.

Regards,

Ulrich
I saw it Urlich and It is well explained.
I don't understand what you mean by global instance. Do you mean a singleton?
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
briceandre
Ultimate wxWidgets Guru
Ultimate wxWidgets Guru
Posts: 672
Joined: Tue Aug 31, 2010 6:22 am
Location: Belgium

Post by briceandre »

I don't think there was contradiction in what Utelle or me said, wrt to what you stated here.

The fact is that there is no real need to delegate the statement prepare operation in a dedicated thread if all steps operations are performed in the main one. If you try extracting a lot of data, the time consuming part of the request will be executed in the main thread (i.e. sqlite3_step function calls).

My comprehension is that the 'ResultSet' object performs this extraction while you are reading its content, and finalises the statement when it is destroyed. So, you could probably pass it from secondary thread to main one if you take some precautions, but it will be a non-sense as all time consuming activities will be done in main thread.

So, for me, in your case, the best approach would probably be something like what I mentionned in previous thread :
If you really need all elements (for performing a processing on it, or something like that), I think the simplest way is to extract all data of wxSQLite3ResultSet and put them in a self-made data structure for which you are sure you can share it between threads. But, in this case, Ulrich is right. If the data is very big, it can be a problem. But, if you expect the data quantity can be too large, you can split it so that your working thread extracts, lets say, 1000 elements at a time, put them in a structure, provided it to main thread, and then process next 1000 ones.
But this lets open the initial problem that was stated in previous post : take care when you choose the data types that will hanlde the data exchanged between one thread and the other because, even if you put a mutex, it can be non-thread-safe due to optimisation mechanisms put in place in those data structures...

EDIT : I think that by global instance, Utelle means a single sqlite3 handle that you share between the different thread. Note that in this case, you should be sure that your sqlite connection is configured in 'Serialized' mode or you could corrupt your database...
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

briceandre wrote:I don't think there was contradiction in what Utelle or me said, wrt to what you stated here.
Right. But evstevemd asked almost the same question on the SQLite mailing list and got an - IMHO misleading - answer.
briceandre wrote:The fact is that there is no real need to delegate the statement prepare operation in a dedicated thread if all steps operations are performed in the main one. If you try extracting a lot of data, the time consuming part of the request will be executed in the main thread (i.e. sqlite3_step function calls).
That's just the point. wxSQLite3 is a thin wrapper for SQLite, that is, all real work is delegated to SQLite itself. And yes, the result set uses sqlite3_step to extract the data from the current row. In case of deeply nested very complex select queries it might take some time to prepare the extraction. In such cases one should take the time to analyze the query whether introducing additionally indexes could help to speed up the query.
briceandre wrote:My comprehension is that the 'ResultSet' object performs this extraction while you are reading its content, and finalises the statement when it is destroyed.
Yes, exactly.
briceandre wrote:So, you could probably pass it from secondary thread to main one if you take some precautions, but it will be a non-sense as all time consuming activities will be done in main thread.
True, but the gain would usually be small or even not existing since creating a thread isn't a cheap operation.
briceandre wrote:EDIT : I think that by global instance, Utelle means a single sqlite3 handle that you share between the different thread. Note that in this case, you should be sure that your sqlite connection is configured in 'Serialized' mode or you could corrupt your database...
By global instance I meant a singleton.In case of several threads accessing the same database I would recommend a database instance per thread. SQLite has no problems to handle several connections to the same database from different threads as long as each thread has it's own SQLite handle. As said before passing SQLite handles from thread to thread might work under certain circumstances but is definitely not recommended.

Regards,

Ulrich
aisnote
In need of some credit
In need of some credit
Posts: 7
Joined: Sat Nov 21, 2020 12:06 am

Re: is wxSQLite3 Resultset thread safe?

Post by aisnote »

how to do as below?
If you need to access the same database from different threads you should open a separate database connection for each thread, then you are safe.
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: is wxSQLite3 Resultset thread safe?

Post by utelle »

aisnote wrote: Fri Dec 04, 2020 11:39 pm how to do as below?
If you need to access the same database from different threads you should open a separate database connection for each thread, then you are safe.
Simply use one wxSQLite3Database instance per thread. Call its Open method on thread initialization and its Close method on thread termination. However, keep in mind that as with all concurrent access to global resources there is a chance of getting a deadlock situation, especially if you have multiple threads that can alter the database.
aisnote
In need of some credit
In need of some credit
Posts: 7
Joined: Sat Nov 21, 2020 12:06 am

Re: is wxSQLite3 Resultset thread safe?

Post by aisnote »

thanks. I solved with one map to catch the thread id and connection. Since I reused the thread pool.
Post Reply