[Sqlalchemy-tickets] Issue #3926: Usage of dialects without sane multi rowcount leads to unexpected behavior on StaleDataErrors (zzzeek/sqlalchemy)

[David M. <iss...@bi...>]

New issue 3926: Usage of dialects without sane multi rowcount leads to unexpected behavior on StaleDataErrors
https://bitbucket.org/zzzeek/sqlalchemy/issues/3926/usage-of-dialects-without-sane-multi

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):

```
#!python

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.