Reputation: 22726
I'm confused about how to concurrently modify a table from several different processes. I've tried using Query.with_lockmode()
, but it doesn't seem to be doing what I expect it to do, which would be to prevent two processes from simultaneously querying the same rows. Here's what I've tried:
import time
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import *
engine = create_engine('mysql://...?charset=utf8&use_unicode=0', pool_recycle=3600, echo=False)
Base = declarative_base(bind=engine)
session = scoped_session(sessionmaker(engine))
class Test(Base):
__tablename__ = "TESTXYZ"
id = Column(Integer, primary_key=True)
x = Column(Integer)
def keepUpdating():
test = session.query(Test).filter(Test.id==1).with_lockmode("update").one()
for counter in range(5):
test.x += 10
print test.x
time.sleep(2)
session.commit()
keepUpdating()
If I run this script twice simultaneously, I get session.query(Test).filter(Test.id==1).one().x
equal to 50, rather than 100 (assuming it was 0 to begin with), which was what I was hoping for. How do I get both processes to either simultaneously update the values or have the second one wait until the first is done?
Upvotes: 4
Views: 6811
Reputation: 54910
Are you by accident using MyISAM tables? This works fine with InnoDB tables, but would have the described behavior (silent failure to respect isolation) with MyISAM.
Upvotes: 5