Flask应用触发IntegrityError后数据库ID不连续如何解决
根本原因
PostgreSQL 自增主键基于序列(sequence)实现,序列的取值不随事务回滚而回退。每次执行插入操作时,数据库会先调用序列的nextval方法获取下一个ID,哪怕后续因为唯一约束冲突导致事务回滚,已经取出的ID也不会被放回序列池,因此失败的插入请求会消耗ID,最终出现ID断层。
如果业务对ID连续性没有强要求,该现象属于PostgreSQL的正常特性,不会影响功能运行,不需要额外处理。
修复方案
1. 插入前主动查重(核心优化)
在执行插入逻辑前先查询是否存在同名产品,存在则直接返回错误,不触发插入操作,从源头避免因唯一约束冲突消耗序列值。
2. 异常捕获与回滚
针对高并发场景下可能出现的「两个相同插入请求同时通过前置校验」的极端情况,主动捕获IntegrityError并回滚会话,避免会话污染。
3. 修复隐藏逻辑错误
现有代码中/store/<product_name>路由的查询逻辑存在问题:query.get_or_404()仅支持按主键(即id字段)查询,传入产品名称会永远返回404,需要改为按name字段过滤。同时Flask 2.0+版本要求db.create_all()必须在应用上下文中执行,需要补充上下文声明。
修改后代码示例
from flask import Flask, render_template, request, flash, jsonify from flask_sqlalchemy import SQLAlchemy from sqlalchemy import exc app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:postgres@db:5432/postgres' app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False db = SQLAlchemy(app) class Product(db.Model): __tablename__ = 'store' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), unique=True, nullable=False) category = db.Column(db.String(120), nullable=False) price = db.Column(db.Integer, nullable=False) def __init__(self, name, category, price): self.name = name self.category = category self.price = price @app.route('/store', methods=['GET','POST']) def add_product(): if request.method=='GET': products = Product.query.all() results = [ { "name": product.name, "category": product.category, "price": product.price } for product in products] return {"count": len(results), "products": results} elif request.method=='POST': if not request.is_json: return {"error": "The request payload is not in JSON format"} data = request.get_json() # 插入前主动查重 exist_product = Product.query.filter_by(name=data['name']).first() if exist_product: return {"error": f"Product {data['name']} already exists"} try: new_product = Product(name=data['name'], category=data['category'], price=data['price']) db.session.add(new_product) db.session.commit() return {"message": f"Product {new_product.name} has been created successfully."} except exc.IntegrityError: # 捕获并发场景下的唯一约束冲突 db.session.rollback() return {"error": f"Product {data['name']} already exists"} @app.route('/store/<product_name>', methods=['GET','PUT','DELETE']) def handle_product(product_name): # 修复按名称查询的逻辑 product = Product.query.filter_by(name=product_name).first_or_404() if request.method == 'GET': result = { "name": product.name, "category": product.category, "price": product.price } return {"message": "success", "product": result} elif request.method == 'PUT': data = request.get_json() product.name = data['name'] product.category = data['category'] product.price = data['price'] db.session.add(product) db.session.commit() return {"message": f"Product {product.name} successfully updated"} elif request.method == 'DELETE': db.session.delete(product) db.session.commit() return {"message": f"Product {product.name} successfully deleted."} if __name__ == '__main__': with app.app_context(): db.create_all() app.run(host='0.0.0.0')
内容的提问来源于stack exchange,提问作者Wolodyjowsky
相关产品推荐
相关产品推荐

