Flask+MySQL.connector执行TRUNCATE表时频繁挂起问题求助

• 事务锁阻塞：TRUNCATE是DDL语句，会隐式提交事务，但如果当前连接或其他连接存在未提交的事务持有该表的锁（比如未提交的SELECT、UPDATE操作），TRUNCATE会一直等待锁释放，导致任务挂起。
• mysql.connector连接/事务配置异常：若连接未开启autocommit，之前的操作未手动提交事务，会导致事务积累，后续TRUNCATE因等待事务释放而挂起；连接池复用状态异常的连接也会触发这类问题。
• 外键约束导致锁等待：如果member_additional_skills表与其他表存在外键关联，TRUNCATE会等待关联表的锁，或因外键约束无法立即执行，进而陷入等待。

修复TRUNCATE挂起问题 #

1. 强制事务提交与自动提交配置 #

执行TRUNCATE前确保当前连接无未提交事务，或直接开启自动提交：

import mysql.connector

# 初始化连接时开启autocommit
db_config = {
    "host": "你的数据库地址",
    "user": "用户名",
    "password": "密码",
    "database": "数据库名",
    "autocommit": True
}
conn = mysql.connector.connect(**db_config)

# 若未开启autocommit，执行TRUNCATE前手动提交
# conn.commit()
cursor = conn.cursor()
cursor.execute("TRUNCATE TABLE member_additional_skills;")

2. 排查并释放阻塞锁 #

登录MySQL服务器，执行以下命令排查阻塞进程：

-- 查看所有连接进程
SHOW PROCESSLIST;
-- 查看InnoDB锁详情
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

找到阻塞TRUNCATE的进程（State列显示Waiting for table metadata lock等），执行KILL 进程ID;释放锁。

3. 临时禁用外键约束（若存在外键） #

如果表有外键关联，先禁用外键检查再执行TRUNCATE：

cursor.execute("SET FOREIGN_KEY_CHECKS = 0;")
cursor.execute("TRUNCATE TABLE member_additional_skills;")
cursor.execute("SET FOREIGN_KEY_CHECKS = 1;")

增量更新实现（替代全量截断重填） #

针对你的能力情况，给出最简增量更新方案，无需复杂逻辑：

步骤1：添加唯一索引 #

先给member_additional_skills表添加唯一键（假设member_id+skill_id是唯一标识）：

ALTER TABLE member_additional_skills ADD UNIQUE KEY idx_member_skill (member_id, skill_id);

步骤2：批量插入/更新数据 #

用INSERT ... ON DUPLICATE KEY UPDATE实现新增数据插入、已有数据更新：

# 假设new_data是从数据源获取的新数据列表，每条格式为(member_id, skill_id, skill_name, ...)
insert_update_sql = """
INSERT INTO member_additional_skills (member_id, skill_id, skill_name, create_time)
VALUES (%s, %s, %s, NOW())
ON DUPLICATE KEY UPDATE
    skill_name = VALUES(skill_name),
    update_time = NOW()
"""
cursor.executemany(insert_update_sql, new_data)

步骤3：删除已失效的旧数据 #

通过临时表匹配，删除主表中不再存在于新数据的记录：

# 创建临时表存储新数据的唯一标识
cursor.execute("CREATE TEMPORARY TABLE temp_skills (member_id INT, skill_id INT, PRIMARY KEY(member_id, skill_id));")
# 插入新数据的唯一标识
temp_data = [(item[0], item[1]) for item in new_data]
cursor.executemany("INSERT INTO temp_skills (member_id, skill_id) VALUES (%s, %s);", temp_data)
# 删除主表中不在临时表的记录
cursor.execute("""
DELETE FROM member_additional_skills
WHERE (member_id, skill_id) NOT IN (SELECT member_id, skill_id FROM temp_skills);
""")

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