how to avoid lock or release metadata lock in sqlalchemy using mysql

I'm using sqlalchemy 2.0 with a MySQL database. If I create some table definitions in my Metadata() object (meta) using meta.create_all() and then immediately drop them using meta.drop_all() there is no problem -- tables are correctly created and dropped without a problem.

However, if I read from those tables, after create and before drop, the drop_all() fails. I presume this is due to a metadata lock, but I'm not sure how to release the lock i.e. via commit() or close(), i.e. methods of Session objects

Example:


from sqlalchemy import Table, Column, Integer, String, MetaData, create_engine, inspect, select
import pandas as pd

engine = create_engine(con_str)

meta = MetaData(schema='test')
table1 = Table('table1',meta, Column('id1', Integer, primary_key = True), Column('col1', String(50)))
table2 = Table('table2',meta, Column('id2', Integer, primary_key = True), Column('col2', String(50)))

meta.create_all(bind=engine)
print(f"Tables after creation: {inspect(engine.connect()).get_table_names()}")

# pd.read_sql_query(sql=select(table1), con=engine.connect())

meta.drop_all(bind=engine)
print(f"Tables after drop_all: {inspect(engine.connect()).get_table_names()}")

Output:

Tables after creation: ['table1', 'table2']
Tables after drop_all: []

If I uncomment the pd.read_sql_query() line, it will correctly return a pd.DataFrame of shape (0,2), but the next line (i.e. the drop_all() call) will freeze.

Question: what is the correct way to unlock the metadata lock or table lock that appears to be placed when query the tables?

Update:

Below is the line from show processlist; that indicates the freeze

| 432 | <user>        | <host>       | test   | Query   |       3 | Waiting for table metadata lock | DROP TABLE test.table1 |

Solution

Looks like I may have solved this. If I instead persist the connection object, rather than pass engine.connect() to the con parameter of pd.read_sql_query(), and explictly commit, the problem is resolved:

i.e.

connection = engine.connect()
pd.read_sql_query(sql=select(table1), con=connection)
connection.commit()

When using this approach between meta.create_all() and meta.drop_all(), the problem is resolved.