Shulou(Shulou.com)06/01 Report--

This article mainly introduces the most complete MySQL transaction details, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

What is a transaction described in the official sentence of MySQL? MySQL transactions are mainly used to deal with data with large amount of operations and high complexity. Then why is the amount of data large? What is high complexity? Let me describe it with my own understanding. Transaction is actually a way of dealing with data in MySQL, which is mainly used in the case of high data integrity and great dependence between data. For example, when Xiao Zhang transferred 200 yuan to Xiao Li's bank card, the system suddenly crashed when Xiao Zhang clicked the button to confirm the transfer. There are several incorrect situations:

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

two。 Xiao Zhang's money could not be transferred to Xiao Li's account, but the money in his own account was deducted.

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

Conditions for the use of transactions

To use transactions in MySQL, you need the storage engine support in MySQL. At present, the built-in storage engines of MySQL support transactions such as InnoDB and NDB cluster, while the third-party storage engines include PBXT and XtrDB.

What are the characteristics of the transaction?

Transactions in MySQL have the following characteristics (ACID):

Atomicity (atomicity):

A transaction must be regarded as an indivisible minimum unit of work, and all operations in each transaction must either succeed or fail. Some operations can never fail and some operations succeed. This is the so-called concept of atomicity.

Consistency (consistency):

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

Isolation (isolation):

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

Persistence (durability):

When a transaction commits, the changes are saved forever in the database.

Isolation level of the transaction

When talking about the isolation characteristics of MySQL, we have to talk about several levels of isolation. As to why this is involved, it can be simply understood as follows: if there are two requests performing the operation of a transaction at the same time, and the two transactions are operating on the same piece of data, then whose final result will prevail? Different isolation levels lead to different results, so the isolation level of transactions is also a very important point.

The isolation level is divided into the following:

1. Uncommitted read (READ UNCOMMITTED)

Even if the changes made to the data in a transaction are not committed, the changes are still visible to other transactions. In this case, dirty reads are easy to occur, affecting the integrity of the data.

For example: when Xiaoming paid with Alipay, he checked that the balance of the bank card was still 300 yuan, but in fact it was only 100 yuan, just because his girlfriend was making a deposit of 200 yuan to the bank card. At this time, his girlfriend didn't want to save it and clicked on the rollback operation. Xiaoming failed to pay.

two。 Read submit (READ COMMITTED)

At the beginning of a transaction, only other transactions that have been committed can be seen. In this case, unrepeatable readings are easy to occur (the results of the two readings are different).

Example: also use the example above, when his girlfriend swiped her card, the balance in the card was 100 yuan, but when she clicked on the final payment, the balance was not enough, and the money in the card was gone. This is because Xiaoming's girlfriend has not yet submitted the affairs operated by Xiaoming when she paid, so Xiaoming's girlfriend saw different results twice.

3. Repeatable read (REPEATABLE READ)

The results of multiple read records are consistent, and repeatable reading can solve the above non-repeatable reading situation. However, there is a situation in which when one transaction reads a range of records, another transaction inserts a new piece of data in that range, and when the transaction reads the data again, it is found that there is one more record than the first time. This is the so-called phantom reading, and the results of the two reads are inconsistent.

For example: when Xiao Ming's girlfriend looked at the bank card records, she saw 5 consumption records. Xiao Ming was spending at this time. At this time, the consumption records were recorded. When his girlfriend read the records again, she found that there were 6 records.

4. Serializable (SERIALIZABLE)

Serial is like a queue, each transaction is queued for execution, and only after the previous transaction is committed can the next transaction operate. Although this situation can solve the illusion above, it will add a lock to each piece of data, which can easily lead to a large number of lock timeouts and lock competition, which is especially not suitable for some high concurrency business scenarios.

For example: we line up to deposit money in the bank, only the previous person has finished all the operation, the next person can handle it. People in the middle can not jump the queue, can only line up one by one, the serial 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. Dirty reading and unrepeatable reading focus on updating data, and then phantom reading focuses on inserting data.

How to handle transactions in multiple storage engines

According to the conditions for the use of transactions above, we can see that some storage engines do not support transactions, such as the MyISAM storage engine. If the transactional storage engine and non-transactional storage are used in a transaction, the commit can be done normally, but the rollback of the non-transactional storage engine will display the error message of the response, which is related to the storage engine.

How to use transactions

Transactions are implicitly opened in MySQL, that is, a sql statement is a transaction, and when the sql statement is executed, the transaction commits. During the demonstration, we explicitly open it.

Autocommit in MySQL

As mentioned above, if the transaction in MySQL is implicitly opened, it means that every sql commits automatically. If you need to close it, you need to set the autocommit option.

/ / check the autocommit configuration value (1 or ON means enabled) mysql root@127.0.0.1: (none) > show variables like'% autocommit%' +-+-+ | Variable_name | Value | +-+-+ | autocommit | ON | +-+-+ 1 row in setTime: 0.018s// setting autocommit configuration value mysql root@127.0.0.1: (none) > set autocommit = 0 Query OK, 0 rows affectedTime: 0.000smysql root@127.0.0.1: (none) > show variables like'% autocommit%';+-+-+ | Variable_name | Value | +-+ | autocommit | OFF | +-+-+ 1 row in setTime: 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 | | auto_increment | | name | varchar | YES | age | int (2) | YES | +-+-+ 3 rows in setTime: 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

two。 Use transaction

MySQL implements transaction

In the following code, we mainly do the following operations

a. Open a transaction

b. Modify 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 its pre-modified state.

f. Modify data

g. Transaction commit

h. Query the data and find that the data is in the state of the last modification

i. Attempt transaction rollback

j. The query verifies whether it has been rolled back, and the data is found to be in the state of the last modification. The transaction rollback failed.

/ / Let's first look at the data in the table. The age field with id 1 is 12mysql root@127.0.0.1:test > select * from user. +-+ | id | name | age | +-+ | 1 | Zhang San | 12 | 2 | Li Si | 15 | +-+ 2 rows in setTime: 0.013s// starts transaction mysql root@127.0.0.1:test > begin Query OK, 0 rows affectedTime: 0.001s// changes the age field with id 1 to 10mysql root@127.0.0.1:test > update user set age=10 where id=1;Query OK, and when 1 row affectedTime: 0.001s// queries the data again, it is found that the data is changed to the modified value mysql root@127.0.0.1:test > select * from user +-+ | id | name | age | +-+ | 1 | Zhang San | 10 | 2 | Li Si | 15 | +-+ 2 rows in setTime: 0.012s// at this time we roll back mysql root@127.0.0.1:test > rollback Query OK, 0 rows affectedTime: 0.001s// queries again and finds that the data returns to its original state mysql root@127.0.0.1:test > select * from user +-+ | id | name | age | +-+ | 1 | Zhang San | 12 | 2 | Li Si | 15 | +-+ 2 rows in setTime: 0.019s// We modify the data again mysql root@127.0.0.1:test > update user set age=15 where id=1 Query OK, 1 row affectedTime: 0.001s// commits the transaction at this time mysql root@127.0.0.1:test > commit;Query OK, 0 rows affectedTime: 0.000s// finds that the data at this time becomes our final commit value mysql root@127.0.0.1:test > select * from user +-+ | id | name | age | +-+ | 1 | Zhang San | 15 | 2 | Li Si | 15 | +-+ 2 rows in setTime: 0.012s// We try to restore data in the way we just rolled back mysql root@127.0.0.1:test > rollback Query OK, 0 rows affectedTime: 0.000s// found that the data cannot be returned, but it is still the submitted data mysql root@127.0.0.1:test > select * from user;+----+ | id | name | age | +-- + | 1 | Zhang San | 15 | 2 | Li Si | 15 | +-+ 2 rows in setTime: 0.017s

PHP implementation transaction instance code

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.