wxSqlite3 & secondary thread Topic is solved

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
Widgets
Super wx Problem Solver
Super wx Problem Solver
Posts: 476
Joined: Thu Jun 01, 2006 4:36 pm
Location: Right here!

wxSqlite3 & secondary thread

Post by Widgets »

One of my apps uses wxSqlite3 to record data imported from an XML file; right now, the DB is created and populated from the main thread, but I want to move that job into a secondary thread.
Searching this forum, I have found this topic viewtopic.php?t=46048, but it does not make it clear enough for me just how to implement the recommendations.
deepti wrote: ↑Fri Jun 28, 2019 2:00 pm
Does wxSqlite3 open the DB in serialized mode by default?

Yes, the serialized mode (THREADSAFE=1) is the default mode selected at compile time (unless you change the compile time options). That is, it is possible to select at runtime whether a database connection should use multi-thread threading mode or serialized threading mode by passing the corresponding flags to the Open method.

However, even in serialized mode I would strongly recommend to create a database connection per thread. It is seldom a good idea to pass the same database connection object between different threads.
I have verified - by reading back the flags - that the code I am using has THREADSAFE=1
What I am unclear about is how to create a database connection in the secondary thread.
At present there is no code to present for discussion, because of that question.
Environment: Win 10 64-bit & Mint 20.3
MSVC Express 2019
wxWidgets 3.1.4
utelle
Moderator
Moderator
Posts: 1086
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: wxSqlite3 & secondary thread

Post by utelle »

Widgets wrote: Mon Aug 01, 2022 4:28 pm One of my apps uses wxSqlite3 to record data imported from an XML file; right now, the DB is created and populated from the main thread, but I want to move that job into a secondary thread.
So, move all DB related code to methods of the secondary thread. That should be all you have to do.
Widgets wrote: Mon Aug 01, 2022 4:28 pm I have verified - by reading back the flags - that the code I am using has THREADSAFE=1
What I am unclear about is how to create a database connection in the secondary thread.
At present there is no code to present for discussion, because of that question.
SQLite supports three different threading modes (see SQLite in Multi-Threaded Applications for details). THREADSAFE=1 aka SERIALIZED is the default mode. In this mode a database connection can be used from different threads. Nevertheless for wxSQLite3 it is recommended to use a database connection (that is, a wxSQLite3Database instance) only within the thread that created the database connection. The easiest way to do that is to instantiate a wxSQLite3Database object (and typically also to call its Open method) in the thread constructor. A database connection will use the default threading mode, unless it is overwritten by explicitly specifying a different mode via a flag in the Open method.
Widgets
Super wx Problem Solver
Super wx Problem Solver
Posts: 476
Joined: Thu Jun 01, 2006 4:36 pm
Location: Right here!

Re: wxSqlite3 & secondary thread

Post by Widgets »

My apologies for not being clear enough.

I also need to access the data in the DB from the main GUI thread.
And if at all possible, while the secondary thread is still loading/updating some of the information.

IOW, at least at some times, I need a db object in both threads, although there is the option to populate a secondary db and then switch them when the job is done.
As I would like understand the more general case when I do need to access the same db from both threads, my preferred solution would be to sort out the issues without using a temporary db file, if at all possible.

Some comments stated that I would have to protect access via a mutex and I can see that as one solution, but then, while going through the wxSqlite3 documentation, I found
void wxSQLite3Database::Begin( wxSQLite3TransactionType transactionType = WXSQLITE_TRANSACTION_DEFAULT )
where I apparently can use the flag WXSQLITE_TRANSACTION_EXCLUSIVE
(Not sure if the comment in viewtopic.php?t=7319
Use of 'DB Transcation' blocks (BeginTransaction & EndTransaction calls), can make your DB calls thread safe (and usually faster) refers to this - because I have not found any EndTransaction() call)

This seemed to be an alternative way to get what I think I need, but that leaves me with a couple of questions:
1) is this a workable alternative
2) if it is, if the Begin() call starts an exclusive access command, when or how does that finish and allow another Begin() to be executed
My first assumption would be when the Begin() returns, but I'd prefer not to make too many assumptions

If the samples which are part of the wxSqlite3 package demonstrate this, I have missed it. Nor have I found any example which demonstrates one or the the other approach.
Environment: Win 10 64-bit & Mint 20.3
MSVC Express 2019
wxWidgets 3.1.4
utelle
Moderator
Moderator
Posts: 1086
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: wxSqlite3 & secondary thread

Post by utelle »

Widgets wrote: Mon Aug 01, 2022 8:58 pm I also need to access the data in the DB from the main GUI thread.
And if at all possible, while the secondary thread is still loading/updating some of the information.
Of course, this can be done. However, you should open a separate database connection for your main thread and your secondary thread. Additionally, you have to keep in mind that SQLite locks the whole database file while a transaction is underway. Read transactions can be executed in parallel, but as soon as you have a write transaction, it can only start if there are no active read transactions, and as soon as the write transaction starts, read transactions have to wait unti it has finished. To avoid deadlocks it is recommended to have only one writer thread.
Widgets wrote: Mon Aug 01, 2022 8:58 pm IOW, at least at some times, I need a db object in both threads, although there is the option to populate a secondary db and then switch them when the job is done.
You can have a single database file to which you establish a database connection (via wxSQLite3Database object) from both threads.
Widgets wrote: Mon Aug 01, 2022 8:58 pm As I would like understand the more general case when I do need to access the same db from both threads, my preferred solution would be to sort out the issues without using a temporary db file, if at all possible.
Most likely you don't need a separate temporary db file. However, it depends on the actual needs of your application. If your main thread depends on db data that the secondary thread is about to create, your main thread needs to check whether the required data are already available. Instead of polling the db it would be better if the secondary thread sends a signal to the main thread after completion in such a case.
Widgets wrote: Mon Aug 01, 2022 8:58 pm Some comments stated that I would have to protect access via a mutex and I can see that as one solution,
You don't have to do that manually. It is done for you automatically by SQLite and wxSQLite3.
Widgets wrote: Mon Aug 01, 2022 8:58 pm but then, while going through the wxSqlite3 documentation, I found
void wxSQLite3Database::Begin( wxSQLite3TransactionType transactionType = WXSQLITE_TRANSACTION_DEFAULT )
where I apparently can use the flag WXSQLITE_TRANSACTION_EXCLUSIVE
(Not sure if the comment in viewtopic.php?t=7319
Use of 'DB Transcation' blocks (BeginTransaction & EndTransaction calls), can make your DB calls thread safe (and usually faster) refers to this - because I have not found any EndTransaction() call)
Database transactions allow to guarantee a consistent database state. If a database connection writes data to a database file from within a transaction, the data are only visible for other database connections after the transaction completed. This is especially important if a write operation affects 2 or more database tables.

As a side effect transactions can speed up processing time considerably. For example, if you have thousands of insert statements, SQLite would be forced to update the database file after each insert statement, if you don't bundle them into a single transaction. Bundled into a single transaction there will occur only a single database file update when the transaction is completed.
Widgets wrote: Mon Aug 01, 2022 8:58 pm This seemed to be an alternative way to get what I think I need, but that leaves me with a couple of questions:
1) is this a workable alternative
2) if it is, if the Begin() call starts an exclusive access command, when or how does that finish and allow another Begin() to be executed
My first assumption would be when the Begin() returns, but I'd prefer not to make too many assumptions
Alternative for what? A transaction is an SQL mechanism to guarantee a consistent database state. If you don't use explicit transactions (begin ... commit/rollback), SQLite handles each statement implicitly as a separate transaction. If a write operation affects more than one database table, transactions should always be used.

A transaction ends when it is completed by executing either a COMMIT (to confirm) or a ROLLBACK (to cancel) statement. The more important question is when a transaction starts. This depends on the transaction mode: DEFERRED, IMMEDIATE, or EXCLUSIVE (default is DEFERRED), for details see SQLite documentation.
Widgets wrote: Mon Aug 01, 2022 8:58 pm If the samples which are part of the wxSqlite3 package demonstrate this, I have missed it. Nor have I found any example which demonstrates one or the other approach.
Currently wxSQLite3 does not include a multi-threaded example.
Widgets
Super wx Problem Solver
Super wx Problem Solver
Posts: 476
Joined: Thu Jun 01, 2006 4:36 pm
Location: Right here!

Re: wxSqlite3 & secondary thread

Post by Widgets »

utelle wrote: Tue Aug 02, 2022 7:11 am However, you should open a separate database connection for your main thread and your secondary thread.
My apologies for being so 'dense'.
As far as I can see, the only function that seems to fit here, is Open().
Does that imply that I do pass the db file name to the secondary thread, in it, open the db to get a distinct handle/object and then let sqlite3 take care of the locks by using exclusive transactions in each thread?
Do I need to bother using transactions at all?
When the secondary thread exits, I cannot close the db, but I would have to free the memory associated with the db object/handle?
To avoid deadlocks it is recommended to have only one writer thread.
That certainly would be the case in this instance, as the main thread would only read from the db.

In the more general case, though, with more than one thread as a potential 'writer', would using exclusive transactions be necessary, sufficient, superfluous or would external mutexes still be required, no matter which?
Environment: Win 10 64-bit & Mint 20.3
MSVC Express 2019
wxWidgets 3.1.4
utelle
Moderator
Moderator
Posts: 1086
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: wxSqlite3 & secondary thread

Post by utelle »

Widgets wrote: Tue Aug 02, 2022 5:26 pm As far as I can see, the only function that seems to fit here, is Open().
The method Open of a wxSQLite3Database object actually establishes a database connection.
Widgets wrote: Tue Aug 02, 2022 5:26 pm Does that imply that I do pass the db file name to the secondary thread, in it, open the db to get a distinct handle/object and then let sqlite3 take care of the locks by using exclusive transactions in each thread?
Yes. However, in general it is better to use the default transaction mode (i.e. DEFERRED), because that locks the database usually for a rather short period of time, while exclusive transactions lock the database already, when the BEGIN command is executed.
Widgets wrote: Tue Aug 02, 2022 5:26 pm Do I need to bother using transactions at all?
That depends on what your secondary thread has to accomplish. If you write data into a relation that involves more than one table, you usually want to use transactions to guarantee consistent data.
Widgets wrote: Tue Aug 02, 2022 5:26 pm When the secondary thread exits, I cannot close the db, but I would have to free the memory associated with the db object/handle?
If each thread has its own database connection (wxSQLite3Database object), the secondary thread can close its database connection without affecting the main thread.

It would be a bit different, if you use the same database connection in both threads, for example by creating and opening a database connection (wxSQLite3Database object) in your main thread and then passing a copy of the already opened wxSQLite3Database object to your secondary thread. In that case the secondary thread must not close the database connection. The destructor of wxSQLite3Database will take care of closing the database automatically.
Widgets wrote: Tue Aug 02, 2022 5:26 pm
To avoid deadlocks it is recommended to have only one writer thread.
That certainly would be the case in this instance, as the main thread would only read from the db.

In the more general case, though, with more than one thread as a potential 'writer', would using exclusive transactions be necessary, sufficient, superfluous or would external mutexes still be required, no matter which?
Using exclusive transactions is very restrictive, because it prevents read transactions while a write transaction is in progress. It will increase chances that a read transaction will fail. Using deferred transactions and keeping write transactions small will help to avoid failed transactions. However, your application must be prepared to handle a BUSY error, for example by repeating the transaction a little bit later. You can set a "busy timeout" for a database connection to assist in avoiding BUSY errors: SQLite will wait up to the specified timeout for a write transaction to finish, so that it is less likely to get a BUSY error.

Please see the SQLite transaction documentation for in-depth information.
Additionally, if you have many concurrent database connections you should consider to use the journal mode Write-Ahead Logging.
Widgets
Super wx Problem Solver
Super wx Problem Solver
Posts: 476
Joined: Thu Jun 01, 2006 4:36 pm
Location: Right here!

Re: wxSqlite3 & secondary thread

Post by Widgets »

That last reference to the Sqlite3 documentation answered all of my (current) questions :D

What I take from the information there and from this discussion, that I will to be able to let (wx)Sqlite3 look after the db access control and won't need any additional external 'magic' like mutexes, as long as my app can handle any delays due to the short term internal locking of the db ?
Environment: Win 10 64-bit & Mint 20.3
MSVC Express 2019
wxWidgets 3.1.4
utelle
Moderator
Moderator
Posts: 1086
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: wxSqlite3 & secondary thread

Post by utelle »

Widgets wrote: Tue Aug 02, 2022 8:50 pm That last reference to the Sqlite3 documentation answered all of my (current) questions :D
Fine. :D

Actually, wxSQLite3 is a thin wrapper for SQLite3. So, the SQLite documentation provides the best reference in most cases.
Widgets wrote: Tue Aug 02, 2022 8:50 pm What I take from the information there and from this discussion, that I will to be able to let (wx)Sqlite3 look after the db access control and won't need any additional external 'magic' like mutexes, as long as my app can handle any delays due to the short term internal locking of the db ?
You definitely shouldn't need additional mutexes. And if your secondary thread avoids long running transactions, then it is unlikely that your main thread will experience busy timeouts.
Widgets
Super wx Problem Solver
Super wx Problem Solver
Posts: 476
Joined: Thu Jun 01, 2006 4:36 pm
Location: Right here!

Re: wxSqlite3 & secondary thread

Post by Widgets »

Perfect.
Not needing anything extra in the main GUI thread is a big bonus, since the secondary thread will need to be run very infrequently and that extra code in the main app would be eating up CPU cycles all the time.
Environment: Win 10 64-bit & Mint 20.3
MSVC Express 2019
wxWidgets 3.1.4
Post Reply