- edited description
Old entity returns after joinedload usage
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
Comments (8)
-
reporter -
repo owner 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).
-
repo owner 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.
-
reporter 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? -
repo owner 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.
-
reporter 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 likeaccount.company_id
from example above.We will try prepopulate_existing() though.
You can close this issue, thank you very much for help!
-
repo owner 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.
-
repo owner - changed status to closed
- Log in to comment