Two solutions for implementing MySQL nested transactions in PHP

Source: Internet
Author: User
Tags savepoint vars

First, the origin of the problem

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

1. Transactions cannot be nested. This was a consequence of the implicit commit performed for any current transaction when you issue a START transaction stat Ement 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:

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):

[PHP]View Plaincopy
  1. /**
  2. * Author http://www.lai18.com
  3. * Date 2015-04-19
  4. * Version 1
  5. **/   
  6. Public function begintransaction ()
  7. {
  8. + +$this->_transactionnestinglevel;
  9. if ($this->_transactionnestinglevel = = 1) {
  10. $this->_conn->begintransaction ();
  11. } Else if ($this->_nesttransactionswithsavepoints) {
  12. $this->createsavepoint ($this->_getnestedtransactionsavepointname ());
  13. }
  14. }

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:

[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. if ($this->_transactionnestinglevel = = 0) {
  9. throw connectionexception::noactivetransaction ();
  10. }
  11. if ($this->_transactionnestinglevel = = 1) {
  12. $this->_transactionnestinglevel = 0;
  13. $this->_conn->rollback ();
  14. $this->_isrollbackonly = false;
  15. } Else if ($this->_nesttransactionswithsavepoints) {
  16. $this->rollbacksavepoint ($this->_getnestedtransactionsavepointname ());
  17. --$this->_transactionnestinglevel;
  18. } Else {
  19. $this->_isrollbackonly = true;
  20. --$this->_transactionnestinglevel;
  21. }
  22. }

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:

[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. if ($this->_transactionnestinglevel = = 0) {
  9. throw connectionexception::noactivetransaction ();
  10. }
  11. if ($this->_isrollbackonly) {
  12. throw connectionexception::commitfailedrollbackonly ();
  13. }
  14. if ($this->_transactionnestinglevel = = 1) {
  15. $this->_conn->commit ();
  16. } Else if ($this->_nesttransactionswithsavepoints) {
  17. $this->releasesavepoint ($this->_getnestedtransactionsavepointname ());
  18. }
  19. --$this->_transactionnestinglevel;
  20. }

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

Third, Laravel solution Laravel is relatively simple and rough, let's take a look at the operation of creating a transaction:

[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 ()
  8. {
  9. + +$this->transactions;
  10. if ($this->transactions = = 1)
  11. {
  12. $this->pdo->begintransaction ();
  13. }
  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:

[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. if ($this->transactions = = 1)
  9. {
  10. $this->transactions = 0;
  11. $this->pdo->rollback ();
  12. }
  13. Else
  14. {
  15. --$this->transactions;
  16. }
  17. }

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!

[PHP]View Plaincopy
    1. Public function commit ()
    2. {
    3. if ($this->transactions = = 1) $this->pdo->commit ();
    4. --$this->transactions;
    5. }

Two solutions for implementing MySQL nested transactions in PHP

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.