Transaction integrity issue in SQLAlchemy SQLite dialect

[Roman Diba]

Summary:
The SQLAlchemy SQLite dialect does not fully support serializable
transaction isolation in SQLite, leading to potential invalid data when
transactions interleave.


Hello,
This report documents an issue with transaction integrity in the
SQLAlchemy SQLite dialect.
SQLite itself is able to fully support ACID transactions with
SERIALIZABLE isolation [1]. Also the Python sqlite3 library supports it
[2]. I'm demonstrating it in an example (roughly equivalent to my
SqlAlchemy code) [3].

The situation in which I'm demonstrating the problem:
1) T1: Open transaction T1.
2) T1: Reads the entire `test` table.
3) T2: Open transaction T2.
4) T2: Add a row to the `test` table.
5) T2: Commit.
6) T1: Based on the read in step 2, calculate and insert a row into the
`test` table.
7) T1: Commit.

The commit of T1 in step 7 should ROLLBACK because it does not reflect
the actual content of the table which was changed in step 5 by
transaction T2, and it would insert incorrect data.

I have written code demonstrating this in SqlAlchemy [4].

When run with a PostreSQL engine, it works as expected -- Transaction T1
rolls back:
[...]
INFO sqlalchemy.engine.Engine INSERT INTO test (value) VALUES
(%(value)s) RETURNING test.id
INFO sqlalchemy.engine.Engine {'value': 'a0'}
INFO sqlalchemy.engine.Engine COMMIT
INFO sqlalchemy.engine.Engine INSERT INTO test (value) VALUES
(%(value)s) RETURNING test.id
INFO sqlalchemy.engine.Engine {'value': 'b0'}
INFO sqlalchemy.engine.Engine ROLLBACK
[...]
sqlalchemy.exc.OperationalError:
(psycopg2.extensions.TransactionRollbackError) could not serialize
access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during write.
HINT: The transaction might succeed if retried.


But when run with a SQLite engine, both transactions succeed:
[...]
INFO sqlalchemy.engine.Engine INSERT INTO test
INFO sqlalchemy.engine.Engine ('a0',)
INFO sqlalchemy.engine.Engine COMMIT
INFO sqlalchemy.engine.Engine INSERT INTO test (value) VALUES (?)
INFO sqlalchemy.engine.Engine ('b0',)
INFO sqlalchemy.engine.Engine COMMIT

After the final commit, there is invalid data in the `test` table.

I expect this code should work similarly with both the PostgreSQL and
SQLite engines in SQLAlchemy.


Tested in:
- sqlalchemy 1.4.48
- sqlalchemy 2.0.12
- Python 3.7.10

Thanks for your attention,
Roman Diba, GLAMI


[1] https://www.sqlite.org/isolation.html
[2] https://docs.python.org/3/library/sqlite3.html#transaction-control
[3] sqlite_isolation.py attached
[4] sa_sqlite_isolation.py attached

[Mike Bayer]

the pysqlite driver does not deliver SERIALIZABLE isolation in its default mode of use. you have to use workarounds to achieve this. See the documentation at https://docs.sqlalchemy.org/en/20/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl which details how to use these workarounds. Also see https://github.com/python/cpython/issues/54133 https://github.com/python/cpython/issues/83638 for related info. pysqlite will have some new APIs for this in Python 3.12 but it's not clear to what extent they restore SERIALIZABLE isolation level without workarounds.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/8b61dd24-e893-5c8e-57d7-69616a272ec3%40glami.cz.
>
> Attachments:
> * sqlite_isolation.py
> * sa_sqlite_isolation.py