Two solutions for implementing MySQL nested transactions in PHP

Two solutions for implementing MySQL nested transactions in PHP

first, the origin of the problem

There is a clear explanation in the official MySQL documentation that nested transactions are not supported:

[SQL]View Plaincopy

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

But when we develop a complex system, it is unavoidable to inadvertently nest transactions in the transaction, such as a function called the B function, a function uses a transaction, and is called in the transaction B function, B function also has a transaction, so there is a transaction nesting. In fact, the matter of a is of little significance, why? As mentioned in the above document, a simple translation is:

[SQL]View Plaincopy

1. When a start transaction instruction is executed, a commit operation is performed implicitly .

So we need to support the nesting of transactions at the system architecture level. Fortunately, nested support has been made in some mature ORM frameworks, such as doctrine or laravel. Let's take a look at how these two frameworks are implemented.
Friendly hints, the two framework of the function and the name of the variable is more intuitive, although it looks very long, but all through the name can directly know the meaning of this function or variable, so do not see such a big lump is frightened:)

Second, the doctrine solution

First look at the code that creates the transaction in doctrine (the irrelevant code is killed):

[SQL]View Plaincopy

2. Public function BeginTransaction ()
6. {
10. + + $this->_transactionnestinglevel;
14. if ($this->_transactionnestinglevel = = 1) {
18. $this->_conn->begintransaction ();
22. } else if ($this->_nesttransactionswithsavepoints) {
26. $this->createsavepoint ($this->_getnestedtransactionsavepointname ());
30. }
34. }

The first line of this function uses a _transactionnestinglevel to identify the current level of nesting, if it is 1, that is not nested, then use the default method to execute the start transaction OK, if it is greater than 1, that is, there is nesting time, She's going to help us create a savepoint, which can be understood as a transaction record point that can only be rolled back to this point when a rollback is required.
Then look at the rollback function:

[SQL]View Plaincopy

2. Public function rollBack ()
6. {
10. if ($this->_transactionnestinglevel = = 0) {
14. Throw Connectionexception::noactivetransaction ();
18. }
20. if ($this->_transactionnestinglevel = = 1) {
24. $this->_transactionnestinglevel = 0;
28. $this->_conn->rollback ();
32. $this->_isrollbackonly = false;
36. } else if ($this->_nesttransactionswithsavepoints) {
40. $this->rollbacksavepoint ($this->_getnestedtransactionsavepointname ());
44. --$this->_transactionnestinglevel;
48. } Else {
52. $this->_isrollbackonly = true;
56. --$this->_transactionnestinglevel;
60. }
64. }

You can see the way of processing is also very simple, if level is 1, direct rollback, otherwise roll back to the previous savepoint.
Then we continue to look at the commit function:

[SQL]View Plaincopy

2. Public function commit ()
6. {
10. if ($this->_transactionnestinglevel = = 0) {
14. Throw Connectionexception::noactivetransaction ();
18. }
22. if ($this->_isrollbackonly) {
26. Throw Connectionexception::commitfailedrollbackonly ();
30. }
32. if ($this->_transactionnestinglevel = = 1) {
36. $this->_conn->commit ();
40. } else if ($this->_nesttransactionswithsavepoints) {
44. $this->releasesavepoint ($this->_getnestedtransactionsavepointname ());
48. }
50. --$this->_transactionnestinglevel;
54. }

Forget it, don't bother explaining the paragraph:)
Third, the Laravel solution

Laravel's handling is relatively straightforward, let's take a look at the operation of creating a transaction:

[SQL]View Plaincopy

1. Public function BeginTransaction ()
3. {
5. + + $this->transactions;
6. if ($this->transactions = = 1)
8. {
10. $this->pdo->begintransaction ();
12. }
14. }

How do you feel? So easy, huh? Judge the current there are several transactions, if it is the first, OK, the start of the transaction, or do nothing, then why do not do anything? Continue to look at the operation of Rollback:

[SQL]View Plaincopy

2. Public function rollBack ()
6. {
10. if ($this->transactions = = 1)
14. {
18. $this->transactions = 0;
20. $this->pdo->rollBack ();
24. }
28. Else
32. {
36. --$this->transactions;
40. }
44. }

Do you understand? Only if the current transaction is only one time will be true rollback, otherwise just will be counted to do minus one operation. This is why just said Laravel's handling is relatively simple and rough, in the nested inner layer is actually the real business of wood, only the outermost of a whole transaction, although simple and rough, but also solved in the inner layer of a new transaction will cause a commit problem. The principle is this way, in order to maintain integrity, the code of the commit to copy it!

[SQL]View Plaincopy

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

Reference Source:
Two solutions for implementing MySQL nested transactions in PHP
Http://www.lai18.com/content/345498.html

Two solutions for implementing MySQL nested transactions in PHP