Two solutions to implement MySQL nested transactions in PHP, mysql nesting_PHP tutorial

Two solutions to implement MySQL nested transactions in PHP, mysql nesting

1. Origin of the problem

There is a clear statement in the official MySQL documentation 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, we will inevitably inadvertently nest transactions within transactions. For example, function A calls function B, function A uses a transaction, and function B is called in the transaction. Function B There is also 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, and the simple translation is:

1. When executing a START TRANSACTION instruction, a commit operation will be performed implicitly. Therefore, 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 see such a big mess at first sight. I was so 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. public function beginTransaction()
8. {
10. $this->_transactionNestingLevel;
12.  if ($this->_transactionNestingLevel == 1) {
14. $this->_conn->beginTransaction();
16. } else if ($this->_nestTransactionsWithSavepoints) {
18. $this->createSavepoint($this->_getNestedTransactionSavePointName());
20. }
22. } 

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. public function rollBack()  
7.   
8. {  
9.   
10.     if ($this->_transactionNestingLevel == 0) {  
11.   
12.         throw ConnectionException::noActiveTransaction();  
13.   
14.     }  
15.     if ($this->_transactionNestingLevel == 1) {  
16.   
17.         $this->_transactionNestingLevel = 0;  
18.   
19.         $this->_conn->rollback();  
20.   
21.         $this->_isRollbackOnly = false;  
22.   
23.     } else if ($this->_nestTransactionsWithSavepoints) {  
24.   
25.         $this->rollbackSavepoint($this->_getNestedTransactionSavePointName());  
26.   
27.         --$this->_transactionNestingLevel;  
28.   
29.     } else {  
30.   
31.         $this->_isRollbackOnly = true;  
32.   
33.         --$this->_transactionNestingLevel;  
34.   
35.     }  
36.   
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. public function commit()  
7.   
8. {  
9.   
10.     if ($this->_transactionNestingLevel == 0) {  
11.   
12.         throw ConnectionException::noActiveTransaction();  
13.   
14.     }  
15.   
16.     if ($this->_isRollbackOnly) {  
17.   
18.         throw ConnectionException::commitFailedRollbackOnly();  
19.   
20.     }  
21.     if ($this->_transactionNestingLevel == 1) {  
22.   
23.         $this->_conn->commit();  
24.   
25.     } else if ($this->_nestTransactionsWithSavepoints) {  
26.   
27.         $this->releaseSavepoint($this->_getNestedTransactionSavePointName());  
28.   
29.     }  
30.     --$this->_transactionNestingLevel;  
31.   
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. public function beginTransaction()
9. {
11. $this->transactions;
12. if ($this->transactions == 1)
14. {
16. $this->pdo->beginTransaction();
18. }
20. } 

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:

[php] view plaincopy  

1.     1. /**  
2. * author http://www.lai18.com  
3. * date 2015-04-19  
4. * version 1  
5. **/   
6. public function rollBack()  
7.   
8. {  
9.   
10.     if ($this->transactions == 1)  
11.   
12.     {  
13.   
14.         $this->transactions = 0;  
15.         $this->pdo->rollBack();  
16.   
17.     }  
18.   
19.     else  
20.   
21.     {  
22.   
23.         --$this->transactions;  
24.   
25.     }  
26.   
27. }   

 

明白了吧？只有当当前事务只有一个的时候才会真正的rollback，否则只是将计数做减一操作。这也就是为啥刚才说laravel的处理比较简单粗暴一些，在嵌套的内层里面实际上是木有真正的事务的，只有最外层一个整体的事务，虽然简单粗暴，但是也解决了在内层新建一个事务时会造成commit的问题。原理就是这个样子了，为了保持完整起见，把commit的代码也copy过来吧！

 

[php] view plaincopy  

1. public function commit()  
2.   
3. {  
4.   
5.     if ($this->transactions == 1) $this->pdo->commit();  
6.     --$this->transactions;  
7.   
8. }  

http://www.bkjia.com/PHPjc/991536.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/991536.htmlTechArticlePHP中实现MySQL嵌套事务的两种解决方案，mysql嵌套 一、问题起源 在MySQL的官方文档中有明确的说明不支持嵌套事务： 1. Transactions cannot be n...