Old entity returns after joinedload usage

[Dima Kukushkin]

hello!

I've got strange behaviour when querying entity already loaded with joined load. Here is some example:

class Company(Base):

    __tablename__ = 'company'

    id = Column(Integer, primary_key=True, unique=True, nullable=False)
    name = Column(String(255), nullable=False)


class Account(Base):

    __tablename__ = 'account'

    id = Column(Integer, primary_key=True, unique=True, nullable=False)
    company_id = Column(BigInteger, ForeignKey('company.id', ondelete='SET NULL'), nullable=True, index=True)

    company = relationship('Company', foreign_keys=company_id, uselist=False, backref=backref('accounts', uselist=True))


# Try to load account, then change company in concurrent transaction, then query for company

# Begin transaction READ COMMITTED
account = db.query(Account).options(joinedload('company')).filter_by(company_id=company_id)
old_company_name = account.company.name
import pdb; pdb.set_trace() # Here we start concurrent transaction in another terminal session to change company name [1]
company = db.query(Company).with_for_update(of=Company, key_share=True).filter_by(company_id=company_id).one_or_none()
assert company.name != old_company_name # It fails!

Concurrent transaction:

db=# begin;
db=# select name from company where id=123 for no key update of company; # here we will press [c]ontinue in first transaction to check that it will wait 2nd commited
db=# update company set name = 'new_name' where id=123;
db=# commit;

I would like to say that sometimes db.query(Company).with_for_update(of=Company, key_share=True).get(company_id) returns None, while row with this id is present in the table all time.

SQLAlchemy version is 1.1.5 PostgreSQL 9.5

[Mike Bayer]

hi there -

please read http://docs.sqlalchemy.org/en/latest/faq/sessions.html#i-m-re-loading-data-with-my-session-but-it-isn-t-seeing-changes-that-i-committed-elsewhere and see if that solves your problem (try populate_existing() first).

[Mike Bayer]

also this call is invalid:

company = db.query(Company).with_for_update(of=Company, key_share=True).filter_by(company_id=company_id).one_or_none()

Company does not have a company_id column. If you need any further help please put a COMPLETE test case (see mcve). For the "concurrent transaction" part use a new Session on the same Engine.

[Dima Kukushkin]

Michael, thank you! It seems that populate_existing() will help!

But, can you explain why some time (in very very rare cases) db.query(Company).with_for_update(of=Company, key_share=True).get(company_id) returns None, while company is present in table all the time?

[Mike Bayer]

I'd gather company_id is not what you think it is, but that's why id need to see a fully reproducing test case.

[Dima Kukushkin]

I cant put here fully reproducing testcase bcs It occurs 1-2 times in 35k runs. This 'strange' query, which returns None, is placed in a middle of transaction. We do not modify identity_map, company_id is integer becase we are using something like account.company_id from example above.

We will try prepopulate_existing() though.

You can close this issue, thank you very much for help!

[Mike Bayer]

you're running the above test with a manual "pdb.set_trace()" 35K times ? :)

If you write a script that shows the concurrency situation using two sessions, it can be put into a loop for 35K iterations.

as it stands it seems this is some in-production issue and only if you ran sql echoing could that show what's going on, it's likely something to do with the design of the application in this regard.