Two solutions to implement MySQL nested transactions in PHP

1. Origin of the problem

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

1. 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 transactions will be nested in transactions unintentionally. For example, function A calls function B, function A uses transactions, and functions B is called in the transaction. Function B also has a Transactions, so transaction nesting occurs. At this time, A's affairs are actually of little significance. Why? It is mentioned in the above document, and the simple translation is:

1. When executing a START TRANSACTION instruction, a commit operation will be performed implicitly. Therefore, we must 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 see such a big mess at first sight. I was scared :)

2. Doctrine’s solution

First, let’s take a look at the code to create a transaction in doctrine (removed irrelevant code):

[php] view plaincopy

1. /**
2. * author http://www.lai18.com
3. * date 2015-04-19
4. * version 1
5. **/
6. publicfunctionbeginTransaction()
8. {
10. ++$this->_transactionNestingLevel;
13. }else
14. if($this->_nestTransactionsWithSavepoints) {
16. not not have been more}
}
18. 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, we can only roll back to this point. Then take a look at the rollBack function:[php] view plaincopy
    1. 1./**
    2. * author http://www.lai18.com
    3. * date 2015-04-19
    4. * version 1
    5. **/
    6. publicfunctionrollBack()
    8. {
    10. if($this->_transactionNestingLevel == 0) {
    12. throwConnectionException::noActiveTransaction();
    14. }
    15. if($this->_transactionNestingLevel == 1) {
    17. $this->_transactionNestingLevel = 0;
    19. $this->_conn->rollback();
    21. $this->_isRollbackOnly = false;
    23. }elseif($this->_nestTransactionsWithSavepoints) {
    25. $this->rollbackSavepoint($this->_getNestedTransactionSavePointName());
    27. --$this->_transactionNestingLevel;
    29. }else{
    31. $this->_isRollbackOnly = true;
    33. --$this->_transactionNestingLevel;
    35. }
    37. }

    可以看到处理的方式也很简单，如果level是1，直接rollback，否则就回滚到前面的savepoint。然后我们继续看下commit函数：

    [php] view plaincopy

    1. 1./**
    2. * author http://www.lai18.com
    3. * date 2015-04-19
    4. * version 1
    5. **/
    6. publicfunctioncommit()
    8. {
    10. if($this->_transactionNestingLevel == 0) {
    12. throwConnectionException::noActiveTransaction();
    14. }
    16. if($this->_isRollbackOnly) {
    18. throwConnectionException::commitFailedRollbackOnly();
    20. }
    21. if($this->_transactionNestingLevel == 1) {
    23. $this->_conn->commit();
    25. }elseif($this->_nestTransactionsWithSavepoints) {
    27. $this->releaseSavepoint($this->_getNestedTransactionSavePointName());
    29. }
    30. --$this->_transactionNestingLevel;
    32. }

    算了，不费口舌解释这段了吧 :)

    三、laravel的解决方案laravel的处理方式相对简单粗暴一些，我们先来看下创建事务的操作：

    [php] view plaincopy

    1. 1.
    2. /**
    3. * author http://www.lai18.com
    4. * date 2015-04-19
    5. * version 1
    6. **/
    7. publicfunctionbeginTransaction()
    9. {
    11. ++$this->transactions;
    12. if($this->transactions == 1)
    14. {
    16. $this->pdo->beginTransaction();
    18. }
    20. }

    感觉如何？so easy吧？先判断当前有几个事务，如果是第一个，ok，事务开始，否则就啥都不做，那么为啥是啥都不做呢？继续往下看rollBack的操作：

    [php] view plaincopy

    1. 1./**
    2. * author http://www.lai18.com
    3. * date 2015-04-19
    4. * version 1
    5. **/
    6. publicfunctionrollBack()
    8. {
    13. }
    14. else
            —
    19. }
    20. }
    21. 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!
    22. [php] view plaincopy
    23. publicfunction
    commit()
    {

    if

    (

    $this

    ->transactions == 1) $this
    ->pdo->commit();
    }
    4. The above introduces two solutions for implementing MySQL nested transactions in PHP, including the relevant aspects. I hope it will be helpful to friends who are interested in PHP tutorials.