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

In this issue, the editor will bring you about how to realize the rollback of things in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

The concept of things:

A thing consists of one or more sql statements of a single unit. If one of the statements cannot be completed, the whole unit will be rolled back and all affected data will be returned to the state before the start of the thing. Therefore, only if all the statements in the thing are executed successfully can it be proved that the thing is executed successfully.

There is nothing wrong with the sentence itself, but take a look at the following example

There are two insert statements in the mysql transaction, the second of which is wrong, and the first is still inserted after running the transaction, as shown in the following code.

/ / create table: CREATE TABLE `test_ tab` (`f1` int (11) NOT NULL, `f2` varchar (11) DEFAULT NULL, PRIMARY KEY (`f1`)) ENGINE=InnoDB

/ / execute transaction: START TRANSACTION;INSERT INTO test_tab VALUES (1,'2'); INSERT INTO test_tab VALUES (1,'3'); COMMIT

At first, I thought that as long as the transaction was written out and finally submitted with commit, the database would automatically judge whether all these statements were executed successfully, if successful, all the data would be inserted into the database, and if there was a failure, it would be automatically rolled back to the original state! Obviously, I think it's wrong.

[SQL] START TRANSACTION; affected lines: 0 time: 0.000s

[SQL] INSERT INTO test_tab VALUES (1,'2'); affected rows: 1 time: 0.001s

[SQL] INSERT INTO test_tab VALUES (1,'3'); [Err] 1062-Duplicate entry'1' for key 'PRIMARY'

We can see that INSERT INTO test_tab VALUES (1,'3'); this sentence failed to run because of a primary key conflict, so the commit below this one is not executed either.

It should be noted that a transaction has been opened and a correct insert statement has been executed, although it is not reflected in the database, but if a commit or begin or start transaction is executed later in the connection (opening a new transaction will commit other uncommitted transactions in the link, which is equivalent to commit! ) you will find that the INSERT INTO test_tab VALUES (1,'2'); has been written into the database.

This is how to realize the rollback of things in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.