Support for setting SQLite Threading at Runtime

[buko]

Based of #199 it seems xerial doesn't support true concurrent access to SQLite.

Just to confirm the current expected behavior of xerial:

1. Xerial's sqlite is compiled with SQLITE_THREADSAFE=1. This sets the threading mode to Serialized. Multiple threads can access a SQLite database at the same time but each thread must obtain its own JDBC Connection.
2. The threading-model is serialized. This means, I think, that the threads won't actually proceed concurrently. A second thread won't be able to do anything until the first thread closes its Connection. The underlying SQLite db is threadsafe but only offers serialized concurrency level.
3. It is possible to change the sqlite concurrency level at runtime but Xerial does not offer a Java API. At least there's nothing in the SQLiteConfig class that can be used to change this.

Is this correct?

Perhaps support setting the threading mode at runtime via a URI parameter eg jdbc:sqlite:c:\temp\test.db;threading=[single|multi|serialized] see this old bitbucket issue or a field on SQLiteConfig.

[mruffing]

Can database connections be configured to multi-thread mode by calling SQLiteConfig.setOpenMode with SQLiteOpenMode.NOMUTEX?

Reference Specifically section 4, using the NOMUTEX flag when calling sqlite3_open_v2.

[exuvo]

I did some tests with DB on disk, JournalMode WAL and MEMORY, FOREIGN_KEYS=true (but had none), SYNCHRONOUS=OFF. NOMUTEX made no difference for concurrent (5 connections) inserts or reads to 1 table. Also tried FULLMUTEX which also gave no difference so i don't think the setting is taking at all.

Writes had same performance with 1 and 5 connections (expected).

Irregardless of settings reads (100st select * from a thread pool) saw some improvement with multiple threads (8s went down to 3 but far from the linear improvement I would expect).

-edit- Well i should have read https://github.com/xerial/sqlite-jdbc/issues/348#issuecomment-396561063 first, setting is not used.

[mruffing]

It looks like sqlite3_open_v2 is being used: https://github.com/xerial/sqlite-jdbc/blob/master/src/main/java/org/sqlite/core/NativeDB.c#L533

[exuvo]

Maybe i need to test with multiple tables then, might be some other locking going on there.

[davidhedley]

Could you please consider adding something to SQLiteConfig that enables multi-threading? It just needs to be a flag that results in a call to sqlite3_config(SQLITE_CONFIG_MULTITHREAD) before the first call to sqlite3_open_v2?

The inability to handle multiple concurrent selects is proving a significant bottleneck.

[edit] Actually, looking at the code, it should be possible to pass SQLiteOpenMode.NOMUTEX as described above which should enable multi-threading as this is passed directly through to sqlite3_open_v2. Has anyone tried this with success?

[2nd edit] Doing my own testing with multiple threads reading the same sqlite file, it seems the NOMUTEX/FULLMUTEX option makes no difference at all to select performance (test run locally on Windows 10). What does make a difference is it is significantly quicker for each thread to have its own connection rather than sharing a connection, although both do work.

[mruffing]

@davidhedley It has been a while since I looked at this but based on you and @exuvo's testing it looks like there might be some type of locking happening at the Java/JDBC level. If this is the case this might explain why the different thread SQLite modes (native level) are not making a difference in read performance.

[gotson]

Is this still happening on the latest version?

[gotson]

According to https://www.sqlite.org/threadsafe.html:

The threading mode for an individual database connection is determined by flags given as the third argument to sqlite3_open_v2(). The SQLITE_OPEN_NOMUTEX flag causes the database connection to be in the multi-thread mode and the SQLITE_OPEN_FULLMUTEX flag causes the connection to be in serialized mode. If neither flag is specified or if sqlite3_open() or sqlite3_open16() are used instead of sqlite3_open_v2(), then the default mode determined by the compile-time and start-time settings is used.

Those 2 flags are already defined, and properly passed when calling open.

I will close this as the support is already there.

Feel free to open a new issue once there's more findings that would explain the fact it doesn't make any difference in performance.