Sequelize事务未按预期回滚，请求技术排查与解决

问题 #

通过SQL事务向Customer、Order、OrderItems三张表插入数据，预期任一操作失败时整个事务回滚以保障数据一致性，但实际事务未按预期回滚——后续操作报错时仍有数据插入。已参照Sequelize文档采用托管事务，将所有查询封装在事务块中，问题依旧存在。相关代码如下：

import { Request, Response } from 'express';
import { Order }  from "../models/order.model";
import IResponse from "../interfaces/response.interface";
import { CustomerModel  } from '../models/customer.model';
import db from '../database';
import { OrderItems } from '../models/orderitem.models';
import ProductModel from '../models/product.model';

class OrderController {
async createOrder(req: Request, res: Response): Promise<void> {
    interface Product {
        product_id: number;
        name: string;
        quantity: number;
        price: number;
    }

    try {
        const { products, name, email, phone, address } = req.body;
        let totalPrice = 0;
          // Calculate total price
          products.forEach((product: Product) => {
            const subtotal = product.quantity * product.price;
            totalPrice += subtotal;
        });
        const result = await db.transaction(async (t) => {
            // Create customer
            const customer = await CustomerModel.create({
                name: name,
                email: email,
                phone: phone,
                address: address,
                transaction: t // Assign transaction
            });
            const cust_id = customer.customer_id;

            // Create order
            const order = await Order.create({
                customer_id: cust_id,
                total_amount: totalPrice,
                order_date: new Date(),
                created_by: '1',
                transaction: t // Assign transaction
            });
            const order_id = order.order_id;

            // Create order items
            const orderItemsPromises: Promise<void>[] = products.map((product: Product) => {
                return OrderItems.create({
                    order_id: order_id,
                    product_id: product.product_id,
                    order_date: new Date(),
                    quantity: product.quantity,
                    price: product.price,
                    transaction: t // Assign transaction
                });
            });

            // Wait for all order items to be created
            await Promise.all(orderItemsPromises);

            return order; // Return the order created within the transaction
        });
        res.status(201).json({ message: 'Order created successfully' });
    } catch (error: any) {
        // Send error response
        console.error(error);
        const response: IResponse = {
            statusCode: 500,
            status: 'error',
            message: error.message,
            data: '',
        };
        res.status(500).json(response);
    }
}
}

原因分析 #

问题核心在于事务参数的传递方式错误：

• Sequelize的create方法中，事务参数不能放在要插入的数据对象里，而是需要作为第二个独立的options参数传入。
• 当前代码把transaction: t写入了数据对象，这会被当作普通字段处理，并没有将操作绑定到目标事务中。因此每个create操作都是在自动提交模式下执行的，前面的操作成功后会直接写入数据库，后续报错无法回滚已插入的数据。

修复方案 #

将每个create方法的事务参数从数据对象中移出，作为第二个参数的transaction属性传入：

修改后的完整代码：

import { Request, Response } from 'express';
import { Order }  from "../models/order.model";
import IResponse from "../interfaces/response.interface";
import { CustomerModel  } from '../models/customer.model';
import db from '../database';
import { OrderItems } from '../models/orderitem.models';
import ProductModel from '../models/product.model';

class OrderController {
async createOrder(req: Request, res: Response): Promise<void> {
    interface Product {
        product_id: number;
        name: string;
        quantity: number;
        price: number;
    }

    try {
        const { products, name, email, phone, address } = req.body;
        let totalPrice = 0;
          // Calculate total price
          products.forEach((product: Product) => {
            const subtotal = product.quantity * product.price;
            totalPrice += subtotal;
        });
        const result = await db.transaction(async (t) => {
            // Create customer - 事务参数作为第二个参数传入
            const customer = await CustomerModel.create(
                {
                    name: name,
                    email: email,
                    phone: phone,
                    address: address
                },
                { transaction: t }
            );
            const cust_id = customer.customer_id;

            // Create order - 事务参数作为第二个参数传入
            const order = await Order.create(
                {
                    customer_id: cust_id,
                    total_amount: totalPrice,
                    order_date: new Date(),
                    created_by: '1'
                },
                { transaction: t }
            );
            const order_id = order.order_id;

            // Create order items - 事务参数作为第二个参数传入
            const orderItemsPromises: Promise<void>[] = products.map((product: Product) => {
                return OrderItems.create(
                    {
                        order_id: order_id,
                        product_id: product.product_id,
                        order_date: new Date(),
                        quantity: product.quantity,
                        price: product.price
                    },
                    { transaction: t }
                );
            });

            // Wait for all order items to be created
            await Promise.all(orderItemsPromises);

            return order; // Return the order created within the transaction
        });
        res.status(201).json({ message: 'Order created successfully' });
    } catch (error: any) {
        // Send error response
        console.error(error);
        const response: IResponse = {
            statusCode: 500,
            status: 'error',
            message: error.message,
            data: '',
        };
        res.status(500).json(response);
    }
}
}

额外注意事项：

• 确保你的数据库表结构中没有transaction字段，否则错误写法会触发字段不存在的额外报错。
• Sequelize托管事务的逻辑是：如果回调函数抛出错误，自动回滚事务；如果回调成功执行并返回结果，自动提交事务。修改后所有操作都绑定到同一个事务t上，任一环节失败都会触发全量回滚。

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