is wxSQLite3 Resultset thread safe?
Posted: Mon Jan 31, 2011 2:19 pm
Is ResultSet thread safe? If no what precautions need to be taken to ensure no troubles?
Thanks
Thanks
Official forum for the wxWidgets Cross-Platform GUI Toolkit
https://forums.wxwidgets.org/
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:Is ResultSet thread safe?
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.evstevemd wrote:If no what precautions need to be taken to ensure no troubles?
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: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.
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: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.
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: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).
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.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.
Well, I don't know exactly what you manage to do and what are your constraints.What do you advice in such case as this?
So it seems there is no problem for me to go ahead as long as I adhere to stated warnings!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
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.evstevemd wrote:I was on SQLite3 mailing list and here is the extract of Quote from Ivanov:
So it seems there is no problem for me to go ahead as long as I adhere to stated warnings!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
I saw it Urlich and It is well explained.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
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...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.
Right. But evstevemd asked almost the same question on the SQLite mailing list and got an - IMHO misleading - answer.briceandre wrote:I don't think there was contradiction in what Utelle or me said, wrt to what you stated here.
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: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).
Yes, exactly.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.
True, but the gain would usually be small or even not existing since creating a thread isn't a cheap operation.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.
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.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...
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.