SQLAlchemy在删除表时被阻止 [英] SQLAlchemy blocked on dropping tables

问题描述

该代码是sqlahcmey的 orm教程的分步复制，除了最后一行， 我打算在查询后删除所有表.但是程序在Base.metadata.drop_all(bind=engine)上被阻止，以下是当时MySQL的状态(取自MySQL Workbench):

The code is a step-by-step copy from sqlahcmey's orm tutorial, except the last line, I intended to drop all tables after the query. But the program blocked on Base.metadata.drop_all(bind=engine), below is the status of MySQL at that time(taken from MySQL Workbench):

如标记线所示，由于 table metadata lock，我建议元数据锁定是由result = session.query(User).all()引起的，因为如果删除该行，程序不会阻塞， 但我仍然不知道原因.所以我的问题是:为什么会发生这种情况，如何避免阻塞

As the marked line shows, the drop table process was hanged due to the table metadata lock, I suggest the metadata lock was caused by result = session.query(User).all() since the program did not block if that line was removed, but I still don't know the reason. So my question is: Why did this happen, how to avoid the blocking

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String


Base = declarative_base()


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(16))
    fullname = Column(String(16))
    password = Column(String(16))

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)


uri = 'mysql://root:zxsaqw21@localhost/test_sa'
engine = create_engine(uri, echo=False)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

user = User('ed', 'Ed Jones', 'edspassword')
session.add(user)
session.commit()

result = session.query(User).all()
print len(result)

Base.metadata.drop_all(bind=engine)

在执行drop_all()之前，

推荐答案

调用session.close()(或commit()或rollback()).会话仍在进行公开交易.

call session.close() (or commit(), or rollback()) before you do the drop_all(). the session is still sitting on an open transaction.

本教程针对的是没有积极的表锁定功能的sqlite(我假设您的MySQL数据库在这里使用InnoDB).

the tutorial is against sqlite which doesn't have aggressive table locking (I'm assuming your MySQL DB is using InnoDB here).

这篇关于SQLAlchemy在删除表时被阻止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！