How to minimize the mutex locking for an object when only 1 thread mostly uses that object and the other thread(s) use it rarely?

Question

Scenario

Suppose there are "Thread_Main" and "Thread_DB", with a shared SQLite database object. It's guaranteed that,

• "Thread_main" seldom uses SQLite object for reading (i.e. SELECT())
• "Thread_DB" uses the SQLite object most of the time for various INSERT, UPDATE, DELETE operations

To avoid data races and UB, SQLite should be compiled with SQLITE_THREADSAFE=1 (default) option. That means, before every operation, an internal mutex will be locked, so that DB is not writing when reading and vice versa.

"Thread_Main"    "Thread_DB"    no. of operation on DB
=============    ===========    ======================
something           INSERT             1
something           UPDATE             2
something           DELETE             3
something           INSERT             4
...                   ...             ...         (collapsed)
something           INSERT            500
something           DELETE            501
...                   ...             ...         (collapsed)
something           UPDATE            1000
something           UPDATE            1001
...                   ...             ...         (collapsed)
SELECT              INSERT            1200  <--- here is a serious requirement of mutex
...                   ...             ...         (collapsed)

Problem

As seen in above, out of 100s of operations, the need of real mutex is required only once in a while. However to safeguard that small situation, we have to lock it for all the operations.

Question: Is there a way in which "Thread_DB" holds the mutex most of the time, so that every time locking is not required? The lock/unlocks can happen only when "Thread_Main" requests for it.

Notes

• One way is to queue up the SELECT in the "Thread_DB". But in larger scenario with several DBs running, this will slow down the response and it won't be real time. Can't keep the main thread waiting for it.
• I also considered to have a "Thread_Main" integer/boolean variable which will suggest that "Thread_Main" wants to SELECT. Now if any operation is running in "Thread_DB" at that time, it can unlock the mutex. This is fine. But if no writeable operation is running on that SQLite object, then "Thread_main" will keep waiting, as there is no one in "Thread_DB" to unlock. Which will again delay or even hang the "Thread_Main".

Answer 1

Here's a suggestion: modify your program somewhat so that Thread_Main has no access to the shared object; only Thread_DB is able to access it. Once you've done that, you won't need to do any serialization at all, and Thread_DB can work at full efficiency.

Of course the fly in the ointment is that Thread_Main does sometimes need to interact with the DB object; how can it do that if it doesn't have any access to it?

The solution to that issue is message-passing. When Thread_Main needs to do something with the DB, it should pass a Message object of some sort to Thread_DB. The Message object should contain all the details necessary to characterize the desired interaction. When Thread_DB receives the Message object, Thread_DB can call its execute(SQLite & db) method (or whatever you want to call it), at which point the necessary data insertion/extraction can occur from within the context of the Thread_DB thread. When the interaction has completed, any results can be stored inside the Message object and the Message object can then be passed back to the main thread for the main thread to deal with the results. (the main thread can either block waiting for the Message to be sent back, or continue to operate asynchronously to the DB thread, it's up to you)