Blog of a LAMP Developer based near Guildford, Surrey

After plenty of research and trials, it is possible to use mysql transactions for custom queries.

You still have to create the table with InnoDB for row based locking to work.

ALTER TABLE table_name type=InnoDB;

And set the transactions variable in your model

var $transactional = true;

Now in your method use the following:

class YourModel extends AppModel {

var $transactional = true;

function mymethod()
{

  $db =& ConnectionManager::getDataSource($this->useDbConfig);

 #start the transaction
  $db->begin($this);

 $success = $db->query('my sql');
  if ($success  === true) {
       return $db->commit($this);
  } else {
       $db->rollback($this);
       return false;
  }
}

}