PHP "Nested" transactions in MySQL could be a reality using the following code?

Question

OK I'm finding a solution for "NESTED" transactions in MySQL using PHP, and as you know in the MySQL documentation says that it's not possible to have transactions within transactions (Mysql transactions within transactions). I was trying to use the Database class propossed in http://php.net/manual/en/pdo.begintransaction.php but unfortunately that's wrong for me, because its counter scope is object level and not class level, to solve this issue I created this class (TransactionController) that has the counter (named $nest) static and it brings the class level required to make the transactions "linear" (with "linear" I'm saying: it aparently is nested but if you look quite it is not nested, then the transactions will work well, what do you think? (LOOK THE EXAMPLE AT THE END, CarOwner)

        class TransactionController extends \\PDO {
            public static $warn_rollback_was_thrown = false;
            public static $transaction_rollbacked = false;
            public function __construct()
            {
                parent :: __construct( ... connection info ... );
            }
            public static $nest = 0;
            public function reset()
            {
                TransactionController :: $transaction_rollbacked = false;
                TransactionController :: $warn_rollback_was_thrown = false;
                TransactionController :: $nest = 0;
            }
            function beginTransaction()
            {
                $result = null;
                if (TransactionController :: $nest == 0) {
                    $this->reset();
                    $result = parent :: beginTransaction();
                }
                TransactionController :: $nest++;
                return $result;
            }

            public function commit()
            {

                $result = null;

                if (TransactionController :: $nest == 0 &&
                        !TransactionController :: $transaction_rollbacked &&
                        !TransactionController :: $warn_rollback_was_thrown) {
                            $result = parent :: commit();
                        }
                        TransactionController :: $nest--;
                        return $result;
            }

            public function rollback()
            {
                $result = null;
                if (TransactionController :: $nest >= 0) {
                    if (TransactionController :: $nest == 0) {
                        $result = parent :: rollback();
                        TransactionController :: $transaction_rollbacked = true;
                    }
                    else {
                        TransactionController  :: $warn_rollback_was_thrown = true;
                    }
                }
TransactionController :: $nest--;
                return $result;
            }

    public function transactionFailed()
    {
        return TransactionController :: $warn_rollback_was_thrown === true;
    }
    // to force rollback you can only do it from $nest = 0
    public function forceRollback()
    {
        if (TransactionController :: $nest === 0) {
            throw new \PDOException();
}
    }
        }

        class CarData extends TransactionController {
            public function insertCar()
            {

                try {
                    $this->beginTransaction();
                    ... (operations) ...
                    $this->commit();
                }
                catch (\PDOException $e) {
                    $this->rollback();
                }
            }
        }
        class PersonData extends TransactionController {
            public function insertPerson(  $person=null )
            {
                try {
                    $this->beginTransaction();
                    ... (operations) ...
                    $this->commit();
                }
                catch (\PDOException $e) {
                    $this->rollback();
                }
            }
        }

        class CarOwnerData extends TransactionController {
            public function createOwner()
            {
                try {
                    $this->beginTransaction();

                    $car = new CarData();
                    $car->insertCar();

                    $person = new PersonData();
                    $person->insertPerson();

                    ... (operations) ...

                    $this->commit();
                }
                catch (\PDOException $e) {
                    $this->rollback();
                }
            }
        }


        $sellCar = new CarOwnerData();
        $sellCar->createOwner();

UPDATE1: static attribute $warn_rollback_was_thrown was added to TransactionController in order to warn that the transaction was failed in some moment of the execution, but there wasn't rollbacked.

UPDATE2: When a transaction fails in some moment you can let the code still running to the end or STOP it definitively using forceRollback(), as an example of this see the following code:

<?php    // inside the class PersonData

    public function insertMultiplePersons( $arrayPersons )
    {
        try {
        $this->beginTransaction();
        if (is_array( $arrayPersons )) {
            foreach ($arrayPersons as $k => $person) {
                $this->insertPerson( $person ); 
                if ($this->transactionFailed()) {
                    $this->forceRollback();                    
                }
            }
        }
        $this->commit();
        }
        catch (\PDOException $e) {
            $this->rollback();
        }
    } ?>

Answer 1

I made the comment above:

By using static class variables, you have just blocked yourself from having more than one db connection (e.g. to different databases) during any given request.

You seem to have a question about my comment:

@BillKarwin what you are meaning may be that the $nest counter be static per db connection. – Cristian Crishk

That's not how static works in PHP. A static class property is shared by all instances of the class. If one instance updates it, all other instances see the change.

<?php

class Foo {
 static $nest = 0;

 public function getNest() {
  return Foo::$nest;
 }

 public function setNest($newNest) {
  Foo::$nest = $newNest;
 }

}

$foo1 = new Foo();
$foo2 = new Foo();

echo "foo1::nest = " . $foo1->getNest() . "\n";
echo "foo2::nest = " . $foo2->getNest() . "\n";

$foo1->setNest(42);

echo "foo1::nest = " . $foo1->getNest() . "\n";
echo "foo2::nest = " . $foo2->getNest() . "\n";

Output:

foo1::nest = 0
foo2::nest = 0
foo1::nest = 42
foo2::nest = 42

This means your static $nest class property is the same value for all db connections in your application. You therefore can't have more than one db connection with your current design.

I don't even know why you made this property static. It doesn't need to be.

But I agree with the answer from @ICE that it's a folly to try to implement this kind of "nesting transaction" class. It doesn't work. Transactions are scoped to the db connection, not to objects. I've written about this before on Stack Overflow, way back in 2008. Read my answer to: How do detect that transaction has already been started?

Answer 2

As pointed out by @YourCommonSense in the comments, you aren't actually implementing nested transactions.

I'm not sure I like the idea of calling commit() anywhere in my code and it not actually committing anything.

Your whole solution seems to be an attempt to mitigate a design decision to put transaction code in your insert functions and forget about it.

You could separate your insert operations from the transaction logic and wrap these function calls in a separate function which does the transactions:

public/private function insertPerson(  $person=null )
{
  ... (operations) ...
}

public function createPerson()
{
    $person = new Person();
    ... (setup person) ...

    $this->beginTransaction();
    try {
        $this->insertPerson($person);
        $this->commit();
    }
    catch (\PDOException $e) {
        $this->rollback();
    }
} 

If you are absolutely sure you need to always insert the person within a transaction, you could check you are within a transaction when it is called:

public/private function insertPerson($person=null)
{
  if (!$this->hasActiveTransaction){ // Needs implementing
     throw new Exception('Must be called within a transaction');
  }
  ...(operations)...
}

In our projects, all the saving logic is within the Models and all transaction logic is at the Controller level.

I'm assuming you are aware that for a single statement there is no need for a transaction, as these are atomic operations, and that your code represents more complex cases.

Answer 3

The logic of the insert in your code must change.

Unnecessary LOOP is the worst thing can affect the PERFORMANCE.

When you know you want to insert multiple persons and they can insert with one query. don't do it inside loop. Just do it with ONE query. This is the main syntax for multiple insert:

INSERT INTO table_name (col1,col2,col3,...)
VALUES (Value1,Value2,...), (Value1,Value2,...)

insertPerson method must deal with multiple persons. like this:

$this->insertPerson($arrayPersons);

and inside insertPerson method you must create VALUES just like i explained here before: How to insert multiple dynamic rows into the database

and after that, insertPerson method can insert one person or multiple persons in ONE QUERY.