Mysql 存储过程的事务使用

一.创建表语句

DELIMITER $$
CREATE TABLE if not exists SPE_TRANSACTION_TAB1 (
  ID INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL ,
  `NAME` VARCHAR(5) NULL,
  AGE INT(3) NULL ,
  CREATED DATETIME(0) NULL 
)$$
CREATE TABLE if not exists SPE_TRANSACTION_TAB2 (
  ID INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
  `NAME` VARCHAR(5) NULL,
  AGE INT(3) NULL ,
  CREATED DATETIME(0) NULL 
)$$
DELIMITER ;

二.创建储存过程

2.1 无添加任务事务

DELIMITER $$
DROP PROCEDURE IF EXISTS `PROC_TRANSACTION_TEST`$$
CREATE PROCEDURE `PROC_TRANSACTION_TEST`()
BEGIN
 INSERT INTO SPE_TRANSACTION_TAB1 (`NAME`,AGE,CREATED) VALUES ( '张三111111111111张三张三张三张三' , 12,now()) ;
 INSERT INTO SPE_TRANSACTION_TAB2 (`NAME`,AGE,CREATED) VALUES ( '张三2' , 12,now() ) ;
END$$
DELIMITER ;

运行结果

> 1406 - Data too long for column 'NAME' at row 1

表中数据(都是空的)

mysql> select * FROM SPE_TRANSACTION_TAB1 ;
Empty set (0.00 sec)

mysql> select * FROM SPE_TRANSACTION_TAB2 ;
Empty set (0.00 sec)

2.2 无添加任务事务

DELIMITER $$
DROP PROCEDURE IF EXISTS `PROC_TRANSACTION_TEST`$$
CREATE PROCEDURE `PROC_TRANSACTION_TEST`()
BEGIN
 INSERT INTO SPE_TRANSACTION_TAB2 (`NAME`,AGE,CREATED) VALUES ( '张三2' , 12,now() ) ;
 INSERT INTO SPE_TRANSACTION_TAB1 (`NAME`,AGE,CREATED) VALUES ( '张三111111111111张三张三张三张三' , 12,now()) ;
END$$
DELIMITER ;

运行结果

> 1406 - Data too long for column 'NAME' at row 1

表中数据

Database changed
mysql> select * FROM SPE_TRANSACTION_TAB1 ;
Empty set (0.00 sec)

mysql> select * FROM SPE_TRANSACTION_TAB2 ;
+----+---------+------+---------------------+
| ID | NAME | AGE | CREATED |
+----+---------+------+---------------------+
| 1 | 张三2 | 12 | 2020-06-24 15:23:57 |
+----+---------+------+---------------------+

说明了Mysql 的整个存储过程不是一个原子操作 ! 我们需要为整个存储过程添加事务

2.3 添加事务

DELIMITER $$
DROP PROCEDURE IF EXISTS `PROC_TRANSACTION_TEST`$$
CREATE PROCEDURE `PROC_TRANSACTION_TEST`()
BEGIN
 start transaction; ##整个存储过程指定为一个事务 
 INSERT INTO SPE_TRANSACTION_TAB2 (`NAME`,AGE,CREATED) VALUES ( '张三2' , 12,now() ) ;
 INSERT INTO SPE_TRANSACTION_TAB1 (`NAME`,AGE,CREATED) VALUES ( '张三111111111111张三张三张三张三' , 12,now()) ;
 commit ;
END$$
DELIMITER ;

运行结果

> 1406 - Data too long for column 'NAME' at row 1

表中数据

mysql> select * FROM SPE_TRANSACTION_TAB1 ;
Empty set (0.00 sec)

mysql> select * FROM SPE_TRANSACTION_TAB2 ;
Empty set (0.00 sec)

2.4 MySQL的回滚事物的操作

在处理事务时，使用SQLException捕获SQL错误，然后处理在MySQL存储过程中捕获SQL错误，最后判断是回滚(ROLLBACK)还是提交(COMMIT)。

DELIMITER $$
DROP PROCEDURE IF EXISTS `PROC_TRANSACTION_TEST`$$
CREATE PROCEDURE `PROC_TRANSACTION_TEST`()
BEGIN
 DECLARE t_error INTEGER DEFAULT 0;    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; 
 start transaction; ##整个存储过程指定为一个事务 
 INSERT INTO SPE_TRANSACTION_TAB2 (`NAME`,AGE,CREATED) VALUES ( '张三2' , 12,now() ) ;
 INSERT INTO SPE_TRANSACTION_TAB1 (`NAME`,AGE,CREATED) VALUES ( '张三111111111111张三张三张三张三' , 12,now()) ;
 IF t_error = 1 THEN    
        ROLLBACK;    
    ELSE    
        COMMIT;    
    END IF;
    select t_error; ##返回标识位的结果集；
END$$
DELIMITER ;

运行结果

mysql> CALL PROC_TRANSACTION_TEST() ;
+---------+
| t_error |
+---------+
| 1 |
+---------+
1 row in set (0.01 sec)

表中数据

mysql> select * FROM SPE_TRANSACTION_TAB1 ;
Empty set (0.00 sec)

mysql> select * FROM SPE_TRANSACTION_TAB2 ;
Empty set (0.00 sec)