MySQL transaction programming performance and problem analysis [Must-see for developers]

No nonsense, full of useful information, go directly to the analysis:

1. Issues submitted in the loop

Many developers like to submit transactions in the loop, as shown below An example of a stored procedure they often write is as follows:

DROP PROCEDURE IF EXISTS load1;
CREATE PROCEDURE load1(count INT UNSIGNED)BEGIN   
DECLARE s INT UNSIGNED DEFAULT 1;   
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);   
WHILE s <= count DO      
INSERT INTO t1 select NULL,c;      
COMMIT;      
SET s=s+1;   
END WHILE;
END;

In the above example, whether to add the commit command is not critical. Since the storage engine of MySQL innodb defaults to automatic submission, the result of removing the commit in the stored procedure is the same. As shown below, the following is another problem that is easily overlooked by developers:

DROP PROCEDURE IF EXISTS load2;
 CREATE PROCEDURE load2(count INT UNSIGNED)BEGIN  
  DECLARE s INT UNSIGNED DEFAULT 1;   
  DECLARE c CHAR(80) DEFAULT REPEAT('a',80);   
  WHILE s <= count DO      
  INSERT INTO t1 select NULL,c;      
  SET s=s+1;   
  END WHILE;
  END;

Regardless of which stored procedure above, when an error occurs, the database will stay at an unknown location. For example, we want to insert 10,000 pieces of data, but an error occurred when inserting 5,000 pieces. However, these 5,000 pieces have already been stored in the database. How should we deal with it? The other is a performance issue. The two stored procedures above will not be faster than the stored procedure below, because the following one puts insert in a transaction:

DROP PROCEDURE IF EXISTS load3; 
CREATE PROCEDURE load3(count INT UNSIGNED)BEGIN   
DECLARE s INT UNSIGNED DEFAULT 1;   
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);   
START TRANSACTION;   
WHILE s <= count DO      
INSERT INTO t1 select NULL,c;      
SET s=s+1;   
END WHILE;   
COMMIT;
END;

For the above three stored procedures, We insert 1 million data respectively to compare the execution time, as shown below. It can be obviously seen that the third method is much faster. This is because a redo log must be written for each mention, so load1 and load2 actually write 100 Thousands of redo logs. For the stored procedure load3, we only wrote the redo log once.

Prepare a test table first

CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT ,`name` varchar(500) NULL ,PRIMARY KEY (`id`)) ;

Execute the test

09:50:44 test> call load1(1000000);
Query OK, 0 rows affected (1 min 4.90 sec)09:54:23 test> truncate table t1;
Query OK, 0 rows affected (0.05 sec)09:54:25 test> call load2(1000000);
Query OK, 1 row affected (1 min 3.38 sec)09:55:32 test> truncate table t1;
Query OK, 0 rows affected (0.20 sec)09:55:58 test> call load3(1000000);
Query OK, 0 rows affected (33.90 sec)

For the second stored procedure load2, we can also manually open the transaction, and the same can be achieved with the stored procedure load3 The effect and execution time are as follows:

09:57:42 test> begin;
Query OK, 0 rows affected (0.00 sec)09:57:46 test> call load2(1000000);
Query OK, 1 row affected (34.08 sec)09:58:26 test> commit;
Query OK, 0 rows affected (0.76 sec)

2. About using automatic submission

In some special scenarios, sometimes automatic submission is not necessarily a good thing, as we mentioned above Regarding the problem of circular submission, the MySQL database defaults to automatic submission (autocommit). You can change MySQL's submission method in the following ways:

10:35:34 test> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

You can also use START TRANSATION or BEGIN to start a transaction explicitly. MySQL will automatically execute

SET AUTOCOMMIT=0, and execute SET AUTOCOMMIT=1 after COMMIT or ROLLBACK ends a transaction.

3. Use automatic rollback to handle exceptions

What to do when an exception occurs in a stored process? The Innodb storage engine supports automatic rollback of transactions through a HANDLER. If an error occurs during the storage process, the rollback operation will be automatically performed. As an example below:

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
   START TRANSACTION;
   INSERT INTO b select 1;
   INSERT INTO b select 2;
   INSERT INTO b select 1;
   INSERT INTO b select 3;
   COMMIT;

END;

The test table is as follows

CREATE TABLE `b` (  
 `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Execute the above stored procedure, so an error will occur when inserting the second record 1, but because the automatic rollback operation is enabled, The execution result of this stored procedure is as follows:

10:09:46 test> call sp_auto_rollback_demo;
Query OK, 0 rows affected (0.01 sec)
10:10:04 test> select * from b;Empty set (0.00 sec)

It seems that there is no problem and the operation is relatively normal, but when executing sp_auto_rollback_demo, did the execution succeed or fail? We can handle this as follows. The example is as follows:

DROP PROCEDURE IF EXISTS sp_auto_rollback_demo;
CREATE PROCEDURE sp_auto_rollback_demo()BEGIN   
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT -1; 
END;   
START TRANSACTION;   
INSERT INTO b select 1;   
INSERT INTO b select 2;   
INSERT INTO b select 1;   
INSERT INTO b select 3;   
COMMIT;   
SELECT 1;
END;

When an error occurs, roll back first and then return -1, indicating that an error occurred during operation. Returning 1 indicates normal operation. The running results are as follows:

10:16:19 test> call sp_auto_rollback_demo\G*************************** 1. row ***************************-1: -1
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
10:16:35 test> select * from b;
Empty set (0.00 sec)

The above is the content of MySQL transaction programming performance and problem analysis [Must-see for development]. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!