Usage of dialects without sane multi rowcount leads to unexpected behavior on StaleDataErrors

[David Matthus]

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.

[Mike Bayer]

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.

For cx_oracle, it seems like the flag supports_sane_multi_rowcount could be set to True.

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.

[David Matthus]

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.

[Mike Bayer]

I've created #3932 to 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.

[Mike Bayer]

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.