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".