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

Source: Internet
Author: User
Tags savepoint

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;
}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.