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
On May 31, 11:00 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On May 31, 2010, at 3:43 AM, David wrote:
>
> > 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
>
> 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 sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
