Two solutions to implement Mysql nested transactions in PHP _mysql

First, the origin of the problem

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

Copy Code code as follows:

Transactions cannot be nested. This is a consequence of the implicit commit performed to any current transaction when you issue a START transaction stat Ement or one of its synonyms.

However, when we develop a complex system, it is inevitable to accidentally nested transactions in the transaction, such as the A function called B function, a function uses the transaction, and is called in the transaction B function, B function also has a transaction, so that a transaction nesting. At this time actually A's business is not very meaningful, why? As mentioned in the above document, the simple translation is:

Copy Code code as follows:

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

So we're going to support the nesting of transactions at the system architecture level. Fortunately, nesting support is done in some mature ORM frameworks, such as doctrine or laravel. Let's take a look at how these two frameworks are implemented.

The friendship hint, these two frame's function and the variable naming all compare intuitively, although looks very long, but all is by the name can directly know this function or the variable meaning, so do not see so a big tuo to be frightened:)

Second, the doctrine solution

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

Copy Code code as follows:

Public Function BeginTransaction ()
{
+ + $this->_transactionnestinglevel;
if ($this->_transactionnestinglevel = = 1) {
$this->_conn->begintransaction ();
else if ($this->_nesttransactionswithsavepoints) {
$this->createsavepoint ($this->_getnestedtransactionsavepointname ());
}
}

The first line of this function uses a _transactionnestinglevel to identify the level of the current nesting, if 1, which is not yet nested, then use the default method to execute the start transaction OK, if greater than 1, which is nested, She will help us create a savepoint, this savepoint can be understood as a transaction log point that can be rolled back only to this point when it needs to be rolled back.

Then look at the rollback function:

Copy Code code as follows:

Public Function RollBack ()
{
if ($this->_transactionnestinglevel = = 0) {
Throw Connectionexception::noactivetransaction ();
}

if ($this->_transactionnestinglevel = = 1) {
$this->_transactionnestinglevel = 0;
$this->_conn->rollback ();
$this->_isrollbackonly = false;
else if ($this->_nesttransactionswithsavepoints) {
$this->rollbacksavepoint ($this->_getnestedtransactionsavepointname ());
--$this->_transactionnestinglevel;
} else {
$this->_isrollbackonly = true;
--$this->_transactionnestinglevel;
}
}

You can see that the process is also very simple, if the level is 1, direct rollback, otherwise roll back to the front of the savepoint.

And then we continue to look at the commit function:

Copy code code as follows:

Public Function commit ()
{
if ($this->_transactionnestinglevel = = 0) {
Throw Connectionexception::noactivetransaction ();
}
if ($this->_isrollbackonly) {
Throw Connectionexception::commitfailedrollbackonly ();
}

if ($this->_transactionnestinglevel = = 1) {
$this->_conn->commit ();
else if ($this->_nesttransactionswithsavepoints) {
$this->releasesavepoint ($this->_getnestedtransactionsavepointname ());
}

--$this->_transactionnestinglevel;
}

Forget it, don't waste your breath explaining this part:)

Third, the Laravel solution

Laravel's approach is relatively simple and rough, so let's look at the operations that create the transaction:

Copy Code code as follows:

Public Function BeginTransaction ()
{
+ + $this->transactions;

if ($this->transactions = = 1)
{
$this->pdo->begintransaction ();
}
}

How do you feel? So easy, huh? First of all to determine the current several transactions, if it is the first, OK, the beginning of the transaction, or do nothing, so why is not doing anything? Continue to look down at the rollback operation:

Copy Code code as follows:

Public Function RollBack ()
{
if ($this->transactions = = 1)
{
$this->transactions = 0;

$this->pdo->rollback ();
}
Else
{
--$this->transactions;
}
}

You get it? Only when the current transaction is only one of the real rollback, otherwise just the count to do minus one operation. This is why just said Laravel's handling is more simple and rough, in the nested inner layer inside is actually wood has real affairs, only the outermost one whole affairs, although simple and rough, but also solved in the inner layer creates a new transaction will cause a commit problem. The principle is this way, in order to keep the integrity, the commit code also copy come!

Copy Code code as follows:

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

--$this->transactions;
}