Docker环境下psycopg2删除PostgreSQL表超时问题求助

根源分析 #

从日志对比可明确锁定问题：

• 移除@login_required后，Flask-Login查询用户信息的事务会自动执行ROLLBACK，释放所有数据库锁
• 保留装饰器时，该用户查询事务仅执行了BEGIN和SELECT，无后续提交/回滚操作，事务长期处于未结束状态，持有相关资源锁，直接阻塞了DROP TABLE操作（DROP TABLE需要获取表的排他锁）

这类问题通常是Flask-Login验证用户时的数据库连接未正确关闭事务，或是应用中Flask-Login的数据库操作与业务操作（删表）使用了不同的连接策略，导致事务隔离引发锁等待。

修复方案 #

方案1：修正Flask-Login用户加载逻辑的事务处理 #

检查user_loader回调函数，确保查询用户后，数据库连接的事务被正确提交或回滚：

from flask_login import user_loader

@user_loader
def load_user(user_id):
    conn = psycopg2.connect(
        database=os.getenv('POSTGRES_DB'),
        user=os.getenv('POSTGRES_USER'),
        password=os.getenv('POSTGRES_PASSWORD'),
        host=os.getenv('POSTGRES_HOST'),
        port=os.getenv('POSTGRES_PORT')
    )
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE user_id = %s", (user_id,))
        user_data = cursor.fetchone()
        conn.commit()
        return User(user_data)  # 替换为你的User模型类
    finally:
        cursor.close()
        conn.close()

方案2：在删表前清理残留事务 #

若无法修改Flask-Login逻辑，可在删表操作前强制回滚当前会话的残留事务：

@site.route("/drop-table", methods=['GET','POST'])
@login_required
def drop_table():
    form = DeleteTableForm()
    if request.method == "POST":
        tablename = form.tablename.data
        db_config = {
            "database": os.getenv('POSTGRES_DB'),
            "user": os.getenv('POSTGRES_USER'),
            "password": os.getenv('POSTGRES_PASSWORD'),
            "host": os.getenv('POSTGRES_HOST'),
            "port": os.getenv('POSTGRES_PORT')
        }
        try:
            # 先清理残留事务
            cleanup_conn = psycopg2.connect(**db_config)
            cleanup_conn.rollback()
            cleanup_conn.close()

            # 执行删表（用参数化避免SQL注入）
            conn = psycopg2.connect(**db_config)
            cursor = conn.cursor()
            sql_command = psycopg2.sql.SQL("DROP TABLE {}").format(
                psycopg2.sql.Identifier(tablename)
            )
            cursor.execute(sql_command)        
            conn.commit()
            flash(f"表 {tablename} 删除成功", "success")
        except Exception as e:
            flash(f"无法删除表 {tablename}：{str(e)}", "error")
            app.logger.info("错误信息：%s", str(e))
            if 'conn' in locals():
                conn.rollback()
        finally:
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()
    return render_template("drop-table.html", form=form)

方案3：使用连接池管理数据库连接 #

避免手动创建连接，改用psycopg2.pool或Flask数据库扩展（如Flask-SQLAlchemy）管理连接，确保每个请求的连接正确回收、事务自动结束：

from psycopg2 import pool

# 应用启动时初始化连接池
postgres_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=20,
    user=os.getenv('POSTGRES_USER'),
    password=os.getenv('POSTGRES_PASSWORD'),
    database=os.getenv('POSTGRES_DB'),
    host=os.getenv('POSTGRES_HOST'),
    port=os.getenv('POSTGRES_PORT')
)

# user_loader中使用连接池
@user_loader
def load_user(user_id):
    conn = postgres_pool.getconn()
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE user_id = %s", (user_id,))
        user_data = cursor.fetchone()
        conn.commit()
        return User(user_data)
    finally:
        cursor.close()
        postgres_pool.putconn(conn)

# 删表接口中使用连接池
@site.route("/drop-table", methods=['GET','POST'])
@login_required
def drop_table():
    form = DeleteTableForm()
    if request.method == "POST":
        tablename = form.tablename.data
        try:
            conn = postgres_pool.getconn()
            cursor = conn.cursor()
            sql_command = psycopg2.sql.SQL("DROP TABLE {}").format(
                psycopg2.sql.Identifier(tablename)
            )
            cursor.execute(sql_command)        
            conn.commit()
            flash(f"表 {tablename} 删除成功", "success")
        except Exception as e:
            flash(f"无法删除表 {tablename}：{str(e)}", "error")
            app.logger.info("错误信息：%s", str(e))
            conn.rollback()
        finally:
            cursor.close()
            postgres_pool.putconn(conn)
    return render_template("drop-table.html", form=form)

调试建议 #

1. 查看锁详情，定位阻塞源：

SELECT l.locktype, l.mode, t.relname, psa.query 
FROM pg_locks l 
JOIN pg_class t ON l.relation = t.oid 
JOIN pg_stat_activity psa ON l.pid = psa.pid
WHERE t.relname IN ('davey1', 'users');

2. 开启PostgreSQL锁等待日志：
   在PostgreSQL配置中设置log_statement = 'all'和log_lock_waits = on，记录所有锁等待事件，便于排查阻塞过程。

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