sqlalchemy "caching"

[David]

Hello,

We are having a problem that is driving us crazy. We are in a load-
balanced environment, with 2 webservers using Django+SQLAlchemy, and
one database server.

When a user is using the application, reads and writes work fine so
long as the user is on a single webserver. However, when we have a
load balancer with multiple web servers, sometimes the user is served
"cached" old, inaccurate data.

Every time a page is accessed, the session is established as follows:

engine = sqlalchemy.create_engine(ALCHEMY_DATABASE)
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
session.expire_all()

We have tried all kinds of different configurations for "Session", but
everything seems to be giving us bad "cached" data.

Can anybody please help? Thanks in advance.

-David

Example Scenario
1.webA read from db ( colmun-data = 10 )
2.webA write to db ( colmun-data = 15 )
OK

3.webB read from db ( colmun-data = 15 )
4.webB write to db ( colmun-data = 20 )increment 5
STILL OK

5.webA read from db ( colmun-data = 10 )
NOT OK - webA reads bad data


Environment:
two Django+SQLAlchemy Servers and one DatabaseServer

Web Servers:
- Python2.6
- Django1.1.1
- SQLAlchemy 0.6
- Mysql-python 1.2.3
with Nginx + Djanogo runfcgi mode (min 10 max100 prefork)
on CentOS 5.4 running 2 phisical servers via ipvs loadbalancer

Database Server:
- MySQLCluster 7.1
on CentOS5.4

[Michael Bayer]

Since I don't see a rollback(), commit(), or close() (or autocommit=True) up there, you are probably seeing transaction isolation in effect. Once you select rows in your Session, MySQL establishes those rows as part of the current transaction. You'll get the same value back for them every time until you start a new transaction.

http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html

>
>
> Environment:
> two Django+SQLAlchemy Servers and one DatabaseServer
>
> Web Servers:
> - Python2.6
> - Django1.1.1
> - SQLAlchemy 0.6
> - Mysql-python 1.2.3
> with Nginx + Djanogo runfcgi mode (min 10 max100 prefork)
> on CentOS 5.4 running 2 phisical servers via ipvs loadbalancer
>
> Database Server:
> - MySQLCluster 7.1
> on CentOS5.4
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> 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.
>

[David]

Thanks for the response, Michael. Sorry, I forgot to mention how we
are committing the data. Here is a skinned-down version of the code
that returns "old" data when switching between web servers.

engine = sqlalchemy.create_engine(ALCHEMY_DATABASE)
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
session.expire_all()

user_query = session.query(User)
user_data = user.get('USERID')
#session.refresh(user_data) <--- this seems to fix the problem

try:
user_data.var = user_data.var + 1
session.add(user_data)
session.commit()
except:
session.rollback()


class User(Base):

__table__ = Table('user', Base.metadata,
Column('user_id', String(10), primary_key=True),
Column('var', Integer, default=0, server_default='0'),
mysql_engine='InnoDB'
)


Interestingly enough, adding "session.refresh(user_data)" right after
the "get" seems to be fixing the problem. But I'm confused, since I
thought "session.expire_all()" should have the same effect. I don't
want to add a "refresh" after every get in our code, since I'm afraid
it might require multiple calls to the database.

Any help is appreciated.
-David

[Michael Bayer]

the expire_all has the same effect, yes. You'd need to reproduce your behavior in an isolated test case to illustrate what you're doing. For example I see a "user.get()" up there, would need to know what that is, etc.

[David]

Oops, the line user_data = user.get('USERID') should have read

user_data = user_query.get('USERID')

For now, we're just going to stick with calling "session.refresh"
after each get. Thanks for the help.

-David