SQLite SharedCache MultiThread Reads

Question

I am developing a multi-threaded sqlite database application on an Ubuntu virtual machine that has 4 processors allocated to it. I am using sqlite version 3.7.13. I created a test to verify that multiple threads/connections can read from the database at the same time.

I have two executables. The first executable simply creates a database, creates 1 table in that database, inserts 50 items into that table, and then closes the database. This does not involve any multi-threading at all and is simply meant to provide a database with entries in it.

The second executable creates multiple threads to read from the database and waits for them to finish and records the time that it took for all of the threads to complete. Each thread does the following: -create a database connection using sqlite_open_v2() so that each thread has its own individual connection to the database created from the first executable -perform 100000 SELECTS on the one database table (each select queries for one row in the table) -close the database connection

When I ran this test with SQLITE_OPEN_READWRITE specified as the flags for sqlite_open_v2 in each thread, I get the following results for total time to perform all queries:

1 Thread - 0.65 seconds 2 Threads - 0.70 seconds 3 Threads - 0.76 seconds 4 Threads - 0.91 seconds 5 Threads - 1.10 seconds 6 Threads - 1.28 seconds 7 Threads - 1.57 seconds 8 Threads - 1.78 seconds

These results were as expected as the times increase just a little (probably from context switching between threads and other causes) as I add threads, meaning that reads are basically being done in parallel.

However, when I ran this same test with SQLITE_OPEN_READWRITE | SQLITE_OPEN_SHAREDCACHE for the flags, I get the following results:

1 Thread - 0.67 seconds 2 Threads - 2.43 seconds 3 Threads - 4.81 seconds 4 Threads - 6.60 seconds 5 Threads - 8.03 seconds 6 Threads - 9.41 seconds 7 Threads - 11.17 seconds 8 Threads - 12.79 seconds

From these results, it appears that something in shared cache mode is preventing multiple reads from happening in the database at the same time. I have verified that indeed different the threads are running in parallel (thread 4 reads, thread 8 reads, thread 2 reads, etc. rather than thread 1 performs all its reads, thread 2 performs all its reads, thread 3 performs all its reads, etc.). However, it appears that the reads for each individual transaction are being done in serial, or something else is slowing down the database in shared cache.

Why am I seeing such a high increase in times as I add threads in shared cache mode as opposed to without it? Is there a way to fix this and still use shared cache mode?

Thanks for any help. It is much appreciated.

Answer 1

Currently, I can only say that in shared cache mode each thread do additional read mutex lock() on each request several times (lock at shared cache, lock master table, lock requested table). Of course, it have some overhead.

To avoid this you can use PRAGMA read_uncommitted = true;, but this can lead to inconsistent query results if another database connection modifies a table while it is being read, but it also means that a read-transaction opened by a connection in read-uncommitted mode can neither block nor be blocked by any other connection.

(can you provide your code?)