SQLAlchemy refresh() not working after committing from a different session

Question

I'm experiencing an issue with sqlalchemy where an update to a record in one session is not reflected in a second session even after committing and refreshing the object.

To demonstrate, consider this (complete) example:

import logging
from sqlalchemy import create_engine, Column, Boolean, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
logging.basicConfig(level=logging.INFO)
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)
# works with this
#engine = create_engine("sqlite://")
# fails with this 
engine = create_engine("mysql+mysqldb://{user}:{pass}@{host}:{port}/{database}?charset=utf8mb4".format(**DB_SETTINGS))
Session = sessionmaker(bind=engine)
Base = declarative_base()
class Foo(Base):
 __tablename__ = "foo"
 id = Column(Integer, primary_key=True, autoincrement=True)
 flag = Column(Boolean)
 def __repr__(self):
 return "Foo(id={0.id}, flag={0.flag})".format(self)
# create the table
Base.metadata.create_all(engine)
# add a row
session = Session()
foo = Foo(id=1, flag=False)
session.add(foo)
session.commit()
# fetch the row in a different session
session2 = Session()
foo2 = session2.query(Foo).filter_by(id=1).one()
logging.info("SESSION2: Got {0}".format(foo2))
# update the row in first session and commit
foo.flag = True
session.commit()
# refresh the row in second session
logging.info("SESSION2: Refreshing...")
session2.refresh(foo2)
logging.info("SESSION2: After refresh: {0}".format(foo2))
# does "flag" come back as True?

When I run this against with the mysql+mysqldb:// engine to connect to my remote MySQL instance, the change to foo.flag is not reflected in session2.

But if I uncomment the line that creates an engine using a simple sqlite:// in-memory database, the change to foo.flag is reflected in session2.

What is it about my MySQL server configuration could cause an UPDATE command in one session followed immediately by a SELECT query in another session to return different data?

• Ilja Everilä: MySQL's default transaction isolation level is REPEATABLE READ (compared to for example Postgresql's default READ COMMITTED), and so your session2's active transaction sees the state of the DB as it was when the transaction began. Add session2.rollback() and observe what happens.
• Jugdish: Thank you @IljaEverilä for introducing me to the concept of transaction isolation -- that's exactly the bit of information I was missing. You're right, adding session2.rollback() before the refresh() has fixed the issue. What is the recommended way to close a transaction after a query? Is it to run rollback()? Or is there a more appropriate method that SQLAlchemy provides?
• Ilja Everilä: That's a bit broad subject that depends heavily on use case etc. You might be running a bunch of queries together that should act as a single atomic operation. A http server serving a request is a good example of such; the queries issued in order to serve the request should probably observe a consistent state, and you'd either commit or rollback at the end of the response. On the other hand a single periodic query that should always observe the latest and greatest state might as well rollback after each query, which SQLAlchemy btw. does if you close a session.
• Ilja Everilä: ...Which gets us to SQLAlchemy's session handling. I'd recommend reading "When do I construct a Session, when do I commit it, and when do I close it?". You could also change the isolation level of a transaction, if need be.