PostgreSQL意外回滚问题：提交后数据丢失排查求助

问题现象 #

• 用户注册登录流程无报错，但pgAdmin查看数据库无用户数据
• SQLAlchemy日志显示COMMIT后随即触发ROLLBACK
• 数据库初始化提示Created Database!出现两次：应用启动时一次，访问注册页面时再次触发

相关代码片段 #

用户注册逻辑 #

new_user = User(email=email, username=username, password=password)
db.session.add(new_user)
db.session.commit()
login_user(new_user, remember=True)
flash('Account created!', category='success')
return redirect(url_for('home.html'))

User模型定义 #

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(150), unique=True)
    password = db.Column(db.String(150))
    username = db.Column(db.String(150))

数据库初始化代码 #

with app.app_context():   
    db.create_all()
    print('Created Database!')

关键日志 #

应用启动时的建表日志 #

CREATE TABLE "user" (
 id SERIAL NOT NULL, 
 email VARCHAR(150), 
 password VARCHAR(150), 
 username VARCHAR(150), 
 PRIMARY KEY (id), 
 UNIQUE (email)
)
Created Database!

注册请求触发的日志 #

"GET /sign-up HTTP/1.1" 200 -
Created Database! <----------- 第二次初始化提示
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT "user".id AS user_id, 
"user".email 
AS user_email, "user".password AS user_password, "user".username AS 
user_username 
FROM "user" 
WHERE "user".email = %(email_1)s 
LIMIT %(param_1)s
INFO sqlalchemy.engine.Engine [generated in 0.00048s] {'email_1': 
'test@test.test', 'param_1': 1}
INFO sqlalchemy.engine.Engine INSERT INTO "user" (email, password,             
username) VALUES (%(email)s, %(password)s, %(username)s) RETURNING                 
"user".id
INFO sqlalchemy.engine.Engine [generated in 0.00041s] {'email':     
'test@test.test', 'password': 'test', 'username': 'test'}
INFO sqlalchemy.engine.Engine COMMIT
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT "user".id AS user_id, 
"user".email     
AS user_email, "user".password AS user_password, "user".username AS         
user_username 
FROM "user" 
WHERE "user".id = %(pk_1)s
INFO sqlalchemy.engine.Engine [generated in 0.00045s] {'pk_1': 1}
INFO sqlalchemy.engine.Engine ROLLBACK
"POST /sign-up HTTP/1.1" 302 -
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine SELECT "user".id AS user_id, 
"user".email AS user_email, "user".password AS user_password, 
"user".username AS user_username 
FROM "user" 
WHERE "user".id = %(pk_1)s
INFO sqlalchemy.engine.Engine [generated in 0.00040s] {'pk_1': 1}
INFO sqlalchemy.engine.Engine ROLLBACK
"GET / HTTP/1.1" 200 -

排查与解决方案 #

1. 修正数据库初始化位置
   目前db.create_all()的执行逻辑被重复触发，说明这段代码被放在了会被请求重复调用的位置（比如视图函数内部）。将其移到应用启动时仅执行一次的位置：

   if __name__ == '__main__':
       with app.app_context():
           db.create_all()
           print('Created Database!')
       app.run(host='0.0.0.0', debug=True)
   

   这样只会在应用启动时初始化一次数据库，避免重复执行。

2. 排查事务回滚原因

   • 在commit()处添加异常捕获，确认是否有隐藏错误导致回滚：

     try:
         db.session.commit()
     except Exception as e:
         db.session.rollback()
         print(f"Commit error: {str(e)}")
         flash('Failed to create account', category='error')
         return redirect(url_for('sign_up'))
     

   • 检查Flask-SQLAlchemy的会话配置，确保没有全局的自动回滚逻辑，同时确认数据库连接字符串正确（Docker环境下需使用PostgreSQL容器名称作为主机地址）。

3. 确保PostgreSQL数据持久化
   检查Docker配置，为PostgreSQL服务挂载数据卷，避免容器重启后数据丢失：

   # docker-compose.yml示例
   services:
     web:
       build: .
       ports:
         - "5000:5000"
       depends_on:
         - postgres
     postgres:
       image: postgres:15-alpine
       environment:
         POSTGRES_USER: flask_user
         POSTGRES_PASSWORD: flask_pass
         POSTGRES_DB: flask_auth
       volumes:
         - postgres_data:/var/lib/postgresql/data  # 挂载数据卷
   volumes:
     postgres_data:  # 定义命名卷
   

4. 检查会话状态
   登录后的回滚可能是会话状态异常导致，确认login_user后是否有额外的会话操作，或者是否存在多个请求上下文混用会话的情况。确保每个请求的会话都是独立的，避免手动修改会话状态。

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