Hi all
I am now seeing the SQLAlchemy raise 'NoSuchColumnError' occasionally when
concurrent accesses happen with 'select for update'.
My application uses
* SQLAlchemy 0.8.4
* MySQL-python 1.2.3
* CentOS6.7
* python 2.6
* MariaDB 10.0.20 (three-node Galera Cluster with haproxy 1.5.2)
I know Galera does not support explicit lock so I configure haproxy to
redirect incoming requests only to one active node.
The reason of this is to make explicit locking work for 'select for
update', like the way we use single database server.
So the other Galera nodes are setup just for backup.
To break down what that application does, firstly I create engine and
sessioinmaker with isolation_level="READ COMMITTED"
```
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool
# 192.168.0.2 is virtual IP
engine = sqlalchemy.create_engine(
"mysql://test:testtest@192.168.0.2/test",
poolclass=NullPool,
isolation_level="READ COMMITTED")
maker = sessionmaker(bind=engine, autocommit=True)
```
Secondary the model is defined below, which has a composite key
(`data_id1`, `data_id2`).
```
Base = declarative_base()
class MyData(Base):
__tablename__ = 'mydata'
__table_args__ = (
UniqueConstraint('data_id1', 'data_id2'),
)
data_id1 = Column(String(64), primary_key=True)
data_id2 = Column(String(64), primary_key=True)
status = Column(String(40), nullable=True)
```
There could be multiple application processes running(not daemon) which
could access to the same record.
Each process firstly tries to lock a row, then check status of MyData and
update status attribute.
```
.....
session.begin()
try:
mydata = get_mydata(session, data_id1, data_id2, lockmode="update")
// do something here to check status
update_status(session, data_id1, data_id2, "some status")
session.commit()
except Exception as e:
session.rollback()
.....
def get_mydata(session, data_id1, data_id2, lockmode=None):
query =
session.query(MyData).filter_by(data_id1=data_id1).filter_by(data_id2=data_id2)
if lockmode:
query = query.with_lockmode(lockmode)
return query.first()
def update_status(session, data_id1, data_id2, status):
data = get_mydata(session, data_id1, data_id2)
data.status = status
session.flush()
```
When concurrent access happens and one process locks a row, subsequent
processes are blocked from processing the same record at the same time.
The subsequent processes will wait until the previous one releases the lock.
This usually works fine as I expect but sometimes I see `NoSuchColumnError`
cast when the subsequent process call `query.first()` within
`update_status()`.
```
Traceback (most recent call last):
File "/root/myapp.py", line 301, in some_func()
update_status(session, data_id1, data_id2, "some status")
File "/root/myapp.py", line 231, in update_status
data = get_mydata(session, data_id1, data_id2)
File "/root/myapp.py", line 256, in get_mydata
return query.first()
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line
2282, in first
ret = list(self[0:1])
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line
2149, in __getitem__
return list(res)
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/loading.py", line
72, in instances
rows = [process[0](row, None) for row in fetch]
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/loading.py", line
356, in _instance
tuple([row[column] for column in pk_cols])
File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/result.py",
line 314, in _key_fallback
expression._string_or_unprintable(key))
NoSuchColumnError: "Could not locate column in row for column
'mydata.data_id1'"
```
I wish someone knows which point causes this error.
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
