ThinkPHP5 数据库事务 - 03

MySQL :

CREATE DATABASE IF NOT EXISTS `db1` DEFAULT CHARACTER SET utf8 ;

USE `db1`; CREATE TABLE `think_data` (   `user_id` int(8) unsigned NOT NULL AUTO_INCREMENT,   `user_name` varchar(255) NOT NULL COMMENT '名称',   `deposit` decimal(18,4) DEFAULT NULL,   PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; insert  into `think_data`(`user_id`,`user_name`,`deposit`) values (1,'Tim','5000.0000'),(2,'Jason','8000.0000'),(3,'Tom','12001.0000'),(4,'Jack','800051.0000'),(5,'Frank','35009.0000'),(6,'Tina','100.0000'),(7,'Suman','3759.0000'),(8,'Linda','5490.0000');

DELIMITER // -- 不要省略双斜线 否则无法创建存储过程 CREATE PROCEDURE transfer(IN from_id INT,IN to_id INT,money DECIMAL(18,4)) BEGIN     DECLARE CheckError INTEGER;     DECLARE    CONTINUE HANDLER FOR SQLEXCEPTION SET CheckError = 1;     START TRANSACTION;         UPDATE think_data SET deposit = deposit - money WHERE think_data.user_id = from_id ;     UPDATE think_data SET deposit = deposit + money WHERE think_data.user_id = to_id ;         INSERT INTO think_data VALUES    (3, 'Rainly',3600.50); -- 人为的制造一个主键重复的错误         IF CheckError = 1 THEN             ROLLBACK;         ELSE             COMMIT;         END IF;      SELECT * FROM think_data WHERE think_data.`user_id` IN(from_id,to_id); -- 返回修改后的记录集合不能放在回滚操作的前面 END; // -- 不要省略双斜线 否则无法创建存储过程

-- 执行存储过程的方式：

CALL transfer(5,6,3000)

[root@contoso ~]# cat /dev/null > /var/log/mariadb/queries.log && tail -f /var/log/mariadb/queries.log 170805 14:43:44    10 Query     CALL transfer(5,6,3000) 编号为5的账户转账给账户为6的用户 3000元失败了 发生了回滚，测试成功！

INSERT INTO think_data VALUES    (3, 'Rainly',3600.50); -- 人为的制造一个主键重复的错误,只要将这条抛出异常的插入操作去掉就是个非常标准存储过程事务

ThinkPHP5 原生SQL事务实现

apps/index/controller/User.php :

<?php namespace app\index\controller; use think\Controller; use think\Request; use think\Db; use PDO; class User extends Controller {     /**      * 显示资源列表      *      * @return \think\Response      */     public function index()     {         $from_id = 5;         $to_id = 6;         $money = 3000.54;         // 启动事务         $db1 = Db::connect('db1');         $db1->startTrans();         try{             $db1->execute(                 'UPDATE think_data SET deposit = deposit - ? WHERE think_data.user_id = ?',                 [[$money,PDO::PARAM_INT],[$from_id,PDO::PARAM_INT]]             );             $db1->execute(                 'UPDATE think_data SET deposit = deposit + ? WHERE think_data.user_id = ?',                 [[$money,PDO::PARAM_INT],[$to_id,PDO::PARAM_INT]]             );             // $db1->execute('INSERT INTO think_data VALUES    (3, \'Rainly\',3600.50)');             $db1->commit();         } catch (\Exception $e){             $db1->rollback();         }         $resultSet = $db1->query(             'SELECT * FROM think_data WHERE think_data.`user_id` IN(?,?)',             [[$from_id,PDO::PARAM_INT],[$to_id,PDO::PARAM_INT]]         );         var_dump($resultSet);     }     /**      * 显示创建资源表单页.      *      * @return \think\Response      */     public function create()     {     }     /**      * 保存新建的资源      *      * @param  \think\Request $request      * @return \think\Response      */     public function save(Request $request)     {     }     /**      * 显示指定的资源      *      * @param  int $user_id      * @return \think\Response      */     public function read($user_id)     {     }     /**      * 显示编辑资源表单页.      *      * @param  int $user_id      * @return \think\Response      */     public function edit($user_id)     {     }     /**      * 保存更新的资源      *      * @param  \think\Request $request      * @param  int $user_id      * @return \think\Response      */     public function update(Request $request, $user_id)     {     }     /**      * 删除指定资源      *      * @param  int $user_id      * @return \think\Response      */     public function delete($user_id)     {     }     public function miss()     {     } }

路由配置 apps/route.php :

<?php use think\Route; //主表路由(分组路由配置顺序必须是从表路由优先配置，否则从表路由一直会路由到主表控制器的方法上) Route::group(['name'=>'users','prefix'=>'index/User/'], function() {     Route::get('create$','create',['merge_extra_vars'=>true]);     Route::post('/$','save',['merge_extra_vars'=>true]);     Route::get(':user_id/edit$','edit',['merge_extra_vars'=>true]);     Route::get(':user_id$','read',['merge_extra_vars'=>true]);     Route::put(':user_id$','update',['merge_extra_vars'=>true]);     Route::delete(':user_id$','delete',['merge_extra_vars'=>true]);     Route::get('/$','index',['merge_extra_vars'=>true]);     Route::miss('miss'); // 在根资源路由里面写miss路由 }, [], ['user_id' => '\d+']); // create GET  http://contoso.org/users/create // save   POST http://contoso.org/users // edit   GET  http://contoso.org/users/10/edit // read   GET  http://contoso.org/users/10 // update PUT  http://contoso.org/users/10 // delete DELETE http://contoso.org/users/10 // index  GET  http://contoso.org/users