SQLAlchemy locked while deleting tables

Question

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

As the marked line shows, the drag and drop table process was hung up due to table metadata lock , I suggest that metadata lock was called result = session.query(User).all() , because the program did not block if this line was deleted, but I still don’t know the reason. So my question is: why did this happen, how to avoid 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) 

Answer 1

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

tutorial against sqlite that does not have aggressive table locking (I assume your MySQL DB uses InnoDB here).