Page 1 of 1

wxsqlite3 - can it be used in a multi-threaded application

Posted: Fri Jun 28, 2019 11:00 am
by deepti
Hi All,

We are using wxsqlite3 (using source files) in our multi-threaded C++ application.
There is only one instance of DB connection.
So far we have been using a separate mutex. Whenever any thread wants to write/read the DB, it acquires the mutex and releases when done.
Now, the GUI thread also accesses the DB to display a list.
When any other thread is busy accessing the DB for write operations and it is time consuming, and the GUI thread also has to access the DB at the same time, it ends up waiting for the mutex and this results in the UI hanging!
Is it possible that we can use a separate mutex for accessing the database table that is required by the UI thread?
If yes, then it is possible that multiple threads are accessing the DB at the same time. Is this a safe option?
If not, could you please suggest other alternatives?


Regards,
Deepti

Re: wxsqlite3 - can it be used in a multi-threaded application

Posted: Fri Jun 28, 2019 1:50 pm
by doublemax
I think wxSQLite3 already handles that for you:
viewtopic.php?p=128586#p128586

Re: wxsqlite3 - can it be used in a multi-threaded application

Posted: Fri Jun 28, 2019 2:00 pm
by deepti
Thank you doublemax.
Does wxSqlite3 open the DB in serialized mode by default?
From the docs i see that this is a primary requirement for safe multi-threaded access.
The wxSQLite3Database::Open function internally calls "sqlite3_open". Whereas it is the "sqlite3_open_v2" function which actually provides a flag parameter through which "SQLITE_OPEN_FULLMUTEX" flag value can be specified, resulting in safe multi-threaded access.

Re: wxsqlite3 - can it be used in a multi-threaded application

Posted: Fri Jun 28, 2019 2:37 pm
by doublemax
wxSQLite3Database::Open does call sqlite3_open_v2 and you can even pass the flags to it:

Code: Select all

void wxSQLite3Database::Open(const wxString& fileName, const wxMemoryBuffer& key, int flags)
{
  wxCharBuffer strFileName = fileName.ToUTF8();
  const char* localFileName = strFileName;
  sqlite3* db;

  int rc = sqlite3_open_v2((const char*) localFileName, &db, flags, NULL);
Flags:

Code: Select all

#define WXSQLITE_OPEN_READONLY         0x00000001
#define WXSQLITE_OPEN_READWRITE        0x00000002
#define WXSQLITE_OPEN_CREATE           0x00000004
#define WXSQLITE_OPEN_URI              0x00000040
#define WXSQLITE_OPEN_MEMORY           0x00000080
#define WXSQLITE_OPEN_NOMUTEX          0x00008000
#define WXSQLITE_OPEN_FULLMUTEX        0x00010000
#define WXSQLITE_OPEN_SHAREDCACHE      0x00020000
#define WXSQLITE_OPEN_PRIVATECACHE     0x00040000

Re: wxsqlite3 - can it be used in a multi-threaded application

Posted: Fri Jun 28, 2019 10:15 pm
by utelle
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.
deepti wrote:
Fri Jun 28, 2019 2:00 pm
From the docs i see that this is a primary requirement for safe multi-threaded access.
The wxSQLite3Database::Open function internally calls "sqlite3_open". Whereas it is the "sqlite3_open_v2" function which actually provides a flag parameter through which "SQLITE_OPEN_FULLMUTEX" flag value can be specified, resulting in safe multi-threaded access.
wxSQLite3Database::Open calls "sqlite3_open_v2", unless you use an old outdated wxSQLite3 version (below version 1.9.6 published almost 10 years ago). Serialized mode is the default. That is, you need to specify a flag only, if you want to select multi-thread mode.

Re: wxsqlite3 - can it be used in a multi-threaded application

Posted: Fri Jul 05, 2019 1:05 pm
by deepti
Thanks a lot utelle and doublemax for your replies. They were indeed helpful.
There is one more question, where i need your help.
We have the following lines in our application, and I noticed that when the DB size is very huge (a couple of GBs big), these lines take upto 2 minutes to execute.
But, funnily, this happens only the first time the application is run. The next time it takes a few seconds only.
Just for testing purposes, if i make a copy of the entire DB, delete the old DB, and then run the application using the copied DB, it again takes up to 2 mins.
Could you help me understand what is happening behind the scenes?

Code: Select all

wxSQLite3StatementBuffer bufSQL;
bufSQL.Format("PRAGMA quick_check;");
wxSQLite3ResultSet q1 = m_con->ExecuteQuery(bufSQL);
while (q1.NextRow())
{
	result = q1.GetStdString("integrity_check");
}

Re: wxsqlite3 - can it be used in a multi-threaded application

Posted: Fri Jul 05, 2019 3:22 pm
by doublemax
My guess would be that it's just the file caching of the OS.

Re: wxsqlite3 - can it be used in a multi-threaded application

Posted: Sat Jul 06, 2019 1:59 pm
by utelle
deepti wrote:
Fri Jul 05, 2019 1:05 pm
We have the following lines in our application, and I noticed that when the DB size is very huge (a couple of GBs big), these lines take upto 2 minutes to execute.
But, funnily, this happens only the first time the application is run. The next time it takes a few seconds only.
In fact, this is not really surprising. In your query you execute the command PRAGMA quick_check;. Although this command is not as thorough as PRAGMA integrity_check;, it has to scan the whole database. For large database files this can take quite some time.

However, after the first execution large portions of the database will reside in cache memory and don't have to be read from disk again. Therefore subsequent executions of the command take significantly less time.

If the application is restarted within the same session, the execution of the command is usually also considerably faster, because - as doublemax already stated - the operating system caches files read from a hard disk to a certain degree.
deepti wrote:
Fri Jul 05, 2019 1:05 pm
Just for testing purposes, if i make a copy of the entire DB, delete the old DB, and then run the application using the copied DB, it again takes up to 2 mins.
Could you help me understand what is happening behind the scenes?
Also no surprise: the cache of the operating system will be cleared in such a case. That is, the whole database file has to be read from hard disk again.

I'd like to add another remark: Why do you perform PRAGMA quick_check; on each start up of your application? PRAGMA quick_check; or PRAGMA integrity_check; will usually only fail if the database was corrupted by some sort of hardware failure. That is, in normal operation the pragma will never report any problems.

I'd like to quote a post from Richard Hipp, the creator of SQLite, when he responded to a similar issue:
Richard Hipp wrote: PRAGMA integrity_check is only recommended after a hard crash - that is to say an operating system lock-up or a power loss. Your application crashing is not sufficient grounds for an integrity_check.

If hard-disks worked as advertised, then no integrity_check would be required even following a power loss or OS crash. But consumer-grade hard-disks cut corner to save cost, fail to honor requests to flush content to oxide, and end up losing data following a power loss. There is nothing we can do about this in software. It is a hardware bug. More recent and larger hard disks are worse about this than older and slower ones.

You can run PRAGMA quick_check as an alternative to PRAGMA integrity_check. It is a little faster, but does not do as thorough of a check of the
database file.

Re: wxsqlite3 - can it be used in a multi-threaded application

Posted: Wed Jul 17, 2019 6:00 pm
by deepti
Thanks a ton for the detailed explanation. That is very helpful!