Transaction behavior

by Jippi
A little behavior that enables you to use (nested) transactions in 1.2+ with MySQL.

Example use

Sample model - note the $actsAs

To change default settings do:

var $actsAs = array('Transactional');

Model Class:

<?php 
class Order extends AppModel
{
    var 
$name     'Order';
    var 
$actsAs   = array('Transactional');
    var 
$hasMany  = array('OrderDetail');
?>

Controller Class:

<?php 
class OrderController extends AppController {
    var 
$name 'Order';
    var 
$uses = array('Order');

    function 
checkout() {
        if(!empty(
$this->data)) {
            
$this->Order->begin(); // Start our transaction
            
if( $this->Order->save$this->data ) ) {
                if( 
$this->Order->OrderDetail->save$this->data ) ) {
                    
$this->Order->commit(); // Persist the data
                    
$this->redirect('/shop/order/thanks');
                    exit;    
                } else { 
// Couldnt save the products to the order
// since the products to the order couldnt be stored, dont store anything at all
// rollback all changes to the database sine 'begin' was called
                    
$this->Order->rollback(); 
                    
$this->redirect('/shop/order/error');
                    exit;
                }
            } else { 
                
// Didnt save
            
}
        }
    }
}
?>

Model Class:

<?php 
<?php
/**
 * Transactional Behavior
 *
 * @author Christian Winther <cwin@expressional.com>
 * @version 1.0
 * @since 19.12.2010
 */
class TransactionalBehavior extends ModelBehavior {

    protected 
$savepoints = array();

    
/**
     * Begin a transaction
     *
     * @param Model $Model
     * @return boolean
     */
    
public function begin(Model $Model) {
        if (!
$this->inTransaction($Model)) {
            
$DataSource ConnectionManager::getDataSource($Model->useDbConfig);
            
$DataSource->begin($Model);
        } else {
            
$Model->query(sprintf('SAVEPOINT %s_%d'$Model->useDbConfig$this->getNextSavepoint($Model)));
        }

        
$this->inTransaction($Modeltrue);
        return 
true;
    }

    
/**
     * Commit a transaction
     *
     * @param Model $Model
     * @return boolean
     */
    
public function commit(Model $Model) {
        if (!
$this->inTransaction($Model)) {
            return 
false;
        }

        if (!
$this->hasSavepoint($Model)) {
            
$DataSource ConnectionManager::getDataSource($Model->useDbConfig);
            
$DataSource->commit($Model);
            
$this->inTransaction($Modelfalse);
        } else {
            
$Model->query(sprintf('RELEASE SAVEPOINT %s_%d'$Model->useDbConfig$this->getCurrentSavepoint($Model)));
            
$this->getPreviousSavepoint($Model);
        }

        return 
true;
    }

    
/**
     * Rollback a transaction
     *
     * @param Model $Model
     * @return boolean
     */
    
public function rollback(Model $Model) {
        if (!
$this->inTransaction($Model)) {
            return 
false;
        }

        if (!
$this->hasSavepoint($Model)) {
            
$DataSource ConnectionManager::getDataSource($Model->useDbConfig);
            
$DataSource->rollback($Model);
            
$this->inTransaction($Modelfalse);
        } else {
            
$Model->query(sprintf('ROLLBACK TO SAVEPOINT %s_%d'$Model->useDbConfig$this->getPreviousSavepoint($Model)));
        }

        return 
true;
    }

    
/**
     * Check if we are in transaction
     *
     * @param Model $Model
     * @return integer
     */
    
public function inTransaction(Model $Model$bool null) {
        
$key sprintf('Model.%s.InTransaction'$Model->useDbConfig);

        if (!
is_null($bool)) {
            return 
Configure::write($key$bool);
        }

        return 
Configure::read($key);
    }

    
/**
     * Get next savepoint identifier
     *
     * @param Model $Model
     * @return integer
     */
    
protected function getNextSavepoint(Model $Model) {
        if (!
array_key_exists($Model->useDbConfig$this->savepoints)) {
            return 
$this->savepoints[$Model->useDbConfig] = 0;
        }
        return ++
$this->savepoints[$Model->useDbConfig];
    }

    
/**
     * Get the previous savepoint identifier
     *
     * @param Model $Model
     * @return integer
     */
    
protected function getPreviousSavepoint(Model $Model) {
        if (!
array_key_exists($Model->useDbConfig$this->savepoints)) {
            throw new 
Exception(sprintf('Database connection %s does not have any savepoints'$Model->useDbConfig));
        }
        return --
$this->savepoints[$Model->useDbConfig];
    }

    
/**
     * Check if the database connection has any active savepoints
     *
     * @param Model $Model
     * @return boolean
     */
    
protected function hasSavepoint(Model $Model) {
        if (!
array_key_exists($Model->useDbConfig$this->savepoints)) {
            return 
false;
        }
        return 
$this->savepoints[$Model->useDbConfig] > 0;
    }

    
/**
     * Get the current savepoint identifier
     *
     * @param Model $Model
     * @return integer
     */
    
protected function getCurrentSavepoint(Model $Model) {
        if (!
array_key_exists($Model->useDbConfig$this->savepoints)) {
            throw new 
Exception(sprintf('Database connection %s does not have any savepoints'$Model->useDbConfig));
        }
        return 
$this->savepoints[$Model->useDbConfig];
    }
}
?>

Report

More on Behaviors

Advertising

Comments

  • aries posted on 07/07/11 06:57:49 PM
    +1 for simplicity. Works as intended, offers easy nested transactions to the Cake model library. The only drawback is that it's MySQL-only, which is okay for my current needs but may be a drag for others. If support for other databases can be included, I'd say it would make a fine contribution to the Cake core.
  • chizovozzi posted on 04/06/11 06:49:34 AM
    Works excellent on cake version 1.3! thanks!
  • tadfisher posted on 03/31/10 04:58:49 PM
    Refer to the following passage from http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html:

    The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly.

    So the use of lockTable() and begin() should be exclusive, and lockTable() should be modified to set autocommit = 0 prior to acquiring the lock. Otherwise, calling begin() followed by lockTable() will implicitly commit any live transactions, and calling lockTable() followed by begin() will implicitly release any existing locks.
    • gmansilla posted on 04/01/10 11:37:33 AM
      So, How would you do it in the cakephp way?
      Refer to the following passage from http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html:

      The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly.

      So the use of lockTable() and begin() should be exclusive, and lockTable() should be modified to set autocommit = 0 prior to acquiring the lock. Otherwise, calling begin() followed by lockTable() will implicitly commit any live transactions, and calling lockTable() followed by begin() will implicitly release any existing locks.
      • tadfisher posted on 04/15/10 10:49:10 PM
        Ideally, locking primitives should be implemented at the DB abstraction layer, providing both table-level and row-level locking functionality. It's possible to emulate this behavior for DBMSs that do not provide it, by using an application-wide lock mechanism (such as temporary lock descriptors). Model methods could implemented to expose this feature to users, and model fields could be set to automatically enable/disable locking tables and/or rows during a transaction.

        On a tangent, when I come across a design that requires locking, I often find that an application-wide mutex can better serve this purpose. MySQL provides a decent solution through its 'GET_LOCK', 'RELEASE_LOCK', and 'IS_FREE_LOCK' functions. Working with these gives you much more fine-grained control over locking; for example, you can lock only around controller actions that update-after-read, leaving other actions free to read those tables/rows to their heart's content.

        So, How would you do it in the cakephp way?
        Refer to the following passage from http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html:

        The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly.

        So the use of lockTable() and begin() should be exclusive, and lockTable() should be modified to set autocommit = 0 prior to acquiring the lock. Otherwise, calling begin() followed by lockTable() will implicitly commit any live transactions, and calling lockTable() followed by begin() will implicitly release any existing locks.
  • gmansilla posted on 03/02/10 04:54:54 PM
    Actually you can use cakephp features to use transactions, in that way you are sure your transactions will be executed no matter what database engine you use, take a look at this article I wrote: http://www.blog.cakephp4all.com/?p=7
    • netresource posted on 01/06/11 03:16:19 AM
      [quote] Actually you can use cakephp features to use transactions, in that way you are sure your transactions will be executed no matter what database engine you use, take a look at this article I wrote: http://www.blog.cakephp4all.com/?p=7
      [end quote] It works fine
      Thanks so much!
  • nelliko posted on 06/04/09 05:54:59 AM
    Hi to all.
    I´m writing a web application where need to edit information of the students.
    I´m tring use transaction, but I think that don´t works.
    The function for editing is:

    function edit($id = null) {
    $this->Alumno->begin();

    $this->Alumno->id = $id;

    if (empty($this->data)) {
    $this->data = $this->Alumno->read();
    } else {

    $this->data["Alumno"]["nombre_apellidos"] = $this->data ["Alumno"]["nombre"]." ".$this->data["Alumno"]["apellidos"];




    if ($this->Alumno->save($this->data))
    {
    $this->Alumno->commit(); // Persist the data
    $this->flash('Las modificaciones se han guardado.','/alumnos');
    exit;
    }
    else
    {
    $this->Alumno->rollback();
    $this->flash('En este momento no se puede modificar datos del alumno.
    Intente mas tarde por favor.','/alumnos');
    exit;
    }
    }
    }

    Any idea what is wrong? or need to do more configurations?
    Apreciate any help, thanks
  • cristina posted on 08/22/08 03:24:27 AM
    I find this article interesting but I've been trying to use it for hours and I don't get to make it work. I've copied the behavior into /app/models/behaviors, added the $actsAs to the model and copied the code for the controller, but it doesn't work.

    I'm using CakePHP 1.2.0.6311 beta version, and maybe this is the problem. I don't know whether it implements the transactions such as version 1.1 or such as version 1.2. I've also tried writting "var $transactional = true;" in the model (it corresponds with version 1.1), but it didn't work either.

    Can anyone help me? I'll accept any ideas... it seems to be easy but I don't know why I can't.
    • cristina posted on 09/03/08 05:14:09 AM
      I find this article interesting but I've been trying to use it for hours and I don't get to make it work. I've copied the behavior into /app/models/behaviors, added the $actsAs to the model and copied the code for the controller, but it doesn't work.

      I'm using CakePHP 1.2.0.6311 beta version, and maybe this is the problem. I don't know whether it implements the transactions such as version 1.1 or such as version 1.2. I've also tried writting "var $transactional = true;" in the model (it corresponds with version 1.1), but it didn't work either.

      Can anyone help me? I'll accept any ideas... it seems to be easy but I don't know why I can't.


      I'm so busy I forgot to tell you I solved my problem. I thought the problem was in my CakePHP version, but I didn't realize that my database tables had MyISAM engines. Just setting them to InnoDB my problem disappeared.

      Finally, I decided not to use CakePHP transactions because I wanted to commit some tables at the same time and I don't find useful having to do commit in every model, separatedly. So I'm using PHP instruction mysql_query();
      The next page is in Spanish but it may help you.

      http://carnar.blogspot.com/2006/11/transacciones-en-php-y-mysql.html
      • penfold_99 posted on 09/03/08 05:45:53 AM
        I find this article interesting but I've been trying to use it for hours and I don't get to make it work. I've copied the behavior into /app/models/behaviors, added the $actsAs to the model and copied the code for the controller, but it doesn't work.

        I'm using CakePHP 1.2.0.6311 beta version, and maybe this is the problem. I don't know whether it implements the transactions such as version 1.1 or such as version 1.2. I've also tried writting "var $transactional = true;" in the model (it corresponds with version 1.1), but it didn't work either.

        Can anyone help me? I'll accept any ideas... it seems to be easy but I don't know why I can't.


        I'm so busy I forgot to tell you I solved my problem. I thought the problem was in my CakePHP version, but I didn't realize that my database tables had MyISAM engines. Just setting them to InnoDB my problem disappeared.

        Finally, I decided not to use CakePHP transactions because I wanted to commit some tables at the same time and I don't find useful having to do commit in every model, separatedly. So I'm using PHP instruction mysql_query();
        The next page is in Spanish but it may help you.

        http://carnar.blogspot.com/2006/11/transacciones-en-php-y-mysql.html

        If you use SaveAll, it uses it's owns transaction functions automatically and so no need for this behaviour.
  • gwicksted posted on 10/18/07 08:09:31 PM
    It looks like this code is MySQL-specific which is fine for most CakePHP applications. Here are some examples of how some other popular databases start transactions:

    MySQL - BEGIN
    PostgreSQL - START TRANSACTION
    SQL Server 2000/MSDE/2005/Express - BEGIN TRANSACTION

    Unfortunately this is yet another area of SQL that is not shared among database vendors.

    Also, note that the cake / libs / model / dbo / dbo_*.php classes have the begin/commit/rollback transaction functions built-in.

    It appears that the models themselves have begin(), commit(), and rollback() functions. I just tested with MySQL. This would be more database independent than the query() function.

    (I'm using Cake version 1.1.16.5421)
  • scoby posted on 09/26/07 04:22:46 PM
    does this require the database engine to be set to InnoDB
    or does it 'emulate' transactions by using table locks in MyISAM type tables?
    • stabbie posted on 09/26/07 04:28:13 PM
      MyISAM

      InnoDB Transactions with MySQL at least, must be BEGIN or START TRANSACTION

      does this require the database engine to be set to InnoDB
      or does it 'emulate' transactions by using table locks in MyISAM type tables?
  • stabbie posted on 09/25/07 08:18:23 PM
    I know behaviours don't, but can this work in 1.1 with current filters/hooks?
  • imoel posted on 09/25/07 07:25:49 PM
    same as stab question, does this work in 1.1.x ?

    I thought the transaction documentation in manual was very bad. Only shown the function and its parameters, without any instruction how to use it.

    Can't move on with 1.2, since it's alpha and I'm build public web-application.
  • stabbie posted on 09/14/07 01:17:41 AM
    does this work in 1.1.x?
  • ianh posted on 08/13/07 02:49:25 AM
    Hi, there appear to be two minor typos in the rollback function as follows:


    function rollback( &$model, $args ) {}

    fails with an error but



    function rollback( &$model, $args = array() ) {

    works fine. Also,


    if( $this->_getTransactionState === true ) {

    should be



    if( $this->_getTransactionState() === true ) {

    Make those changes and the behavior works on 1.2. Thanks, Ianh
login to post a comment.