SQL Alchemy Relationship加载程序在表上留下了锁? [英] SQL Alchemy Relationship loader leaves a lock on table?

问题描述

我有非常简单的代码导致我的MySQL数据库挂起:

I have very simple code that cause my MySQL db to hang:

import sqlalchemy as sa
from sqlalchemy import orm

# creating the engine, the base, etc
import utils
import config

utils.base_init(config)
Base = config.Base

class Parent(Base):
    __tablename__ = 'Parents'
    id = sa.Column(sa.Integer, primary_key=True)
    children = orm.relationship('Child', backref='parent')

class Child(Base):
    id = sa.Column(sa.Integer, primary_key=True)
    parent_id = sa.Column(sa.Integer)

    __tablename__ = 'Children'

    __table_args__ = (sa.ForeignKeyConstraint(
        ['parent_id'],
        ['Parents.id'],
        onupdate='CASCADE', ondelete='CASCADE'),{})

Base.metadata.create_all()

session = orm.sessionmaker(bind=config.Base.metadata.bind)()
p = Parent(id=1)
c1 = Child(id=1)
c2 = Child(id=2)
session.add(p)
session.add(c1)
session.add(c2)
session.commit()

# Works
# Base.metadata.drop_all()

c1.parent
# 2012-08-17 20:16:21,459 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
# 2012-08-17 20:16:21,460 INFO sqlalchemy.engine.base.Engine SELECT `Children`.id AS `Children_id`, `Children`.parent_id AS `Children_parent_id` 
# FROM `Children` 
# WHERE `Children`.id = %s
# 2012-08-17 20:16:21,460 INFO sqlalchemy.engine.base.Engine (1,)



Base.metadata.drop_all()
# hangs until i kill the connection above.
# server status: 'Waiting for table metadata lock'

在发出加载关系属性所需的选择查询后，SQL Alchemy似乎没有释放元数据锁定?我该如何释放它?我什至不明白为什么select语句首先需要将表锁定！

It looks like SQL Alchemy doesn't release a metadata lock after issuing the select query needed to load a relationship attribute? How can i get it to release it? I don't even understand why a select statement would need to lock the table in the first place!

当然，我可以通过关闭会话来使这段特定的代码起作用，但这在我的实际程序中不切实际.

Of course, I can get this specific piece of code to work by closing the session, but that isn't practical in my actual program.

推荐答案

您需要在.drop_all()调用之前开始新的事务； MySQL在此事务中看到您正在从表中读取数据，并锁定该表以防被删除:

You need to start a new transaction before the .drop_all() call; MySQL sees you reading from the table in this transaction, and locks the table against being dropped:

session.commit()
Base.metadata.drop_all()

隐式提交事务将开始新的事务.

Committing a transaction implicitly begins a new transaction.

MySQL保证有关事务隔离；您的交易将读取一致的数据，并且在开始新交易之前，不会看到其他交易所提交的更改.但是，DROP TABLE语句使MySQL无法保留这些保证，因此表已被锁定.

MySQL makes guarantees about transaction isolation; your transaction will read consistent data and won't see changes committed by other transactions until you start a new transaction. A DROP TABLE statement however makes it impossible for MySQL to keep these guarantees so the table is being locked.

或者，您可以更改事务隔离级别，告诉MySQL您不在乎隔离保证.因为会话连接是池化的，所以只能对所有连接都做，或者根本不做.对 create_engine() isolation_level参数>:

Alternatively, you could alter the transaction isolation level, telling MySQL you don't care about the isolation guarantees. Because session connections are pooled, this can only be done for all connections or none at all; use the isolation_level argument to create_engine():

engine = create_engine(
    'mysql://username:passwd@localhost/databasename',
    isolation_level='READ UNCOMMITTED')

有关详细信息，请参见 SET TRANSACTION文档关于每个隔离级别.

See the SET TRANSACTION documentation for details about each isolation level.

这篇关于SQL Alchemy Relationship加载程序在表上留下了锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！