SQL Alchemy Relationship loader leaves a lock on table?

Question

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'

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.

Answer 1

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 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.

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')

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