sqlite3_step return code 5 with connection pool

[Earsuit]

Hello, I'm new to this library and I found that if I change the configutation in sqlite3 ConnectionPool test to:

config->path_to_database = "test";
config->flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE ;

Then if I run this test, it gives:

Available tests:
  0. DateTime
  1. Sample
  2. Select
  3. Union
  4. With
  5. Attach
  6. DynamicSelect
  7. AutoIncrement
  8. Transaction
  9. FloatingPoint
 10. Integral
 11. Blob
 12. Connection
 13. ConnectionPool
To run a test, enter the test number: 13
test_conn_move
test_basic
Sqlite3 debug: Preparing: 'DROP TABLE IF EXISTS tab_department'
Sqlite3 debug: Preparing: 'CREATE TABLE tab_department (id INTEGER PRIMARY KEY AUTOINCREMENT, name CHAR(100), division VARCHAR(255) NOT NULL DEFAULT 'engineering')'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
test_conn_check
test_single_connection
test_multiple_connections
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
test_multithreaded
Run a random number [1,20] of threads
Each with a random number [1,20] of {pool.get() & insert}
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: sqlite3_step return code: 5
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: Preparing: 'Sqlite3 debug: sqlite3_step return code: 5
Sqlite3 debug: Preparing: 'INSERT INTO tab_department DEFAULT VALUES'
Sqlite3 debug: sqlite3_step return code: 5
test_multithreaded: In-thread exception: Sqlite3 error: Could not execute statement: database is locked

However, using the in-memory database is okay. Are there any limitations with using connection pool or I used it run? Thanks.

[MeanSquaredError]

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.

[Earsuit]

Okay I understood, thank you!