sqlite3_step return code 5 with connection pool about sqlpp11 HOT 2 CLOSED

This is really a problem with SQLite3 which is rather mediocre when handling parallel transactions (just like most transactional databases) and the error that you see is not really caused by SQLPP11's connection pools or even by SQLPP11.

The multithreaded test is getting the SQLite3 error SQL_BUSY with integer code 5.

When handling parallel transactions SQLite3 does (somewhat) optimistic locking. Transactions just go in parallel and when there is a conflict between two of them (that is two transactions try to acquire a write lock), one of them succeeds and the other gets an automatic rollback with SQL_BUSY. In that respect it is quite similar to PostgreSQL where instead of SQL_BUSY you get serialization_failure (40001) or deadlock_detected (40P01).

Essentially you have two ways of handling these errors in SQLite3:

Option 1. The quick and dirty solution. For each connection handle that you get (from the pool or by creating it manually) install a busy timeout by calling sqlite3_busy_timeout() on the native connection under the handle. Something like this:

auto& conn = pool.get();
sqlite3_busy_timeout(conn.native_handle(), 10000); // SQL_BUSY if we cannot acquire lock after 10 seconds

Then use explicit or implicit transactions with SQLite3 and hope that your transactions don't span more than 10 seconds (or whatever timeout you set). You can issue explicitly BEGIN IMMEDIATE/EXCLUSIVE but it is not really mandatory because by default SQLite3 uses autocommit mode and sqlpp11's explicit transactions create transactions in DEFERRED mode which is probably good enough.

For SQLite3 I would probably go with with the quick-and-dirty option, because in most cases you are not going to run any complex transactions/queries with SQLite3.

Option 2. Handle automatic transaction rollbacks (i.e. SQL_BUSY) properly and upon getting a rollback retry the whole transaction until it succeeds. We discussed a similar approach for MySQL connection fails and for PostgreSQL transactions here #530 but it is pretty much the same for SQLite3 transaction failures.

If you are interested in the details of SQLite3 transaction handling and why you are getting SQL_BUSY, you can read these two documents:
https://activesphere.com/blog/2018/12/24/understanding-sqlite-busy
https://www.sqlite.org/lang_transaction.html

Actually I have a transaction manager class that retries transactions for PostgreSQL. If someone is interested I can upload it. It is quite simple but works well for PostgreSQL. It can be ported to the other connectors (MySQL and SQLite3), but before that we really need to fix the exceptions thrown by the MySQL/SQLite3 connectors because right now they just throw a generic sqlpp::exception() so the transaction manager does not have an easy way of detecting the error type.

from sqlpp11.

Okay I understood, thank you!

from sqlpp11.