The most complete and detailed explanation of MySQL transactions

What is a transaction?

What is the official MySQL sentence to describe a transaction? MySQL transactions are mainly used to process data with large operations and high complexity. So why is the amount of data so large? Why is complexity high? Let me describe it using my own understanding. Transaction is actually a way of processing data in MySQL. It is mainly used when data integrity is high and the dependence between data is large. For example, Xiao Zhang transferred 200 yuan to Xiao Li's bank card. When Xiao Zhang clicked the button to confirm the transfer, the system suddenly crashed. There will be several incorrect situations like this:

1. Xiao Zhang’s money was transferred to Xiao Li’s account, but the money in his own account was not deducted.

2. Zhang's money was not transferred to Xiao Li's account, but the money in his own account was deducted.

Such a business scenario requires MySQL transaction maintenance. Even if the machine fails, the data is still correct. .

Conditions for using transactions

MySQL To use transactions, the storage engine support in MySQL is required. Currently, MySQL's built-in storage engines that support transactions include InnoDB and NDB cluster, and third-party storage engines include PBXT and XtrDB.

What are the characteristics of transactions?

Transactions in MySQL have the following characteristics (ACID):

Atomicity:

A transaction must be As an indivisible minimum unit of work, all operations in each transaction must either succeed or fail. It is never possible for some operations to fail and some operations to succeed. This is the so-called concept of atomicity.

Consistency:

Consistency is just like the example above. When an exception occurs, the data is still correct. That is to say, when a transaction fails to execute, the data will not be affected by abnormal situations and will always maintain its correctness.

Isolation:

When a transaction has not yet been committed, each transaction is isolated from each other and complementarity is affected.

Durability (durability):

When After a transaction is submitted, the changes will be stored in the database forever.

Isolation level of transaction

When talking about the isolation characteristics of MySQL, we have to talk about isolation. Several levels of sex. As for why this is involved, it can be understood simply like this: If there are two requests performing transaction operations at the same time, and these two transactions operate on the same piece of data, then whose final result is based on whose Which shall prevail? Different isolation levels lead to different results, so the isolation level of the transaction is also a very important point.

Isolation levels are divided into the following points:

1. Uncommitted read (READ UNCOMMITTED)

Modifications made to data in one transaction, even if not committed, are still visible to other transactions. In this case, dirty reads are prone to occur, affecting Data integrity.

For example: When Xiao Ming paid with Alipay, he checked that the balance of his bank card still had 300 yuan. In fact, it was only 100 yuan. It was just because his girlfriend was depositing 200 yuan into the bank card. At this time, the girlfriend did not want to save, so she clicked the rollback operation, but Xiao Ming failed to pay.

2. READ COMMITTED

A transaction begins , only other transactions that have been submitted can be seen. In this case, non-repeatable reading is easy to occur (the results of the two readings are different).

Example: Using the same example above, when his girlfriend swiped the card, the balance in the card was 100 yuan, but after When I clicked on the final payment, it was prompted that the balance was insufficient. At this time, the money in the card was gone. This is because when Xiao Ming’s girlfriend made the payment, the transaction operated by Xiao Ming had not yet been submitted, so the results Xiao Ming’s girlfriend saw twice were different.

3. REPEATABLE READ

##The results of reading records multiple times are consistent. Repeatable reading can solve the above non-repeatable reading situation. But there is a situation where when a transaction is reading records in a certain range, another transaction inserts a new piece of data in this range. When the transaction reads the data again, it is found that the data is larger than the first read. There is one more record. This is the so-called phantom reading. The results of the two reads are inconsistent.

For example: When Xiao Ming’s girlfriend checked the bank card records, she saw 5 consumption records. At this time, Xiao Ming was Consumption, this consumption record was recorded in the consumption record. When the girlfriend read the record again, she found that there were 6 records.

4. SERIALIZABLE

Serial is like a queue. Each transaction is queued and waiting for execution. Only after the previous transaction is submitted, the next transaction can be operated. Although this situation can solve the above phantom reading, it will add a lock to each piece of data, which can easily lead to a large number of lock timeouts and lock competition. It is especially not suitable for some high-concurrency business scenarios.

Example: We queue up to deposit money at the bank. Only when the previous person has completed all operations can the next person proceed. People in the middle are not allowed to jump in the queue, they can only line up one by one. The serialization of transactions is such a concept. In fact, the so-called serial mode is such a concept.

Isolation summary

Through the above examples, it is not difficult for us to find out. Dirty reads and non-repeatable reads focus on updating data, and phantom reads focus on inserting data.

Transaction processing methods for multiple storage engines

According to the above transaction usage conditions, we can get It is known that some storage engines do not support transactions, such as the MyISAM storage engine. If a transactional storage engine and a non-transactional storage are used in a transaction, the commit can proceed normally, but rolling back the non-transactional storage engine will display a response error message. The specific information is related to the storage engine.

How to use transactions

Transactions in MySQL are enabled implicitly, that is to say, a sql statement is a transaction. When the sql statement is executed, the transaction is submitted. . During the demonstration, we explicitly turned on.

Auto-commit in MySQL

As mentioned above, transactions in MySQL are implicitly turned on, which means that every time we A sql is automatically submitted. If you need to close it, you need to set the autocommit option.

// 查看autocommit配置值（1或者ON则表示开启）
mysql root@127.0.0.1:(none)> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set
Time: 0.018s
// 设置autocommit配置值
mysql root@127.0.0.1:(none)> set autocommit = 0;
Query OK, 0 rows affected
Time: 0.000s
mysql root@127.0.0.1:(none)> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set
Time: 0.013s

1. The table structure is as follows

mysql root@127.0.0.1:test> desc user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | <null> | auto_increment |
| name | varchar(255) | YES | | <null> | |
| age | int(2) | YES | | <null> | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set
Time: 0.013s

SQL statement

CREATE TABLE `test`.`Untitled` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(2) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2. Use transactions

MySQL implements transactions

In the code below, we mainly do the following operations

a. Open the transaction

b. Modify the data

c. Query whether the data has changed

d. Data rollback

e. Query the data again and find that the data has changed back to the state before modification

f . Modify data

g. Transaction submission

h. Query the data and find that the data has changed to the last modified state

i. Try transaction rollback

j. Query to verify whether it has been rolled back, and it is found that the data is still in the last modified state, and the transaction rollback failed

// 我们先查看表中的数据,id为1的age字段是12
mysql root@127.0.0.1:test> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 张三 | 12 |
| 2 | 李四 | 15 |
+----+------+-----+
2 rows in set
Time: 0.013s
// 开启事务
mysql root@127.0.0.1:test> begin;
Query OK, 0 rows affected
Time: 0.001s
// 将id为1的age字段改为10
mysql root@127.0.0.1:test> update user set age=10 where id=1;
Query OK, 1 row affected
Time: 0.001s
// 再次查询数据时,发现数据改为修改后的值
mysql root@127.0.0.1:test> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 张三 | 10 |
| 2 | 李四 | 15 |
+----+------+-----+
2 rows in set
Time: 0.012s
// 此时我们进行回滚操作
mysql root@127.0.0.1:test> rollback;
Query OK, 0 rows affected
Time: 0.001s
// 再次查询发现数据回到最初状态
mysql root@127.0.0.1:test> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 张三 | 12 |
| 2 | 李四 | 15 |
+----+------+-----+
2 rows in set
Time: 0.019s
// 我们再次对数据进行修改
mysql root@127.0.0.1:test> update user set age=15 where id=1;
Query OK, 1 row affected
Time: 0.001s
// 此时将事务进行提交
mysql root@127.0.0.1:test> commit;
Query OK, 0 rows affected
Time: 0.000s
// 发现此时的数据变为我们最终提交的值
mysql root@127.0.0.1:test> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 张三 | 15 |
| 2 | 李四 | 15 |
+----+------+-----+
2 rows in set
Time: 0.012s
// 我们尝试用刚才回滚的方式进行还原数据
mysql root@127.0.0.1:test> rollback;
Query OK, 0 rows affected
Time: 0.000s
// 发现数据无法回退了,仍然是提交后的数据
mysql root@127.0.0.1:test> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 张三 | 15 |
| 2 | 李四 | 15 |
+----+------+-----+
2 rows in set
Time: 0.017s

PHP transaction implementation example code

<?php
// 连接MySQL
$mysqli = new mysqli(&#39;127.0.0.1&#39;, &#39;root&#39;, &#39;123456&#39;, &#39;test&#39;, 3306);
// 关闭事务自动提交
$mysqli->autocommit(false);
// 1.开启事务
$mysqli->begin_transaction();
// 2.修改数据
$mysqli->query("update user set age=10 where id=1");
// 3.查看数据
$mysqli->query("select * from user");
// 4.事务回滚
$mysqli->rollback();
// 5.查看数据
$mysqli->query("select * from user");
// 7.修改数据
$mysqli->query("update user set age=15 where id=1");
// 8.事务提交
$mysqli->commit();
// 9.事务回滚
$mysqli->rollback();
// 10.查看数据
$mysqli->query("select * from user");

How to set the isolation level of a transaction

// 查看当前的事务隔离级别
mysql root@127.0.0.1:test> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set
Time: 0.015s
// 设置隔离级别
set session transaction isolation level 隔离级别(上面事务隔离级别中的英文单词);

The above is the detailed content of The most complete and detailed explanation of MySQL transactions. For more information, please follow other related articles on the PHP Chinese website!