[sqlalchemy] select x for update is still snapshot read in sqlalchemy.

[ying zhang]

I'm confused with how Query.with_lockmode('update') works in sqlalchemy.
My code is in attachment.
For testing, I start test.py in 1st terminal, then start test.py in 2nd 
terminal.
so the 2nd termial will read the data modified by 1st test.py.
But the 2nd terminal's output contains L42 in test.py.
It means the 2nd test.py select x for update is still a 'snapshot read'.
In my opinion, the variable 'locking_u' should be the one changed by 1st 
terminal, locking.name should be 'aa'.
Because it's [current read] in 'select x for update' mysql sentences.
And mysql Isolation level is RR.

My test table is as below:
mysql> select * from user_test;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | NULL |
+----+------+


Thank you so much if you can understand and help me.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

#!/usr/bin/env python

import time
import os

from sqlalchemy import create_engine
from sqlalchemy import func, or_, not_
from sqlalchemy import Column
from sqlalchemy.orm import sessionmaker
from sqlalchemy.types import CHAR, Integer, String
from sqlalchemy.ext.declarative import declarative_base

BaseModel = declarative_base()
DB_CONNECT_STRING = 'mysql+mysqldb://root@127.0.0.1:3006/test'
engine = create_engine(DB_CONNECT_STRING, echo=True)

DB_Session = sessionmaker(bind=engine)
session = DB_Session()


class User(BaseModel):
    __tablename__ = 'user_test'
    __table_args__ = {
        'mysql_engine': 'InnoDB'
    }

    id = Column(Integer, primary_key=True)
    name = Column(CHAR(30))

    def __str__(self):
        return 'User[%d, %s]' % (self.id, self.name)


u = session.query(User).filter(User.name == 'a').first()
print 'before locking'
locking_u = session.query(User).\
    with_lockmode('update').\
    filter(User.id == u.id).\
    first()
print locking_u
if locking_u and locking_u.name == 'a':
    print 'change'
    locking_u.name = 'aa'
else:
    print 'no change'
time.sleep(10)
session.commit()
print 'after locking'