Detailed example of how to implement transaction commit and rollback in mysql

Recently, we need to perform a scheduled migration of the database data. In order to prevent the SQL statement from reporting an error for some reason during the execution process, which may cause data transfer confusion, we need to control the transaction of our script.

First we create a tran_test table

CREATE TABLE tran_test( 
f1 VARCHAR(10) NOT NULL, 
f2 INT(1) DEFAULT NULL, 
PRIMARY KEY (f1) 
)ENGINE=INNODB CHARSET=utf8

I want to insert two pieces of data into tran_test, but in order to prevent errors during the insertion, I want to control the insert statement within a transaction.

At this time, if you check some people's articles, you will often be given such an answer.

START TRANSACTION;  
INSERT INTO tran_test VALUES('A',1); 
INSERT INTO tran_test VALUES('B',2); 
ROLLBACK;

or

START TRANSACTION;  
INSERT INTO tran_test VALUES('A',1); 
INSERT INTO tran_test VALUES('B',2); 
COMMIT;

These SQL statements look very simple, and they can actually commit or rollback.

But can this really achieve our goal? The answer is no.

For example, in the first paragraph, it will ROLLBACK all your SQL statements in the transaction regardless of whether they are correct or not. Such an absolute rollback makes your SQL meaningless.

Therefore, if we want to truly control transactions, my idea is to perform anomaly detection on the SQL to be executed. If there is no exception in the sql, COMMIT, if an exception is caught, ROLLBACK.

At this time, we need to build a stored procedure to capture the exception. COMMIT is performed when the execution is successful, and ROLLBACK is performed when the SQL execution fails.

There are two ways to achieve the effect I want.

The first one is to capture the exception of the SQL we want to execute. We define a variable t_error. When an exception is captured, let t_error=1. Then make a conditional judgment on t_error. If t_error=1, perform ROLLBACK, otherwise perform COMMIT.

DROP PROCEDURE IF EXISTS t_test; 
DELIMITER // 
CREATE PROCEDURE t_test() 
 BEGIN 
  DECLARE t_error INTEGER; 
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1; 
  START TRANSACTION; 
     INSERT INTO tran_test VALUES('A',1); 
   INSERT INTO tran_test VALUES('B',2); 
     IF t_error = 1 THEN 
       ROLLBACK; 
     ELSE 
       COMMIT; 
     END IF; 
END// 
CALL t_test();

The other is a simplification of the first one, that is, if an exception is caught, ROLLBACK is performed directly, and if no exception is caught, COMMIT is performed directly

DROP PROCEDURE IF EXISTS t_test; 
DELIMITER // 
CREATE PROCEDURE t_test() 
BEGIN 
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; 
START TRANSACTION; 
INSERT INTO tran_test VALUES('A',1); 
INSERT INTO tran_test VALUES('B',2); 
COMMIT; 
END// 
CALL t_test()

In this way, the two insert statements are truly controlled within one transaction.

You can test the above examples this time. If you have other supplements and questions, you can contact the editor directly. Thank you for your support of 123WORDPRESS.COM.