Technology sharing | nested transaction, autonomous transaction and chained transaction of MySQL

Time:2020-10-4

Author: Yang Taotao

This article is inspired by the recent support for a customer to migrate from Oracle to MySQL. Next weDetailed description of transaction types in MySQL

classification

1. General affairs

A transaction that starts with begin / start transaction and ends with commit / rollback. Or a transaction with a savepoint.

2. Chain transaction

When a transaction is committed, the context is automatically passed to the next transaction. That is to say, the commit of a transaction and the start of the next transaction are atomic. The next transaction can see the processing results of the previous transaction. MySQL chain transaction depends on the parameter completion_ Type control, and the work keyword is added after the rollback and commit statements.

Nested transaction 3

A transaction with multiple transaction blocks such as begin / commit / rollback and has a parent-child relationship. After the child transaction is committed, it will not commit, but wait for the parent transaction to commit.

4. Autonomy

The commit of internal transaction is not affected by external transaction, and is generally used to record the exception of internal transaction. MySQL does not support autonomous transactions, but some scenarios can be implemented in disguise with MySQL plug-in engine.

Next, we illustrate each transaction with detailed examples

example

1. General affairs

Table C1 below starts a transaction block with two save points S1 & S2. We roll back all operations after S2 and commit all operations before S2. At this time, S1 & S2 is invalid. That’s exactly two records.

{"db":"ytt"},"port":"3320"}-mysql>truncate c1;
Query OK, 0 rows affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>
{"db":"ytt"},"port":"3320"}-mysql>
{"db":"ytt"},"port":"3320"}-mysql>use ytt
Database changed
{"db":"ytt"},"port":"3320"}-mysql>begin;
Query OK, 0 rows affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>insert into c1 values (1,20,now());
Query OK, 1 row affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>savepoint s1;
Query OK, 0 rows affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>insert into c1 values (2,30,now());
Query OK, 1 row affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>savepoint s2;
Query OK, 0 rows affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>insert into c1 values (3,40,now());
Query OK, 1 row affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>rollback to savepoint s2;
Query OK, 0 rows affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>commit;
Query OK, 0 rows affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>select * from c1;
+----+------+---------------------+
| id | c1   | c2                  |
+----+------+---------------------+
|  1 |   20 | 2019-12-02 10:07:02 |
|  2 |   30 | 2019-12-02 10:07:12 |
+----+------+---------------------+
2 rows in set (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>

2. Chain transaction

Set completion_ Type = 1, that is, the chained transaction feature is enabled. In the following example, the statement after commit work is an implicit transaction statement. In other words, after the statement rollback statement is executed, SQL 2 must have been submitted by default. However, since the context is inherited, that is, the statement SQL 2 becomesbegin; SQL 2;At this time, SQL 2 and the rollback statement are actually a transaction block. The end result is only two records.

{"db":"ytt"},"port":"3320"}-mysql>truncate table c1;
Query OK, 0 rows affected (0.01 sec)

{"db":"ytt"},"port":"3320"}-mysql>set completion_type=1;
Query OK, 0 rows affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>insert into c1 values (4,50,now());
Query OK, 1 row affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>insert into c1 values (5,60,now());
Query OK, 1 row affected (0.00 sec)

-- sql 1 
{"db":"ytt"},"port":"3320"}-mysql>commit work;
Query OK, 0 rows affected (0.00 sec)

-- sql 2
{"db":"ytt"},"port":"3320"}-mysql>insert into c1 values (6,70,now());
Query OK, 1 row affected (0.00 sec)

-- sql 3
{"db":"ytt"},"port":"3320"}-mysql>rollback;
Query OK, 0 rows affected (0.01 sec)

{"db":"ytt"},"port":"3320"}-mysql>select * from c1;
+----+------+---------------------+
| id | c1   | c2                  |
+----+------+---------------------+
|  4 |   50 | 2019-12-02 10:14:16 |
|  5 |   60 | 2019-12-02 10:14:31 |
+----+------+---------------------+
2 rows in set (0.00 sec)

3. Nested transaction

In fact, strictly speaking, MySQL does not support nested transactions. The start of each transaction block in MySQL will commit the previous transaction by default. For example, in the following example, the second begin statement changes tocommit;begin;The rollback after that actually rolled back only one record. The final number of records is id = 7.

{"db":"ytt"},"port":"3320"}-mysql>truncate table c1;
Query OK, 0 rows affected (0.01 sec)

{"db":"ytt"},"port":"3320"}-mysql>begin;
Query OK, 0 rows affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>insert into c1 values (7,80,now());
Query OK, 1 row affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>begin;
Query OK, 0 rows affected (0.01 sec)

{"db":"ytt"},"port":"3320"}-mysql>insert into c1 values (8,90,now());
Query OK, 1 row affected (0.00 sec)

{"db":"ytt"},"port":"3320"}-mysql>rollback;
Query OK, 0 rows affected (0.01 sec)

{"db":"ytt"},"port":"3320"}-mysql>select * from c1;
+----+------+---------------------+
| id | c1   | c2                  |
+----+------+---------------------+
|  7 |   80 | 2019-12-02 10:24:44 |
+----+------+---------------------+
1 row in set (0.00 sec)

4. Autonomous affairs

In fact, MySQL does not support autonomous transactions, but based on MySQL’s inborn pluggable architecture, autonomous transactions can also be implemented in disguise. For example, you can change a log table into a non transaction engine table, such as MyISAM.

{"db":"(none)"},"port":"3326"}-mysql>use ytt
Database changed
{"db":"ytt"},"port":"3326"}-mysql>create table log(err_msg varchar(200))engine myisam;
Query OK, 0 rows affected (0.01 sec)

{"db":"ytt"},"port":"3326"}-mysql>begin;
Query OK, 0 rows affected (0.00 sec)

{"db":"ytt"},"port":"3326"}-mysql>insert into t1 values (100);
Query OK, 1 row affected (0.01 sec)

{"DB": "YTT"}, "port": "3326"} - MySQL > insert into log values ('This record should not be inserted ');
Query OK, 1 row affected (0.00 sec)

{"db":"ytt"},"port":"3326"}-mysql>select * from t1;
+------+
| id   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

{"db":"ytt"},"port":"3326"}-mysql>rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)

{"db":"ytt"},"port":"3326"}-mysql>select * from log;
+-----------------------------------+
| err_msg                           |
+-----------------------------------+
|This record should not be inserted|
+-----------------------------------+
1 row in set (0.00 sec)

summary

This paper mainly introduces the transaction categories of MySQL, and makes a simple SQL demonstration for several scenarios of daily use, hoping to help you.