Call sqlite3_config with SQLITE_CONFIG_MULTITHREAD

[SymbolicFrank]

Is it possible to call sqlite3_config to turn on mulithread mode when using the TSQLite3Connection? The handle is private. Because, as it is, running multiple threads generates an error that the database is locked. Not in all combinations, but very often.

[SymbolicFrank]

Or is there another way to select multithread mode for SQLite?

[rvk]

Even when using SQLITE_CONFIG_MULTITHREAD you should make sure you don't access the database at the same time.

The application is responsible for serializing access to database connections and prepared statements. But other mutexes are enabled so that SQLite will be safe to use in a multi-threaded environment as long as no two threads attempt to use the same database connection at the same time.

https://sqlite.org/c3ref/c_config_getmalloc.html

So if it works some of the time but not others, you probably run into the fact you are accessing it at the same time. You'll need to build in some locking mechanisme (maybe with Critical sections).

Besides that, I'm not sure TSQLite3Connection itself is threadsafe but maybe someone else knows.

BTW is SQLITE_CONFIG_MULTITHREAD really needed. SQLITE_CONFIG_SERIALIZED should already give you thread safety.

This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1) the SQLite library will itself serialize access to database connections and prepared statements so that the application is free to use the same database connection or the same prepared statement in different threads at the same time. If SQLite is compiled with the SQLITE_THREADSAFE=0 compile-time option then it is not possible to set the Serialized threading mode and sqlite3_config() will return SQLITE_ERROR if called with the SQLITE_CONFIG_SERIALIZED configuration option.

So I think there must be something else wrong if you get errors (if you are NOT using SQLITE_CONFIG_MULTITHREAD actively).

[SymbolicFrank]

I use multiple threads, all with their own data and TSQLite3connection. Theoretically, concurrent access should result in retries, up to the maximum timeout of 5 seconds. But they give an error that the database is locked. And they all write to different tables.

If I have a "select .. from tablex" statement and I run a "select count(*) from tablex" statement at the same time on a different connection, it gives an error. Ok, I can understand that. But inserting data in different tables through different connections should work. Well, sometimes it does: I can write to multiple small tables (just a few fields/records), but just one big one (many fields).

But, except for rebuilding SQLite.dll with the correct settings, there doesn't seem to be a way to configure it (from FPC) without modifying the TSQLite3connection class.

And if I try to read a table after inserting data in it from a different connection, I get the error that "the schema has changed". Probably because it is cached in the connection. I'll see if I can make sure the connection is flushed/closed.

[rvk]

Ok, I can understand that. But inserting data in different tables through different connections should work. Well, sometimes it does: I can write to multiple small tables (just a few fields/records), but just one big one (many fields).

No, concurrent SELECTs can be handled just fine by SQLite. But when writing to the database it becomes very tricky.
https://www.sqlite.org/lockingv3.html

After a SHARED lock is obtained, a RESERVED lock must be acquired. The RESERVED lock signals that the process intends to write to the database at some point in the future. Only one process at a time can hold a RESERVED lock. But other processes can continue to read the database while the RESERVED lock is held.

As you already stated (flush and close connection), a process needs to release the RESERVED lock as soon as possible because only one process can hold a RESERVED lock.

And:

If the process that wants to write is unable to obtain a RESERVED lock, it must mean that another process already has a RESERVED lock. In that case, the write attempt fails and returns SQLITE_BUSY.

So that must be happening for you when you write in multiple threads at the same time.
TABLE level doesn't matter, SQLite is file based and doesn't do TABLE-locking.

But, except for rebuilding SQLite.dll with the correct settings, there doesn't seem to be a way to configure it (from FPC) without modifying the TSQLite3connection class.

Why would you need to rebuild SQLite.dll? It is default already compiled with SQLITE_CONFIG_SERIALIZED which is the most flexible option for multi-threading.
https://www.sqlite.org/threadsafe.html

If I have a "select .. from tablex" statement and I run a "select count(*) from tablex" statement at the same time on a different connection, it gives an error.

BTW, this should be possible. As long as there is no other process that's writing to the table. That one will get an EXCLUSIVE lock after which no other connection can do a read.

An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held.

[SymbolicFrank]

Ok, thanks. That confirms my suspicions. I'll serialize everything.

I'm not sure I want to switch to Firebird Embedded just for that. The application is a simple tool, having to install it would beat the purpose. In that case, I rather create a central PostgreSQL server somewhere. I expect it'll end up on an S3 instance somewhere anyway in a few years.

[Thaddy]

FireBird suffers from the same issue and also holds exclusive locks. Every multi-user scenario does that: it is required.
What can differ is the amount of data being locked. Some databases lock more of the data than necessary.
It is all about data integrity and that simply needs strong exclusivity on write. Even the big guns have some means for that.
Even e.g. Oracle, with the highest concurrency in the market, has a - configurable - very finely grained resolution where in the deep there is still an exclusive lock on small or depending on configuration very small pieces of data.
Such a lock is generally just held on the data to commit. Actually two write locks at the minimum: client data is also locked at commit.

[SymbolicFrank]

Yes, I know how it works. The big ones cache the data and write the transactions in the log, to be processed. So, while the actual access has locks, nothing keeps you from pumping all the tables full of data through multiple connections. Although trying to read such a table (or a query that accesses multiple of those tables) can take a while, as it first has to be indexed and it has to traverse the cache to get the data.

Of course, when you change the structure of the tables, things get complicated. That's why I think the SQLite error that "the schema has changed" in my case is wrong, because the structure doesn't change. It's just that the queries hold stale data. So, it seems SQLite only keeps a single state.

So, in my specific case, I think FireBird would work. But I'm not going to install a database server on each client. And if I'm going to use a central one, I prefer a PostgreSQL on an S3 slice. That way, I don't have to manage it.

[rvk]

So, in my specific case, I think FireBird would work. But I'm not going to install a database server on each client. And if I'm going to use a central one, I prefer a PostgreSQL on an S3 slice. That way, I don't have to manage it.

The embedded version of Firebird (no install needed) should work with multi-threading.

The client libraries, including the embedded one, can now be used in multi-threaded applications without any application-level synchronization.

The embedded libraries—libfbembed.so on POSIX and fbembed.dll on Windows—are now multi-thread-capable and thread-safe, so they can be used in multi-threaded applications.

https://firebirdsql.org/rlsnotesh/rnfb25-engine.html

But building in some application-level synchronization for SQLite should work too.

Of course, a solid real server environment would be even more stable.