Issue WIth ACID Compliant Transaction With Flask-SqlAlchemy

Question

As per Flask-SqlAlchemy Docs:
*
You may see uses of Model.query to build queries. This is an older interface for queries that is considered legacy in SQLAlchemy. Prefer using db.session.execute(db.select(...)) instead.*

I want to perform a batch transaction where I am performing multiple transactions and all of them should succeed or all should fail.


@user.route("/generate", methods=["GET", "POST"])
@login_required
def generate_codes():
    username = current_user.id
    credit = get_user_credit(username)
    msg, err = None, None

    if products := db.session.execute(db.select(Products).where(Products.user == current_user.id)).scalars().all():
        products = list(products)

    if request.method == "POST":
        qty = int(request.form.get('qty'))
        p_name = request.form.get('p_name')
        p_batch = request.form.get('p_batch')

        if qty > credit:
            err = "Insufficient Credit!! Please Recharge Your Account"
            return render_template("/user/codes_add.html", products=products, err=err, msg=msg)

        batch_id = ulid.new().str
        codes: list[UnverifiedCodes] = gen_random_codes(qty, current_user.id, batch_id)


        try:
            db.session.add_all(codes)
            db.session.execute(db.update(Users).where(Users.username == username)
                               .values(credit=Users.credit - qty, code_count=Users.code_count + qty))
            register_batches(username=current_user.id, total_count=qty, batch_id=batch_id, p_name=p_name,
                             p_batch=p_batch)
            activity_logger(f"{qty} Codes Generated")
            msg = f"{qty} Codes Added Successfully"
            db.session.commit()
        except Exception:
            db.session.rollback()
            err = "Internal Error Occurred"
            raise

    return render_template("/user/codes_add.html", products=products, err=err, msg=msg)

 I wan the transaction to fail if any exception occures within the try except block. However there is a big Issue, i can see the transaction is commited even though any exception occured.

Now i am knowingly creating an exception:

dummy_var = []
        try:
            db.session.add_all(codes)
            dummy_var.split(",")
            db.session.execute(db.update(Users).where(Users.username == username)
                               .values(credit=Users.credit - qty, code_count=Users.code_count + qty))
            register_batches(username=current_user.id, total_count=qty, batch_id=batch_id, p_name=p_name,
                             p_batch=p_batch)
            activity_logger(f"{qty} Codes Generated")
            msg = f"{qty} Codes Added Successfully"
            db.session.commit()
        except Exception:
            db.session.rollback()
            err = "Internal Error Occurred"
            raise

Now i have added a dummy_var and applying split to cause an exception. It it works perfectly, i see no codes are commited to db. But here comes the problem:

dummy_var = []
try:
    db.session.add_all(codes)
    
    db.session.execute(db.update(Users).where(Users.username == username)
                       .values(credit=Users.credit - qty, code_count=Users.code_count + qty))
    dummy_var.split(",")
    register_batches(username=current_user.id, total_count=qty, batch_id=batch_id, p_name=p_name,
                     p_batch=p_batch)
    activity_logger(f"{qty} Codes Generated")
    msg = f"{qty} Codes Added Successfully"
    db.session.commit()
except Exception:
    db.session.rollback()
    err = "Internal Error Occurred"
    raise

Now i have moved the split part after db.session.execute() and something strange happens. I see exception raises also the 2 db operation (adding codes and updating credit is commited) why this happens particularlyu

I have tried with db.session.begin() and begin_nested() these are again causing issues saying A session has already begun

Issue WIth ACID Compliant Transaction With Flask-SqlAlchemy

0 Answers0