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.
import sqlite3
import tempfile
tst_table = """
CREATE TABLE test (
id SERIAL NOT NULL,
value VARCHAR NOT NULL,
PRIMARY KEY (id),
UNIQUE (value)
)
"""
with tempfile.TemporaryDirectory() as tmpdirname:
sqlite_file = f"{tmpdirname}/db.sqlite"
isolation_level = "DEFERRED"
con = sqlite3.connect(sqlite_file, isolation_level=isolation_level)
con.set_trace_callback(print)
con.execute(tst_table)
con.close()
con1 = sqlite3.connect(sqlite_file, isolation_level=isolation_level)
con2 = sqlite3.connect(sqlite_file, isolation_level=isolation_level)
con1.set_trace_callback(lambda s: print(f"CONN1: {s}"))
con2.set_trace_callback(lambda s: print(f"CONN2: {s}"))
try:
with con1:
con1.execute(f"BEGIN {isolation_level}")
print("outer transaction starts")
n1 = len(con1.execute("SELECT test.id, test.value FROM test").fetchall())
with con2:
con2.execute(f"BEGIN {isolation_level}")
print("inner transaction starts")
n2 = len(con2.execute("SELECT test.id, test.value FROM test").fetchall())
con2.execute(f'INSERT INTO test (id, value) VALUES (0, "a{n2}");')
print("inner transaction will commit")
con2.commit()
con1.execute(f'INSERT INTO test (id, value) VALUES (1, "b{n1}")')
n3 = len(con1.execute("SELECT test.id, test.value FROM test").fetchall())
print("outer transaction will commit")
con1.commit()
finally:
con1.close()
con2.close()
import tempfile
from sqlalchemy import create_engine, Column, Integer, String, select
from sqlalchemy.orm import declarative_base, Session
TstBase = declarative_base()
class TstTable(TstBase):
__tablename__ = "test"
id = Column(Integer, primary_key=True)
value = Column(String, nullable=False, unique=True)
# with postgres -> (psycopg2.extensions.TransactionRollbackError) could not serialize access due to read/write dependencies among transactions
# with sqlite -> no error
with tempfile.TemporaryDirectory() as tmpdirname:
postgres_url = "postgresql+psycopg2://..."
sqlite_url = f"sqlite:///{tmpdirname}/db.sqlite"
engine = create_engine(sqlite_url, echo=True, isolation_level="SERIALIZABLE", future=True)
engine.update_execution_options(isolation_level="SERIALIZABLE")
TstBase.metadata.drop_all(engine)
TstBase.metadata.create_all(engine)
with Session(engine, autocommit=False) as session2:
with Session(engine, autocommit=False) as session:
with session.begin():
print("outer transaction starts")
stmt = select(TstTable)
n1 = len(session.execute(stmt).all())
with session2.begin():
print("inner transaction starts")
stmt = select(TstTable)
n2 = len(session2.execute(stmt).all())
t2 = TstTable(value=f"a{n2}")
session2.add(t2)
print("inner transaction will commit")
t1 = TstTable(value=f"b{n1}")
session.add(t1)
print("outer transaction will commit")
