Does MySQL support transaction nesting?

Recently, I encountered the problem of data confusion when using MySQL and opening transactions multiple times. The pseudo code is as follows:

begin;
# 操作1
begin;
# 操作2
rollback;

Operation 1# occurred after execution. The data of ## is actually written, and only the data of operation 2 of is rolled back. When the first transaction is not committed or rolled back, when the second transaction is started, the first transaction will be automatically committed.

This is obviously not in line with psychological expectations, and it is impossible to roll back part of the operation. So here comes the question,

Does MySQL support transaction nesting?

This question is difficult to answer accurately whether it is supported or not!

First of all, calling begin multiple times will definitely not allow transaction nesting in MySQL. After being reminded by a friend in the group, I learned that there is a statement 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;

The final execution result is that there is only 111 data in the test table, which realizes the rollback operation of some operations. In the same way, it also avoids the problem of starting a transaction multiple times, causing the previous transaction to be committed.

Maybe the savepoint and rollback to statements cannot be called transaction nesting, and it cannot be said whether MySQL supports or does not support transaction nesting. In short, savepoint and rollback to can be used to achieve some transaction nesting features.

The above is the detailed content of Does MySQL support transaction nesting?. For more information, please follow other related articles on the PHP Chinese website!