sqlite3_column mutex

(1) By anonymous on 2022-09-08 14:46:31 [source]

Hi,

We are trying to find potential performance improvements in our application. Let me first give a bit of background on how our application uses sqlite:

There are many connections to the same sqlite DB in our application.
Each connection is opened with SQLITE_OPEN_FULLMUTEX.
Many threads can use the same sqlite DB connection.
Only one thread may use a sqlite3_stmt at a time. In fact, a particular thread will step() on a stmt and fetch (and copy) all of the column values out with sqlite3_column* methods before any other thread calls step() or some other sqlite function on the same sqlite3_stmt.

We noticed the sqlite3_column* functions takes the per-connection sqlite mutex. With the assumptions above, it seems safe to not take the mutex in this particular use case. Are there any gotchas or reasons why this is a bad idea?

Thanks!

(2) By Richard Hipp (drh) on 2022-09-08 14:59:47 in reply to 1 [link] [source]

Every prepared statement contains a pointer to its database connection, and might make changes to data elements contained within the database connection object. Hence, it is important that the prepared statement told the database connection mutex while it is active, if the same database connection might be used by two or more threads.

(5) By Joe M (jmonaco) on 2022-09-08 15:54:34 in reply to 2 [link] [source]

Is there any sqlite3_column_* function that this might be safer for?

In particular we are curious about sqlite3_column_value.

(3) By Gunter Hick (gunter_hick) on 2022-09-08 15:20:43 in reply to 1 [link] [source]

Why are you using multiple connections and sharing them between multiple threads (which requires serialized mode to operate) instead of giving each thread its own connection, which would work in threadsafe mode?

(4) By anonymous on 2022-09-08 15:42:01 in reply to 3 [link] [source]

Initially our model showed decent performance and it balanced memory/open FDs well. In our application, there may be 300 or so threads attempting to read from the database at any given time. That said, we are open to attempting to use a thread pool of private sqlite connection using SQLITE_OPEN_NOMUTEX if it shows better performance under high read loads.

With a sqlite connection opened with SQLITE_OPEN_NOMUTEX, can two different sqlite3_stmt objects be used at the same time even if they are prepared on the same sqlite DB connection?

(6.1) By Keith Medcalf (kmedcalf) on 2022-09-08 17:48:31 edited from 6.0 in reply to 4 [link] [source]

With a sqlite connection opened with SQLITE_OPEN_NOMUTEX, can two different sqlite3_stmt objects be used at the same time even if they are prepared on the same sqlite DB connection?

Apendages of connections (such as statements, or anything else which is referrs to a connection) may only be accessed by a single thread at a time. Ever. No exceptions.

SQLITE_OPEN_FULLMUTEX uses mutexes in the library code itself to ensure your compliance with the stated requirement. Failure to do so means that the "subsequent threads" will have to wait until the "prior threads" using something associated with the same connection completes, and that this is enforced by the library uitself.

SQLITE_OPEN_NOMUTEX does not alter the requirement. It merely turns off the code and mutexes in the library which provide "insurance" (at a cost, of course) that your code will behave "with propriety". And just like in the real world, you can choose to self-insure. This means that you do not pay the "insurance fee" (the cost of having the library ensure that you are behaving yourself), however any mishaps that ensue will be entirely your fault and entirely at your cost. (and if re-instating SQLITE_OPEN_FULL_MUTEX makes the problem "go away", then the problem is well and truly as a result of your bad behaviour).

In either mode you can access appendages of different connections in separate threads (each connection/appendage being confined to being used by a "single thread at a time").

(7.1) By Gunter Hick (gunter_hick) on 2022-09-09 06:07:41 edited from 7.0 in reply to 4 [link] [source]

The proper object to serialize SQLite access in multi-thread mode is the connection, not the statement.

Preparing exactly one statement per connection will allow this to be achieved without much change; threads will still compete for access to specific statements, but without the overhead of the connection mutex. Heavily used statements could be duplicated (same statement on a different connection).

Alternatively, each connection could have all statements prepared. A thread wishing to run a specific statement (or statements) would gain control of any "unused" connection, run one or more statements and then reliquish control of the connection. Threads would then compete for connections, with the risk that "selfish" threads running multiple statements could starve others needing just one statement.

There are many possible assignments of statements to threads in between these extremes.

Another approach would be to apply the client-server model to database access. Threads don't read directly from the database, but send requests to a database dispatcher. Each database worker thread has its own dedicted connection with its private statement(s) and the result is passed back to the requesting thread.