Using multiple sessions in a single thread, unexpected behavior, stale information: Why?

[Derek Litz]

Code is below: see NOTE comments


from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, Unicode
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from contextlib import contextmanager

USERNAME = u'root'
PASSWORD = u''
LOCAL_DB = u'localhost'
DATABASE = u'test_db'

engine = create_engine(u'mysql+mysqldb://{}:{}@{}/{}'.format(
    USERNAME, PASSWORD, LOCAL_DB, DATABASE), echo=True)

get_session = sessionmaker(bind=engine)
mysql = declarative_base(bind=engine)

server_engine = create_engine(u'mysql+mysqldb://{}:{}@{}'.format(
    USERNAME, PASSWORD, LOCAL_DB))
get_server_session = sessionmaker(bind=server_engine)
server_session = get_server_session()
server_session.execute('CREATE DATABASE IF NOT EXISTS test_db')
server_session.commit()
server_session.close()


@contextmanager
def mysql_session():
    try:
        session = get_session()
        yield session
    except Exception as e:
        session.rollback() # Release any potential locks
        raise e
    finally:
        session.rollback()
        session.close()


class User(mysql):
    __tablename__ = u'user'
    id = Column(Integer, primary_key=True)
    first_name = Column(Unicode(64))
    last_name = Column(Unicode(64))


session = get_session()
mysql.metadata.drop_all()
mysql.metadata.create_all()


def add_last_name(user_id):
    with mysql_session() as s:
        user = s.query(User).filter(User.id == user_id).one()
        user.last_name = u'Litz'
        s.commit()


with mysql_session() as s:
    new_user = User(first_name=u'Derek')
    s.add(new_user)
    s.commit()
    add_last_name(new_user.id)
    # NOTE this makes sense, the object was never updated here
    assert not new_user.last_name == u'Litz'
    # NOTE Why doesn't user receive the updated information?
    user = s.query(User).filter(User.id == new_user.id).one()
    assert not user.last_name == u'Litz'
    # NOTE Despite commit being the last thing I did for this session
    # this fixes the problem.  Why didn't it before?
    s.commit() # or s.rollback() or s.close()
    user = s.query(User).filter(User.id == new_user.id).one()
    assert user.last_name == u'Litz'

[Michael Bayer]

On Jan 17, 2013, at 10:27 AM, Derek Litz wrote:

> with mysql_session() as s:
> new_user = User(first_name=u'Derek')
> s.add(new_user)
> s.commit()
> add_last_name(new_user.id)
> # NOTE this makes sense, the object was never updated here
> assert not new_user.last_name == u'Litz'


> # NOTE Why doesn't user receive the updated information?
> user = s.query(User).filter(User.id == new_user.id).one()

because you made the change to "User" in a different transaction (as a result of using a different Session). This transaction still continues and is considered to be "isolated" - SQLAlchemy doesn't invalidate the data until the transaction ends. If you say, s.expire(user), then you'll get the new value within the transaction, *assuming* the transaction isolation here is not enforcing repeatable reads. If MySQL is enforcing repeatable reads, you'll see the same data for user.last_name for each SELECT regardless of a change in another transaction.

all about transaction isolation: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html

> assert not user.last_name == u'Litz'
> # NOTE Despite commit being the last thing I did for this session
> # this fixes the problem. Why didn't it before?
> s.commit() # or s.rollback() or s.close()

the transaction here ends, ending any "repeatable read" state set up by that transaction (this would be via MySQL), and additionally the SQLAlchemy Session expires all the data. The next access starts a new transaction that gets the latest data.

[Derek Litz]

So the earlier commit ended the transaction but immediately began a new one, that's why a second call to commit had a different behavior? I made the mistake of thinking that the session would be in a similar state to when I originally acquired it, however, this is not true because I still have a open connection to the database and a new transaction as already begun.  Correct?  For some reason I had the thought a new transaction wouldn't be started with the session after a commit until I actually did something with the session again.

Yeah, expire has no effect so repeatable reads may be on.  Thank you for the insight.

[Derek Litz]

Yeah, just reading through the logs, they confirm my new understanding :).  Thanks much!

2013-01-17 10:09:41,335 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-01-17 10:09:41,335 INFO sqlalchemy.engine.base.Engine INSERT INTO user (first_name, last_name) VALUES (%s, %s)
2013-01-17 10:09:41,335 INFO sqlalchemy.engine.base.Engine ('Derek', None)
2013-01-17 10:09:41,336 INFO sqlalchemy.engine.base.Engine COMMIT
2013-01-17 10:09:41,337 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)  <------- New Transaction Begun Immediately

[Michael Bayer]

the "beginning" of a transaction is implicit when using the Python DBAPI, typically when the first SQL statement is emitted subsequent to the last commit or rollback.

the subtlety in your example, as I look more closely, is that it says: 

commit()

add_last_name(new_user.id)

the "commit" ends the transaction.  then "new_user.id" emits a SELECT for the row again.  while this is just the primary key, it also checks that the row hasn't been deleted.  But in any case that's your new transaction, before add_last_name() gets called.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/C42ExAAYErIJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Derek Litz]

Ah, ha! Thanks much.