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.