laravel mysql事务无效,Laravel DB :: rollback（）在事务处理中不起作用

First off my engine is innoDB and I already tried the following on mySQL:

BEGIN;

INSERT INTO `tbl_users`(...) VALUES (...)

ROLLBACK();

And it works fine, meaning the problem wasn't in my mysql config.

But when I tried this on my Laravel Model:

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;

}

Now the above code works fine when successful, now to generate error, I will deliberately change this part of code:

//GET THE LAST ID INSERTED, NEEDED FOR NEXT INSERT

$last_id = DB::select("

SELECT

LAST_INSERT_ID() AS 'id'

FROM `tbl_consignmentorders`;"

)[0]; //id to return the whole object causing object to string error on the next query

Now as expected it goes to the catch block to pass the error message but, the queries executed before the error is still present in the database where it should not.

解决方案

My mistake,

I use DB::rollback(); instead of DB::rollBack(); with capital B