When Flask-SQLAlchemy hits a save error — a duplicate unique email, for example — the session does not recover on its own: every later operation in the same session throws PendingRollbackError until you call rollback. The fix is to wrap commit in try/except IntegrityError and call db.session.rollback() right inside the except block. This applies to every write path: user registration, order creation, bulk import, or a seed script.

The Problem

Imagine a script importing a list of emails into a User table where the email column is unique=True. The first duplicate email makes the commit fail. If you only catch the error and print a message but forget to roll back, the session keeps holding that broken transaction.

from sqlalchemy.exc import IntegrityError
for email in emails:
    try:
        db.session.add(User(email=email))
        db.session.commit()
    except IntegrityError:
        print(f"Trùng email, bỏ qua: {email}")
        # Forgot the rollback here!

The damage does not stop at one duplicate email. The next loop iteration — or any query afterwards in the same session — fails with: “This Session’s transaction has been rolled back due to a previous exception”. A single error “jams” the whole session, and valid data behind it never gets processed. In an API, the client gets a 500 instead of a clear duplicate-email message.

The Solution

Just one extra rollback line in the except block leaves the session clean and ready for the next operation:

from sqlalchemy.exc import IntegrityError
for email in emails:
    try:
        db.session.add(User(email=email))
        db.session.commit()
    except IntegrityError:
        db.session.rollback()
        print(f"Trùng email, bỏ qua: {email}")

The same pattern fits neatly into an API route, with the right status code for the client:

@app.post("/users")
def create_user():
    user = User(email=request.get_json()["email"])
    try:
        db.session.add(user)
        db.session.commit()
    except IntegrityError:
        db.session.rollback()
        return jsonify({"error": "Email đã tồn tại"}), 409
    return jsonify({"id": user.id}), 201

Why is this better? Rollback discards exactly the broken transaction, while data committed earlier stays intact. The session is usable immediately — no need to create a new session or restart the process. One caveat: the object that caused the error cannot be committed again after a rollback — to retry, create a fresh object.

Apply It to Your Project

Audit every db.session.commit() call and wrap it in try/except with a rollback, starting where duplicates are most likely: user registration, CSV import, webhook sync, and seed scripts. For important writes, log the email or identifier of the failed record before rolling back so you can reconcile later. This habit turns a database error from a process-wide “jam” into an ordinary, clearly reported case.

Related Posts

FAQ

Does rollback delete data that was already committed successfully?

No. Rollback only discards the current uncommitted transaction; earlier successful commits stay in the database.

When do you need rollback, and when is closing the session enough?

You need a rollback right after a failed commit if you still want to use the same session in the same request, script, or loop. Session teardown only happens automatically at the end of a Flask request, so it cannot rescue the remaining operations within that request.

Do errors other than IntegrityError need a rollback too?

Yes. Any error that makes a commit fail leaves the session in a state that needs a rollback. If you cannot yet tell error types apart, catch the broader SQLAlchemyError, roll back first, then handle it.

Should you put rollback in a finally block, just to be safe?

No. Only roll back in the except block when a real error happened, because a rollback in finally could silently discard changes you added after a successful commit.

💬 Have you tried this in your own project? If you hit an error or found a neater way, leave a comment below — I read them all and will fold the best ideas into the post.

References