CODEWITHSUNDEEP
sqlitesqlalchemy
Helmut Grohne
Helmut Grohne

Reputation: 6788

sqlalchemy: How to make sqlite transactions immediate?

sqlite transactions cat be "deferred", "immediate" or "exclusive". The default is "deferred" which means, not starting the transaction until absolutely necessary. This can cause transaction aborts if concurrent transactions start with reading and the proceed to writing. Such aborts can be avoided by using immediate transactions (at the cost of degrading performance).

sqlalchemy abstracts sql dialects including sqlite. It also has a model for writing transactions:

with engine.begin() as connection:
    do_something_with_connection

How does one tell sqlalchemy that such a transaction should be immediate. Alternatively, how does one tell sqlalchemy that all sqlite transactions should be immediate?

Upvotes: 6

Views: 1705

Answers (2)

phil
phil

Reputation: 31

The reference code from the SQLAlchemy docs doesn't easily allow you to customize the transaction type (BEGIN vs BEGIN IMMEDIATE) while sharing the same Engine connection pool.

I got tired of this and made a small library called sqlalchemy-boltons to customize the SQLite transaction type as well as other things on a per-engine or per-connection basis. This is implemented using Engine.execution_options which is the recommended way according to the documentation. Now you can write something like:

from sqlalchemy.orm import sessionmaker
from sqlalchemy_boltons.sqlite import create_engine_sqlite

engine = create_engine_sqlite(
    "file.db",
    journal_mode="WAL",
    timeout=0.5,
    # create_engine_args={"echo": True},
)

# Configure the engine to use a plain "BEGIN" to start transactions and
# and to use deferred enforcement of foreign keys (recommended!)
engine = engine.execution_options(
    x_sqlite_begin_mode=None, x_sqlite_foreign_keys="defer"
)

# Make a separate engine for write transactions using "BEGIN IMMEDIATE"
# for eager locking.
engine_w = engine.execution_options(x_sqlite_begin_mode="IMMEDIATE")

# Construct a sessionmaker for each engine.
Session = sessionmaker(engine)
SessionW = sessionmaker(engine_w)

# read-only transaction
with Session() as session:
    session.execute(select(...))

# lock the database eagerly for writing
with SessionW() as session:
    session.execute(update(...))

Upvotes: 1

EAW
EAW

Reputation: 952

Core events https://docs.sqlalchemy.org/en/latest/core/events.html can be used to intercept connection events and rewrite the BEGIN statement issued at the start of the transaction to achieve what you want.

See the section of the sqlalchemy documentation on the sqlite dialect for more details https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html.

The example code below is copied directly from the documentation other than for changing BEGIN to BEGIN IMMEDIATE.

from sqlalchemy import create_engine, event

engine = create_engine("sqlite:///myfile.db")

@event.listens_for(engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # disable pysqlite's emitting of the BEGIN statement entirely.
    # also stops it from emitting COMMIT before any DDL.
    dbapi_connection.isolation_level = None

@event.listens_for(engine, "begin")
def do_begin(conn):
    # emit our own BEGIN
    conn.execute("BEGIN IMMEDIATE")

Upvotes: 6

Related Questions