Run insert functions in PHP as transaction

Question

I have a Model class which is in Model.php and i have two models like ModelOne and ModelTwo in ModelOne.php and ModelTwo.php. These models extend the base Model class. The model class extends a DatabaseConnector class which uses PDO to interact with database.

class ModelOne extends Model {
    public function storeX($params1) {
        $query = 'insert ...';
        return $this->insert($query, $params1);
    }
}

class ModelTwo extends Model {
    public function storeY($params2) {
        $query = 'insert ...';
        return $this->insert($query, $params2);
    }
}

class Model extends DatabaseConnector {/* not related to this question */}

class DatabaseConnector {
    private $_mysql;

    public function __construct() {
        $this->_mysql = new PDO(...);
    }

    public function insert($query, $params = array()) {
        $sql_stmt = $this->_mysql->prepare($query);

        foreach($params as $i => $param) {
            $sql_stmt->bindValue(++$i, $param['value'], $param['type']);
        }

        $sql_stmt->execute();
        return $this->_mysql->lastInsertId();
    }
}

I am running two different functions in my model classes like this:

/* begin transaction */
$model1 = new ModelOne();
$anotherParam = $model1->storeX($params1);

$model2 = new ModelTwo();
$model2->storeY($params2, $anotherParam);
/* end transaction */

I want these two store functions (each one run "prepare", "bindValue" and "execute" from PDO) to run as transactions. What should i do? I prefer not to change my MVC structure.

Edit: $anotherParam variable added.

Answer 1

Without changing your current DatabaseConnector and class inherintance

You could arrange a "transactional" fallback by using a couple of try...catch blocks if the storeX() and storeY() methods return Exception objects on failure:

try {
    $model1 = new ModelOne();
    $anotherParam = $model1->storeX($params1);
} catch (Exception ex) {
    //First INSERT failed
    //Handle the issue and don't continue
    exit;
}

try {
    $model2 = new ModelTwo();
    $model2->storeY($params2, $anotherParam);
} catch (Exception ex) {
    //Second INSERT failed
    //Roll back the first INSERT!
    $model1->removeX($anotherParam);
}

Modifying how DatabaseConnector is used

It would be much nicer to use proper MySQL database-side transactions, but this would require you to have a single database connection that is shared between the various ModelX objects.

You should extend DatabaseConnector to handle transactions nicely (as suggested by @jayxhj), and this will suit your needs.

If you want/need all of your models to use the same instance of the DatabaseConnector class (which will allow you to use transactions from within your ModelX classes if you want), you could make your DatabaseConnector implement the singleton pattern.

I do not think that your ModelX classes should be extending the DatabaseConnector class - they don't seem to be more specific versions of the database connector. Rather, I think that the ModelX classes should just make use of the DatabaseConnector like this example:

Wrapping calls in a transaction

//Start transaction
$db = DatabaseConnector::getInstance();
$db->beginTransaction();

try
{
    //Do database work
    $model1 = new ModelOne();
    $anotherParam = $model1->storeX($params1);
    $model2 = new ModelTwo();
    $model2->storeY($params2, $anotherParam);

    //Commit the whole transaction
    $db->commit();    
}
catch (Exception ex)
{
    //Roll back the whole transaction
    $db->rollback();
}

Model class

class Model {
    private $_mysql;

    public function __construct() {
        //Get "singleton" instance of the DatabaseConnector (shared between all Models)
        $this->_mysql = DatabaseConnector::getInstance();
    }
}

DatabaseConnector class

class DatabaseConnector extends Singleton {
    private $_mysql;

    public function __construct() {
        $this->_mysql = new PDO(...);
    }

    public function beginTransaction() { 
        return $this->_mysql->beginTransaction();
    }

    public function commit() { 
        return $this->_mysql->commit();
    }

    public function rollback() { 
        return $this->_mysql->rollback();
    }
}

Singleton class

class Singleton
{
    /**
     * @var Singleton The reference to *Singleton* instance of this class
     */
    private static $instance;

    /**
     * Returns the *Singleton* instance of this class.
     *
     * @return Singleton The *Singleton* instance.
     */
    public static function getInstance()
    {
        if (null === static::$instance) {
            static::$instance = new static();
        }

        return static::$instance;
    }

    /**
     * Protected constructor to prevent creating a new instance of the
     * *Singleton* via the `new` operator from outside of this class.
     */
    protected function __construct()
    {
    }

    /**
     * Private clone method to prevent cloning of the instance of the
     * *Singleton* instance.
     *
     * @return void
     */
    private function __clone()
    {
    }

    /**
     * Private unserialize method to prevent unserializing of the *Singleton*
     * instance.
     *
     * @return void
     */
    private function __wakeup()
    {
    }
}