How to realize transaction nesting in MySQL Database

This article shows you how to achieve transaction nesting in the MySQL database, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Solution method

At present, there are two general solutions in the PHP circle, one is to set the rollback point represented by Doctrine, and the other is to control the number of transactions represented by Laravel.

The solution of Doctrine

The core of the Doctrine solution is to control the rollback point, as follows:

The method of opening transaction in Doctrine

The method of transaction rollback in Doctrine

The method of transaction commit in Doctrine

Doctrine uses a _ transactionNestingLevel to identify the current level of nesting. If it is 1, which means there is no nesting, then ok will be executed with the default method of START TRANSACTION; if it is greater than 1, that is, when there is nesting, it will help us create a savepoint. This savepoint can be understood as a transaction record point, and we can only roll back to this point when we need to roll back.

The solution of Laravel

Compared to Doctrine, Laravel's solution is somewhat simple and crude, and it skillfully uses a transactions attribute to record the number of times a transaction is called. When the transaction starts, the transaction commits and the transaction rolls back, the attribute value of transactions is judged first, and the transaction operation is performed only when the attribute value of transactions is 1. As follows:

When starting a transaction, we first judge how many transactions there are currently. If there are * * ok, the transaction starts, otherwise we will do nothing.

When the transaction commits, the current number of transactions is also judged. If there are * ok, commit transactions, otherwise, only the value of the transactions attribute is reduced by one.

When a transaction is rolled back, the current number of transactions is also judged. If there are * * ok, roll back transactions, and set the value of the transactions attribute to 0, otherwise, only reduce the value of the transactions attribute by one.

In Laravel's solution, there is actually no real transaction in the nested inner layer, and there is only the outermost whole transaction, which is simple and rough, but it also solves the problem that commit will be caused when a new transaction is created in the inner layer.

The above is how to achieve transaction nesting in MySQL database. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.