Usage of dialects without sane multi rowcount leads to unexpected behavior on StaleDataErrors
When using a dialect without sane multi rowcount (like cx_oracle), a StaleDateError will not be raised if several rows are updated (probably due to the usage of executemany since sqlalchemy version 1.0). The following test case shows the different behavior of dialects with and without sane multi rowcount (tested with sqlalchemy 1.1.5):
import pytest
from sqlalchemy import orm, create_engine, Column, Integer, String
from sqlalchemy.orm.exc import StaleDataError
from sqlalchemy.dialects.sqlite.base import SQLiteDialect
from sqlalchemy.ext.declarative import declarative_base
@pytest.mark.parametrize('rowcount', [1, 2, 3])
@pytest.mark.parametrize('sane_rowcount', [True, False])
def test_stale_data_error_on_multi_row_update(monkeypatch, rowcount, sane_rowcount):
"""
This test provokes a StaleDataError by modifying the primary key of some entries while updating another value
using the orm. The StaleDataError triggers a rollback, so that the table should not hold any changed entries
afterwards. This should always be true, even if the used dialect does not support sane multi rowcount.
"""
# Sqlite supports multi rowcount but is easier to use for testing purposes. Hence the
# flag 'supports_sane_multi_rowcount' is manually set from True to False.
monkeypatch.setattr(SQLiteDialect, "supports_sane_multi_rowcount", sane_rowcount)
engine = create_engine("sqlite:///")
Base = declarative_base()
class Car(Base):
__tablename__ = 'car'
id = Column(Integer, primary_key=True)
owner = Column(String)
Base.metadata.create_all(engine)
session = orm.sessionmaker(bind=engine)()
original_car_data = [(car_id, 'Bob') for car_id in range(1, rowcount + 1)]
session.add_all([Car(id=car_id, owner=owner) for car_id, owner in original_car_data])
session.commit()
cars = session.query(Car).all()
session.execute("update car set id = id + 3 where id < 3")
for car in cars:
car.owner = 'Peter'
try:
session.commit()
except StaleDataError:
session.rollback()
assert session.query(Car.id, Car.owner).order_by(Car.id).all() == original_car_data
Summary of the test result:
rowcount | sane_rowcount | result | content of table 'car' after running the test |
---|---|---|---|
1 | True | passed | id=1 owner='Bob' |
2 | True | passed | id=1 owner='Bob', id=2 owner='Bob' |
3 | True | passed | id=1 owner='Bob', id=2 owner='Bob', id=3 owner='Bob' |
1 | False | passed | id=1 owner='Bob' |
2 | False | failed | id=4 owner='Bob', id=5 owner='Bob' |
3 | False | failed | id=3 owner='Peter', id=4 owner='Bob', id=5 owner='Bob' |
For cx_oracle, it seems like the flag supports_sane_multi_rowcount
could be set to True. For other affected dialects it may be saver not to use executemany.
Comments (4)
-
repo owner -
reporter Thank you for the explanation.
I've tested the rowcount result with cx_Oracle 5.2.1 and Oracle 12c. I'm not sure since when it reports the correct result, but version 5.2.1 included a commit that modified the rowcount determination: https://bitbucket.org/anthony_tuininga/cx_oracle/commits/21bf6d4b38ca7e0b77dac40d9e1e0b812f4fde52 It should be noted, that this modification makes a distinction between versions from Oracle 12c and older versions.
-
repo owner - changed status to closed
I've created
#3932to specifically add Oracle into sane_multi_rowcount. As far as using executemany() when the flag is False, it is theoretically possible to allow a mapper flag that sets this up, however the ORM currently doesn't guarantee "rowcount" if the versioning feature isn't in use. So adding a flag to start trying to guarantee "rowcount" even when versioning is turned off would imply a bigger change, since at the very least it means a lot of new test coverage, and I think also there may be some cases such as in deletions where we aren't counting rowcount in any case if versioning isn't in use. -
repo owner also note that in the past, we've had "executemany" set up "sane multi rowcount" by just flipping dialect.do_executemany() to run cursor.execute() many times. So "sane multi rowcount" can be set up on the dialect side as well, again at great performance impact. But that still doesn't guarantee rowcount is checked in all ORM persistence use cases.
- Log in to comment
so this is by design. The ORM makes no guarantees about concurrency unless you use the versioning feature, and in the case of deletes when versioning is not in use, a mismatch rowcount only warns, rather than raises, as some databases might have ON DELETE CASCADE rules that are not accommodated by the mappings. The use of executemany() is to gain a significant performance increase. It's a tradeoff between performance vs. detection of concurrency, most users prefer performance by default unless they specifically want concurrency protection in which case they'd use the versioning feature.
I'm gathering you must have tested it. It seems to be reporting the correct number now, however it definitely didn't in the past for that flag to be un-set, so the version of cx_Oracle that repairs this should be identified so it can be detected. The behavior of cursor.rowcount when executemany() is run is unfortunately not part of the DBAPI specification and whether or not it works is sometimes even not known by the DBPI author themselves. However if cx_Oracle is guaranteeing this now as of (to-be-identified) version then that's a more readily actionable issue.