SQLAlchemy-实体未标记为已过期

问题描述

我有多线程python3工具来管理其他进程.我使用数据库中的记录来标记特定进程正在忙.

I have multi-thread python3 tool to manage other processes.I use records in database to mark that particular process is busy now.

class Process(base):
    id = Column('ID', Integer, primary_key=True)
    name = Column('NAME', String(128))
    tasks = relationship('ProcessTask', back_populates="process", cascade="all, delete-orphan", passive_deletes=True)
    """ :type: list[ProcessTask] """

class ProcessTask(base):
    id = Column('ID', Integer, primary_key=True)
    process_id = Column('PROCESS_ID', Integer, ForeignKey('PROCESS.ID', ondelete='CASCADE', onupdate='CASCADE'), nullable=False)
    status = Column('STATUS', String(128), nullable=False)
    process = relationship(Process, back_populates="tasks")
    """ :type: Process """

例如，任务只有两种可能的状态-正在运行"和完成"

For example, tasks have only two possible statuses - "running" and "finished"

我遇到了一个问题:

• thread1为进程创建任务
• thread2检查该进程的可用性.进程忙.线程2进入睡眠状态，直到任务标记为完成
• thread1将任务标记为已完成
• thread2不会看到" ProcessTask实体的更改，并且会永远休眠

我试图通过线程2中的session.expire(entity)将ProcessTask实体标记为过期-不起作用.

I tried to mark ProcessTask entity as expired via session.expire(entity) in thread2 - does not work.

我在做什么错了?

这是我用来创建会话工厂的代码:

This is the code which I use to create session factory:

connString = "mysql+mysqlconnector://{userName}:{userPass}@{host}:{port}/{dbName}".format(...)
self._db_engine = sqlalchemy.create_engine(connString, pool_size=100, pool_recycle=3600)
self._db_session_factory = sqlalchemy.orm.sessionmaker(bind=self.getEngine())
self._db_session = sqlalchemy.orm.scoped_session(self._db_session_factory)

每个线程通过self._db_session()

推荐答案

失败的原因是MySQL事务的隔离级别.

Reason of failure was the isolation level of MySQL transactions.

MySQL事务的默认隔离级别为REPEATABLE READ.在事务内部首先进行读取操作，MySQL会创建数据库的快照，而在事务期间，您将从该快照读取.因此，在事务处理完成之前，您将无法读取其他线程所做的更改.

Default isolation level of MySQL transactions is REPEATABLE READ. At first read operation inside transaction MySQL creates snapshot of database and during transaction you read from that snapshot. So you can't read changes made by other threads until transaction finished.

要解决此问题，我必须将隔离级别设置为READ COMMITTED

To fix this behaviour I had to set isolation level to READ COMMITTED

https://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

http://docs.sqlalchemy. org/en/rel_1_0/orm/session_transaction.html#setting-transaction-isolation-levels

这篇关于SQLAlchemy-实体未标记为已过期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！