How to implement MySQL nested transactions in PHP

This article mainly introduces two solutions for implementing MySQL nested transactions in PHP. This article analyzes the implementation methods of doctrine and laravel, and extracts them for analysis and summary. Friends in need can refer to it

1. Origin of the problem

In the official documentation of MySQL, it is clearly stated that nested transactions are not supported:

Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

But it is inevitable when we develop a complex system Transactions may be nested in transactions unintentionally. For example, function A calls function B, function A uses a transaction, and calls function B within a transaction. Function B also has a transaction, so transaction nesting occurs. At this time, A's affairs are actually of little significance. Why? It is mentioned in the above document. A simple translation is:

当执行一个START TRANSACTION指令时，会隐式的执行一个commit操作。

So we need to support the nesting of transactions at the system architecture level. Fortunately, some mature ORM frameworks have support for nesting, such as doctrine or laravel. Next, let’s take a look at how these two frameworks are implemented.

Friendly reminder, the naming of functions and variables in these two frameworks is relatively intuitive. Although it looks very long, you can directly know the meaning of the function or variable through naming, so don’t I was scared when I saw such a big mess :)

2. Doctrine’s solution

First let’s take a look at the code to create a transaction in doctrine (dirty) Unrelated code):

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 nesting level. If it is 1, that is, there is no nesting yet, then use the default method to execute it. START TRANSACTION is ok. 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 recording point. When rollback is needed, we can only roll back to this point.

Then look at the rollBack function:

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

You can see that the processing method is also very simple. If the level is 1, rollback directly, otherwise rollback to the previous savepoint.

Then let’s continue to look at the commit function:

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 explain this paragraph without any hassle:)

3. Laravel’s solution

Laravel's processing method is relatively simple and crude. Let's first look at the operation of creating a transaction:

public function beginTransaction()
{
    ++$this->transactions;
    if ($this->transactions == 1)
    {
        $this->pdo->beginTransaction();
    }
}

How do you feel? So easy, right? First determine how many transactions there are currently. If it is the first one, ok, the transaction starts. Otherwise, nothing is done. So why is nothing done? Continue to look at the operation of rollBack:

public function rollBack()
{
    if ($this->transactions == 1)
    {
        $this->transactions = 0;
        $this->pdo->rollBack();
    }
    else
    {
        --$this->transactions;
    }
}

Do you understand? Only when there is only one current transaction will it be truly rolled back, otherwise it will just decrement the count by one. This is why I just said that Laravel's processing is relatively simple and crude. There are actually no real transactions in the nested inner layer. There is only an overall transaction in the outermost layer. Although it is simple and crude, it also solves the problem of When the inner layer creates a new transaction, it will cause commit problems. The principle is like this. For the sake of completeness, please copy the commit code too!

public function commit()
{
    if ($this->transactions == 1) $this->pdo->commit();
    --$this->transactions;
}

The above is the entire content of this article. I hope it will be helpful to everyone's study. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

How to use the mb_detect_encoding function in PHP

##How to use smtp sending support in PHP Attached email

#How to implement supervisor execution asynchronous process in Laravel framework in PHP

The above is the detailed content of How to implement MySQL nested transactions in PHP. For more information, please follow other related articles on the PHP Chinese website!