Transaction cannot be committed because it has been finished with state: commit

Question

Hi all i'am getting this error in sequilize transaction, please can anyone help me to resolve this issue and i'am doing it in nodejs lambda.

aftre exexuting this code it saves the data as well but it returns the error saying "Transaction cannot be committed because it has been finished with state: commit", Below is the code :

return database_object.sequelize.transaction({autocommit:false}, (transaction_object) => {
    let update_attributes = {
        date_updated : dt.format('Y-m-d H:M:S'),
        is_returned : 1
    };
    console.info("UPDATE-create-return-attributes:info", update_attributes);
    return database_object.order_item_details.model.update(update_attributes, {where : where_object, transaction : transaction_object})
    .then(result => {
        where_object = {
            order_no : order_details.item_details.order_no,
            website_id : order_details.item_details.website_id
        };
        return database_object.order_details.model.find({where : where_object, transaction : transaction_object})
        .then(result => {
            let customer_id = result.dataValues.customer_id;
            let orderd_date = result.dataValues.order_date;
            let return_create_obj;
            if(!order_details.prev_return_request) {
                return_create_obj = {
                    customer_id : customer_id,
                    subsidiary_id : order_details.allocation_data.subsidiary_id,
                    store_id : order_details.allocation_data.store_id,
                    order_id : order_details.item_details.order_id,
                    order_no : order_details.item_details.order_no,
                    item_id : item_details.item_id,
                    website_id : item_details.website_id,
                    lastmile_id : item_details.lastmile_id,
                    customerlastmilepartner_name : !item_details.customerlastmilepartner_name ? null : item_details.customerlastmilepartner_name,
                    sku : order_details.item_details.sku,
                    status : item_details.status,
                    lastmile_status : order_details.lastmile_allocation.status,
                    qty_to_return : item_details.qty_to_return,
                    reason_to_return : item_details.reason_to_return,
                    remarks : item_details.remark,
                    order_date : orderd_date,
                    request_type : item_details.request_type,
                    return_type : item_details.return_type,
                    type: item_details.type,
                    forward_awb_no: item_details.forward_awb_no,
                    reverse_awb_no: item_details.reverse_awb_no,
                    valid_until: item_details.valid_until,
                    is_manifested : order_details.lastmile_allocation.is_manifested,
                    create_by : item_details.create_by,
                    create_time : item_details.create_time,
                    modified_by : !item_details.modified_by ? item_details.create_by : item_details.modified_by,
                    modified_time : !item_details.modified_time ? item_details.create_time : item_details.modified_time,
                    mp_return_id : !item_details.mp_return_id ? null : item_details.mp_return_id
                    };
                    return database_object.item_return.model.create(return_create_obj, {transaction : transaction_object})
                    .then(result => {
                        transaction_object.commit();
                    return create_return_cb(null, {item_id : item_details.item_id, status : "Success", detail : "Return request created successfully for item"});
                    })
                    .catch(error => {
                        transaction_object.rollback();
                        console.error("UPDATE-create-return-attributes:error", error);
                        return create_return_cb(error);
                    });
            } else {
                update_attributes = {
                    qty_to_return : item_details.qty_to_return + order_details.prev_return_request.qty_to_return,
                    reason_to_return: item_details.remark,
                    request_type : item_details.request_type,
                    return_type : item_details.return_type,
                    type: item_details.type,
                    status : item_details.status,
                    forward_awb_no: item_details.forward_awb_no,
                    reverse_awb_no: item_details.reverse_awb_no,
                    valid_until: item_details.valid_until,
                    create_by: item_details.create_by,
                    create_time: item_details.create_time,
                    modified_time : dt.format('Y-m-d H:M:S'),//!item_details.modified_time ? item_details.create_time : item_details.modified_time,
                    lastmile_id : item_details.lastmile_id              
                };
                where_object.customer_id = customer_id;
                where_object.order_no = order_details.item_details.order_no;
                console.info("UPDATE-create-return-attributes:info", update_attributes);
                return database_object.item_return.model.update(update_attributes, {where : where_object, transaction : transaction_object})
                .then(result => {
                    transaction_object.commit();
                    return create_return_cb(null, {item_id : item_details.item_id, status : "Success", detail : "Return request updated successfully for item"});
                })
                .catch(error => {
                    transaction_object.rollback();
                        console.error("UPDATE-create-return-attributes:error", error);
                        return create_return_cb(error);
                });
            }
        })
        .catch(error => {
            transaction_object.rollback();
            console.error("UPDATE-create-return-attributes:error", error);
            return create_return_cb(error);
        });
    })
    .catch(error => {
        transaction_object.rollback();
        console.error("UPDATE-create-return-attributes:error", error);
        return create_return_cb(error);
    });
});

and also i can see the transaction logs as below. 1. START TRANSACTION; 2. SET autocommit = 0;

aftre that am not able to see the transaction complete and done.

Answer 1

Remove {autocommit:false} and all other manual commit and rollback code ( transaction_object.commit(), transaction_object.rollback()) to make it pure managed transaction.

For Unmanaged transaction, use the below format

const t = await sequelize.transaction();

try {

  // Then, we do some calls passing this transaction as an option:

  const user = await User.create({
    firstName: 'Bart',
    lastName: 'Simpson'
  }, { transaction: t });

  await user.addSibling({
    firstName: 'Lisa',
    lastName: 'Simpson'
  }, { transaction: t });

  // If the execution reaches this line, no errors were thrown.
  // We commit the transaction.
  await t.commit();

} catch (error) {

  // If the execution reaches this line, an error was thrown.
  // We rollback the transaction.
  await t.rollback();

}