MySQL nested transaction implementation

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 when we develop a complex system, it is inevitable that we will inadvertently Transactions are nested in transactions. For example, function A calls function B. Function A uses a transaction and calls function B within the 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:

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

So we need to support transaction nesting 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 (removed irrelevant code):

publicfunctionbeginTransaction(){
    ++$this->_transactionNestingLevel;    
    if ($this->_transactionNestingLevel == 1) {        
    $this->_conn->beginTransaction();
    } elseif ($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 and it will be ok. If it is greater than 1, that is, when there is nesting, she will help us create a savepoint. This savepoint can be understood as a transaction recording point. When rollback is needed, you can only roll back to this point.

Then look at the rollBack function:

publicfunctionrollBack(){    
if ($this->_transactionNestingLevel == 0) {        
throw ConnectionException::noActiveTransaction();
    }    
    if ($this->_transactionNestingLevel == 1) {        
    $this->_transactionNestingLevel = 0;        
    $this->_conn->rollback();        
    $this->_isRollbackOnly = false;
    } elseif ($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:

publicfunctioncommit(){    
if ($this->_transactionNestingLevel == 0) {        
throw ConnectionException::noActiveTransaction();
    }    if ($this->_isRollbackOnly) {        
    throw ConnectionException::commitFailedRollbackOnly();
    }    if ($this->_transactionNestingLevel == 1) {        
    $this->_conn->commit();
    } elseif ($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:

publicfunctionbeginTransaction(){
    ++$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:

publicfunctionrollBack(){    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!

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

    --$this->transactions;
}

The above is the content of MySQL's nested transaction implementation. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!