Sqlite3 threading mode

[sapristi]

Hello, is there any particular reason why the sqlite3 driver uses the FULLMUTEX mode ?

cf https://github.com/paurkedal/ocaml-caqti/blob/master/lib-driver/caqti_driver_sqlite3.ml#L540 Sqlite3.db_open ~mutex:`FULL ?mode (Uri.path uri |> Uri.pct_decode))

Edit: to be a bit more specific, I'm using caqti-driver-sqlite3 in a Lwt multithreaded program, and I am encountering Invalid concurrent usage of SQLite connection detected. error messages, so I was wondering if that could cause this issue.

After reading sqlite3 documentation a bit more carefully (https://sqlite.org/threadsafe.html) :

Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. `

a solution might be to open a connection each time i need one.

[paurkedal]

The way I read the the documentation either mode allows connections to be used within the thread they were created, also concurrently, while ~mutex:`FULL also allows the same connection to be used across threads. The latter could happen if the application uses a single connection pool for multiple threads. So, unless we forbid that, I think this is the safe choice. (I would advice against using the same connection across threads though, and I think the only reason I chose ~mutex:`FULL was to avoid memory corruption issues, as they can be hard to debug.)

That leaves the question what causes the failure. Does the application use a shared connection or connection pool? And could it be the sqlite3 library is compiled without support for full mutex mode?

[sapristi]

And could it be the sqlite3 library is compiled without support for full mutex mode?

Yes that seems to be the issue, i have

sqlite> pragma COMPILE_OPTIONS;
COMPILER=gcc-10.2.0
...
THREADSAFE=1

I have tried using a connection pool, with the same results. Opening a new connection in each thread seems to be working :+1:

I have just one more question: what is the behaviour of using a connection pool with the caqti-sqlite3 driver ? In the source I can read

  Caqti_driver_info.create
    ~uri_scheme:"sqlite3"
    ~dialect_tag:`Sqlite
    ~parameter_style:(`Linear "?")
    ~can_pool:false
    ~can_concur:false
    ~can_transact:true
    ~describe_has_typed_params:false
    ~describe_has_typed_fields:true
    ()

which seems to indicate connection pools are not supported.

Thanks for your time !

[paurkedal]

What still puzzles me though is that THREADSAFE=1 (just as mutex:`FULL) means serialized mode, and according to the documantion "Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction." which I would interpret to include unrestricted use of connection objects, when opposed to "Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.".

But in any case, it has not been the intention to support connections or connection pools being used outside the thread where they were created, so I'll update the documentation to make that clear.

And you're right about pooling essentially being disabled. ~can_pool:false has to do with the connection being local and ~can_concur has to do with the possibility that the library is compiled without threading support. Both have the same effect currently though, and I considering making some changes here.

[sapristi]

Hmm yes I keep on mixing up serialized and single-threaded, I don't know why...

Thank you !