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.
On Wed, May 10, 2023, at 6:58 PM, Roman Diba wrote:
> 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
>
> --
> 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+unsubscr...@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
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/97af0dd4-e59f-4c69-a9b1-2bf89d246feb%40app.fastmail.com.
