• notice
  • Congratulations on the launch of the Sought Tech site

Two solutions to implement mysql nested transactions in PHP

First, the origin of the problem

It is clearly stated in the official MySQL document that nested transactions are not supported:

The code is as follows:

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.

However, when we develop a complex system, we will inevitably nest transactions in transactions.For example, function A calls function B, function A uses transaction, and function B is called in transaction, and function B also has a transaction., So there is transaction nesting.In fact, A’s affairs are of little significance at this time.Why? As mentioned in the above document, a simple translation is:
The code is as follows:

When a START TRANSACTION instruction is executed, a commit operation is implicitly executed.

So we have 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 naming of functions and variables in these two frameworks is relatively intuitive.Although it looks very long, you can directly know the meaning of this function or variable through naming, so don’t see it.I was scared after a big tuft:)

Second, doctrine's solution

Let’s first look at the code to create a transaction in doctrine (get rid of irrelevant code):

The 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 nesting level.If it is 1, that is, there is no nesting yet, then use the default method to execute START TRANSACTION.When nesting, she will help us create a savepoint, this savepoint can be understood as a transaction record point, when you need to roll back, you can only roll back to this point.

Then look at the rollBack function:

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


It can be seen that the processing method is also very simple.If the level is 1, directly rollback, otherwise it will roll back to the previous savepoint.

Then we continue to look at the commit function:

The 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, don't bother to explain this paragraph :)

Three.Laravel's solution

laravel's processing method is relatively simple and rude, let's first look at the operation of creating a transaction:

The code is as follows:

public function beginTransaction()
{
    ++$this->transactions;

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


How does it feel? so easy, right? First judge how many transactions currently exist.If it is the first one, ok, the transaction starts, otherwise it will not do anything, so why not do it? Continue to look down at the operation of rollBack:
The code is as follows:

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

        $this->pdo->rollBack();
    }
    else
    {
       --$this->transactions;
    }
}


Understand? Only when there is only one current transaction, will the real rollback be true, otherwise it will only decrease the count by one.This is why I just said that the processing of laravel is relatively simple and rude.In the nested inner layer, there are actually no real transactions.There is only the outermost overall transaction.Although simple and rude, it also solves the problem.When a new transaction is created in the inner layer, it will cause a commit problem.The principle is like this, for the sake of integrity, copy the commit code too!
The code is as follows:

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

   --$this->transactions;
}

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+