Python线程池执行MySQL DELETE后线程暂停，移除commit则查询不生效

核心问题 #

你的问题根源在于MySQL连接和游标并非线程安全，加上事务提交时可能遇到的锁等待，导致线程出现暂停。同时，频繁创建、销毁连接会加重性能负担，进一步放大问题。

具体解决步骤 #

• 每个线程必须独立持有数据库连接，绝对不能跨线程共享连接对象
• 优先使用数据库连接池管理连接，避免频繁创建销毁连接的开销
• 排查是否存在行锁冲突：比如其他事务正在操作你要删除的行，导致commit()时等待锁释放，表现为线程暂停

修正后的代码示例 #

方式1：每个线程独立创建连接（基础版） #

import mysql.connector
from concurrent.futures import ThreadPoolExecutor

def delete_worker(worker_id):
    # 每个线程单独创建连接
    mydb = mysql.connector.connect(
        host="localhost",
        user="user",
        passwd="1",
        database="workers"
    )
    try:
        cursor = mydb.cursor()
        query = "DELETE FROM `workers` WHERE id = %s"
        cursor.execute(query, (worker_id,))
        mydb.commit()  # 提交事务，确保删除生效
    except Exception as e:
        mydb.rollback()  # 出错时回滚事务
        print(f"删除失败：{e}")
    finally:
        # 确保资源被正确释放
        cursor.close()
        mydb.close()

# 使用线程池执行删除任务
if __name__ == "__main__":
    worker_ids = [1,2,3,4,5]
    with ThreadPoolExecutor(max_workers=5) as executor:
        executor.map(delete_worker, worker_ids)

方式2：使用连接池（推荐，性能更优） #

import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
from concurrent.futures import ThreadPoolExecutor

# 初始化数据库连接池
pool = MySQLConnectionPool(
    pool_name="worker_pool",
    pool_size=5,
    host="localhost",
    user="user",
    passwd="1",
    database="workers"
)

def delete_worker(worker_id):
    # 从连接池获取连接
    mydb = pool.get_connection()
    try:
        cursor = mydb.cursor()
        query = "DELETE FROM `workers` WHERE id = %s"
        cursor.execute(query, (worker_id,))
        mydb.commit()
    except Exception as e:
        mydb.rollback()
        print(f"删除失败：{e}")
    finally:
        cursor.close()
        mydb.close()  # 归还连接到池

# 使用线程池执行删除任务
if __name__ == "__main__":
    worker_ids = [1,2,3,4,5]
    with ThreadPoolExecutor(max_workers=5) as executor:
        executor.map(delete_worker, worker_ids)

额外排查点 #

• 检查数据库是否有其他长期运行的事务，锁定了你要删除的行，导致commit()阻塞
• 可执行SELECT * FROM information_schema.innodb_locks;查看当前锁情况，确认是否存在锁等待

内容的提问来源于stack exchange，提问作者A D