python - Sqlite3 'BEGIN CONCURRENT' and 'WAL2'

Question

Hoping I have a simple question here. I am trying to utilize the BEGIN CONCURRENT enhancement in sqlite3 https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md#:~:text=Overview,system%20still%20serializes%20COMMIT%20commands. In order to allow multiple writers concurrently on the same database file. These writers will never write to the same table in that database.

The issue I am having is that sqlite does not seem to recognize 'WAL2' (only legacy 'WAL') as a journal_mode option or the 'concurrent' keyword following a 'begin' statement.

sqlite version:

sqlite> select sqlite_version();
3.31.1
sqlite>

WAL2 Failure:

sqlite>  PRAGMA journal_mode;
delete
sqlite>  PRAGMA journal_mode=WAL2;
delete
sqlite>  PRAGMA journal_mode=WAL;
wal
sqlite>

begin:

sqlite> begin;
sqlite>

begin exclusive:

sqlite> begin exclusive;
sqlite>

begin concurrent:

sqlite> begin concurrent;
Error: near "concurrent": syntax error
sqlie>

It would be great to have this concurrent writing functionality as each time a job is run a new database is created and multiple tables will be created inside this new database. Running these create and insert statements in parallel would greatly reduce runtime and as I said I am not worried about conflicting writes as every writer is writing to their own table. Ideally one job will have many writers at runtime and a queue of commits (or a single commit statement once all writers are finished?) as I understand commits still need be serialized.

Another thing to mention is in production this will all be executed with the python sqlite-db-api. I know that the python sqlite api does some things by default in the background. If there are any considerations I should know about when trying to use this api to perform these writes I would love to be informed.

Let me know if I can clarify the issue in any way. I appreciate the support.

question from:https://stackoverflow.com/questions/65926476/sqlite3-begin-concurrent-and-wal2

Answer 1

Waitting for answers