database - Laravel DB::rollback() 不适用于交易流程

Question

首先我的引擎是 innoDB，我已经在 mySQL 上尝试了以下内容:

BEGIN;
INSERT INTO `tbl_users`(...) VALUES (...)
ROLLBACK();

它工作正常，这意味着问题不在我的 mysql 配置中。

但是当我在我的 Laravel 模型上尝试这个时:

public static function addNew($request, $department_id) {

    $result = array();

    $now = Carbon::now();

    DB::beginTransaction();

    //Checking for existing Order to set appropriate starting ID
    $result = DB::select("
        SELECT COUNT(`id`) AS 'count'
        FROM `tbl_consignmentorders`
    ")[0];

    if($result->count == 0){
        DB::update("ALTER TABLE `tbl_consignmentorders` AUTO_INCREMENT = 70000000001;");
    }

    try {
        //INSERT
        DB::insert("
            INSERT INTO `tbl_consignmentorders`
            (`from`, `to`, `status`, `created_at`, `updated_at`) 
            VALUES 
            (?, ?, ?, ?, ?)",
            [
                $department_id,
                strtoupper($request->input('supplier')),
                'PENDING',
                $now,
                $now
            ]
        );
        //GET THE LAST ID INSERTED, NEEDED FOR NEXT INSERT
        $last_id = DB::select("
            SELECT 
                LAST_INSERT_ID() AS 'id'
            FROM `tbl_consignmentorders`;"
        )[0]->id;

        //CONSTRUCTING QUERY STRING FOR VALUES
        $values = '';
        $count = 0;
        foreach($request->input('item_id') as $item) {
            $values .= ',(' . $request->input('quantity')[$count] . ', ' . $last_id . ', ' . $item . ', ' . $request->input('item_price_id')[$count] . ' )';
            $count++;
        }
        $values[0] = ' ';

        //INSERT TO DETAILS
        DB::insert("
            INSERT INTO `tbl_consignmentorderdetails`
            (`quantity`, `order_id`, `item_id`, `item_price_id`) 
            VALUES 
            $values;"
        );

        //INSERT TO TRANSACTION AUDIT
        DB::insert("
            INSERT INTO `tbl_transactions`
            (`type`, `reference_id`, `department_id`, `created_at`, `updated_at`) 
            VALUES 
            (?, ?, ?, ?, ?)",
            [
                'CONSIGNMENT ORDER',
                $last_id,
                $department_id,
                $now,
                $now
            ]
        );

        //COMMIT NOTHING FAILS
        DB::commit();
        $result = true;

    } catch (\Exception $e) {
        //ROLLBACK SOMETHING IS WRONG
        DB::rollback();
        $result = $e->getMessage();
    }

    return $result;
}

现在上面的代码在成功时可以正常工作，现在为了产生错误，我将特意更改这部分代码:

    //GET THE LAST ID INSERTED, NEEDED FOR NEXT INSERT
    $last_id = DB::select("
        SELECT 
            LAST_INSERT_ID() AS 'id'
        FROM `tbl_consignmentorders`;"
    )[0]; //<--- I removed the ->id to return the whole object causing object to string error on the next query

现在正如预期的那样，它转到 catch block 以传递错误消息，但是，在错误之前执行的查询仍然存在于数据库中，而它不应该出现在该位置。

Answer 1

如果你的系统使用多个数据库配置，你需要做这样的事情

DB::connection('database_config_2')->beginTransaction();
DB::connection('database_config_2')->rollback();
DB::connection('database_config_2')->commit();

希望对您有所帮助!