sqlalchemy mysql application - unable to rename table - getting waiting for table metadata lock

Question

I am using a python fastapi application that queries a mysql 8 database & provides response. It uses sqlalchemy to query the mysql database. It runs all day 24/7 with 2-5 requests per second.

Connection is made as follows:

engine = create_engine(
    'mysql+pymysql://username:password@127.0.0.1/dataset'
)

conn = engine.connect()

agent_list = pd.read_sql(AgentLandingModel.select().where(AgentLandingModel.c.agent_ref_id == agent_ref_id), conn)

Now, I have a dag in airflow that is set to update the table once a day that is queried all day by the fastapi application. It first inserts the data in a temporary table & then renames it to the main table to minimize downtime. It goes like this:

sql_statement = f'''START TRANSACTION;
        CREATE TABLE IF NOT EXISTS {temp_tablename} LIKE {table};
        RENAME TABLE {table} TO {table}_old;
        RENAME TABLE {temp_tablename} TO {table};
        DROP TABLE {table}_old;
        COMMIT;'''

        logger.info(sql_statement)
        cursor.execute(sql_statement)
        logger.info(f'Ran transactional query')

But this never runs properly. It gets stuck with the message Waiting for table metadata lock in mysql.

sqlalchemy makes a sleep process in mysql that blocks metadata lock required for rename. The process looks like below on running SHOW FULL PROCESSLIST;

Now, I'm well aware that killing the process will solve the problem temporarily, but this is an automated process that has to run everyday. So what I'm looking for is how to avoid the lock in the first place.

On doing some research, I found that setting isolation level to 'REPEATABLE READ' OR 'READ UNCOMMITED' might help, but it didn't. Also, I tried disabling connection pooling using NullPool, but even that didn't help.

engine = create_engine(
    'mysql+pymysql://username:password@127.0.0.1/dataset',
    isolation_level="READ UNCOMMITTED",
    poolclass=NullPool
)

conn = engine.connect()

How can I make the connection in application such that it doesn't lock the table? I want to be able to update the base table once a day while the application is running, as it runs 24/7.

sqlalchemy mysql application - unable to rename table - getting waiting for table metadata lock

0 Answers