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
wxsqlite3 - can it be used in a multi-threaded application
Re: wxsqlite3 - can it be used in a multi-threaded application
I think wxSQLite3 already handles that for you:
viewtopic.php?p=128586#p128586
viewtopic.php?p=128586#p128586
Use the source, Luke!
Re: wxsqlite3 - can it be used in a multi-threaded application
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.
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
wxSQLite3Database::Open does call sqlite3_open_v2 and you can even pass the flags to it:
Flags:
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);
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
Use the source, Luke!
Re: wxsqlite3 - can it be used in a multi-threaded application
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.
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.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.
Re: wxsqlite3 - can it be used in a multi-threaded application
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?
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
My guess would be that it's just the file caching of the OS.
Use the source, Luke!
Re: wxsqlite3 - can it be used in a multi-threaded application
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.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.
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.
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
Thanks a ton for the detailed explanation. That is very helpful!