Two solutions for implementing MySQL nested transactions in PHP: mysql nested

Two solutions for implementing MySQL nested transactions in PHP: mysql nested

I. Problem Origin

MySQL official documentation clearly states that nested transactions are not supported:
Copy codeThe Code is as follows:
Transactions cannot be nested. This is a consequence of the implicit commit timed Med for any current transaction when you issue a start transaction statement or one of its synonyms.

However, when developing A complex system, we will inevitably inadvertently embed transactions in the transaction. For example, function A calls function B and function A uses transactions, in addition, the B function is called in the transaction, and the B function also has a transaction, so that transaction nesting occurs. At this time, the transaction of A is of little significance. Why? As mentioned in the above document, the simple translation is:
Copy codeThe Code is as follows:
When a start transaction command is executed, a commit operation is executed implicitly.

Therefore, we need to support the nesting of transactions at the system architecture level. Fortunately, nesting is supported in some mature ORM frameworks, such as doctrine or laravel. Next, let's take a look at how these two frameworks are implemented.

Friendly reminder: The names of functions and variables in these two frameworks are relatively intuitive. Although they look very long, they can be directly known through the naming, so don't be scared as soon as you see it :)

Ii. doctrine Solution

First, let's take a look at the code for creating a transaction in doctrine (irrelevant code is eliminated ):
Copy codeThe Code is as follows:
Public function beginTransaction ()
{
+ $ This-> _ transactionNestingLevel;
If ($ this-> _ transactionNestingLevel = 1 ){
$ This-> _ conn-> beginTransaction ();
} Else if ($ this-> _ nestTransactionsWithSavepoints ){
$ This-> createSavepoint ($ this-> _ getNestedTransactionSavePointName ());
}
}

The first line of this function uses a _ transactionNestingLevel to identify the current nested level. If it is 1, that is, it is not nested yet, then execute start transaction in the default method. If it is greater than 1, that is, when there is nesting, she will create a savepoint for us, this savepoint can be understood as a transaction record point. You can only roll back to this point when you need to roll back.

Then let's look at the rollBack function:
Copy codeThe Code is as follows:
Public function rollBack ()
{
If ($ this-> _ transactionNestingLevel = 0 ){
Throw ConnectionException: noActiveTransaction ();
}

If ($ this-> _ transactionNestingLevel = 1 ){
$ This-> _ transactionNestingLevel = 0;
$ This-> _ conn-> rollback ();
$ This-> _ isRollbackOnly = false;
} Else if ($ this-> _ nestTransactionsWithSavepoints ){
$ This-> rollbackSavepoint ($ this-> _ getNestedTransactionSavePointName ());
-- $ This-> _ transactionNestingLevel;
} Else {
$ This-> _ isRollbackOnly = true;
-- $ This-> _ transactionNestingLevel;
}
}

We can see that the processing method is also very simple. If the level is 1, roll back directly; otherwise, roll back to the previous savepoint.

Then let's continue to look at the commit function:
Copy codeThe Code is as follows:
Public function commit ()
{
If ($ this-> _ transactionNestingLevel = 0 ){
Throw ConnectionException: noActiveTransaction ();
}
If ($ this-> _ isRollbackOnly ){
Throw ConnectionException: commitFailedRollbackOnly ();
}

If ($ this-> _ transactionNestingLevel = 1 ){
$ This-> _ conn-> commit ();
} Else if ($ this-> _ nestTransactionsWithSavepoints ){
$ This-> releaseSavepoint ($ this-> _ getNestedTransactionSavePointName ());
}

-- $ This-> _ transactionNestingLevel;
}

Forget it. Let's just explain it :)

3. laravel Solution

Laravel's processing method is relatively simple and rough. Let's take a look at the operations for creating a transaction:
Copy codeThe Code is as follows:
Public function beginTransaction ()
{
++ $ This-> transactions;

If ($ this-> transactions = 1)
{
$ This-> pdo-> beginTransaction ();
}
}

How do you feel? So easy? First, judge how many transactions there are currently. If it is the first transaction, OK, the transaction starts. Otherwise, nothing will be done. Why is nothing done? Continue to the rollBack operation:
Copy codeThe Code is as follows:
Public function rollBack ()
{
If ($ this-> transactions = 1)
{
$ This-> transactions = 0;

$ This-> pdo-> rollBack ();
}
Else
{
-- $ This-> transactions;
}
}

Do you understand? The rollback is true only when the current transaction has only one transaction; otherwise, the count is only subtracted by one. This is why laravel's processing is relatively simple and crude. In the nested inner layer, there is actually a real transaction, and only the outermost layer is a whole transaction. Although it is simple and crude, but it also solves the commit problem when creating a transaction in the internal layer. The principle is like this. For the sake of completeness, copy the commit code!
Copy codeThe Code is as follows:
Public function commit ()
{
If ($ this-> transactions = 1) $ this-> pdo-> commit ();

-- $ This-> transactions;
}