Transaction manager for SQLite3

[roberto-code]

Hello @MeanSquaredError,

I am currently working with a SQLite3 database that can be accessed by several processes. One of the processes can run a long write transaction and other processes could run read transactions at the same time. In issue #554 you mentioned that you have a transaction manager for PostgreSQL and that sounds exactly like the solution I need for this. Could you share it? What changes do you think would be needed in the SQLite3 connector to make it work with SQLite3? Thanks a lot.

Best, Roberto.

[MeanSquaredError]

Sure, give me moment, to prepare a zip file and a brief explanation.

[MeanSquaredError]

pg_dbm.zip

OK, I have attached a zip file with the source code for the database manager that I use. It uses PostgreSQL but it should be relatively easy to adjust it to MySQL or SQLite3. It does optimistic locking, i.e. parallel/concurrent users try to make their changes to the database and if there is some kind of problem combining the parallel transactions, one or more of them are retried until they succeed.

Here is how to use it:

1. Somewhere at the beginning of your program

   include <database/db_global.h>

2. In other places in your code

   include <database/db_use.h>

   then call db_global_init (); to initialize the database manager. It defines a global object called g_dbmthat you use when you need to run a database transaction. For example:

   
   auto retval = g_dbm.tx ([&] {
   
       // Your database transaction goes here
   auto db_mytbl = db_model::mytbl {};
   auto rows = g_dbm.exec (
       select (db_mytbl.channel_id)
       .from (db_mytbl)
       .where (db_mytbl.tbl_id == table_id)
   );
   if (! rows.empty ()) {
       return rows.front ().channel_id.value ();
   }
   g_dbm.exec (
       insert_into (db_mytbl)
       .set (
           db_mytbl.username = my_username,
           db_mytbl.channel_id = my_channel
       )
   );
   return 0;

});

`g_dbm` is a global object that defines several methods:

g_dbm.tx (lambda) : Runs a transaction. The lambda contains your transaction code. If a transaction isolation error occurs, then the transaction is retries. Other exceptions are not intercepted so the bubble up and if unhandled terminate If the lambda returns normally (no exception), its return value becomes the return value of the call to g_dbm.tx

g_dbm.exec (sqlpp11_query) : Calls the database handle with the given query. Does the same as `db(your query)` from the sqlpp11 documentation. Must be called from inside a transaction.

g_dbm.dbc_no_tx() : Returns a database connection handle that can be used to do anything you do with regular sqlpp11 database connections, for example make a query outside of a transaction (I guess then the database engine uses autocommit mode).

3. Notes about the code:

The code includes db_model.h : This file is not included in the .zip. This is the database model generated by `sqlpp11-ddl2cpp` or written manually by you.

`db_global_init()` uses a configuration store object to fetch the database name, connection username and password. You will need to adjust that function to use your database name and credentials.

`g_dbm` is a global object but it does not have to be. I defined it this way just because it is easy to use. If you want, you can pass it as a parameter to the place where you are going to use it.
Also the lambda uses the global `g_dbm`, but if you don't want to use globals you can adjut the code to pass either the database manager or the database connection as a parameter to the lambda.

The code in includes spdlog.h, but you can remove the calls to it. THe database manager uses it to log messages about transaction failures and their retries.

The code in `g_dbm.tx` retries the transaction on these PostgreSQL exceptions:

sqlpp::postgresql::serialization_failure
sqlpp::postgresql::deadlock_detected

You will need to change that probably to retry on `SQLITE_BUSY`, although there might be other errors too that may need handling.

The db manager takes (or at least should take) care of multithreading so it is OK to call `g_dbm.tx()` and `g_dbm.exec()` from multiple threads in parallel.

I haven't used this code with SQLite3 so no guarantee that it will actually work, but it is worth trying.

Hope that helps.

[roberto-code]

Thanks a lot for sharing the code ad the detailed explanation! I will try to adapt it to work with SQLite3.

[MeanSquaredError]

By the way, the code was originally written for a C++20 project, but I think that it is mostly compatible and should compile in C++17 mode with no or little changes. There is one place where it uses if constexpr so it won't compile in any mode that is below C++17. I guess if you need to build it in C++11/14 mode you could replace the if constexpr with SFINAE.