A case study of whether MySQL supports transaction nesting

Does MySQL support case studies of transaction nesting? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Recently, we have encountered using MySQL to open transactions for many times, resulting in data confusion. The pseudo code is as follows:

Begin;# operation 1 beginscape # operate 2rollback

After the execution, the data of operation 1 is actually written, and only the data of operation 2 is rolled back. When the first transaction is not committed or rolled back, and the second transaction is opened, the first transaction is automatically committed.

This is obviously not in line with psychological expectations, and it is not possible to roll back some operations. So the question is, does MySQL support transaction nesting?

It is difficult to answer this question accurately. Yes or no!

First of all, if you call begin multiple times, it is certainly impossible to nest transactions first in MySQL. After a reminder from a friend in the group, I learned that there are sentences called savepoint and rollback to in MySQL.

Sample code:

DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; begin; insert into `test` (`name`) values ('111'); SAVEPOINT p1; insert into `test` (`name`) values (' 222'); ROLLBACK TO p1; commit

In the final execution result, there is only 111data in the test table, which realizes the rollback operation of part of the operation. In the same way, the problem of opening a transaction multiple times and causing the previous transaction to be committed is avoided.

Perhaps savepoint and rollback to statements cannot be called transaction nesting, nor can it be said that MySQL supports or does not support transaction nesting. In short, some transaction nesting features can be achieved through savepoint and rollback to.

Thank you for reading! After reading the above, do you have a general understanding of the case study on whether MySQL supports transaction nesting? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.