Adding transaction and locking support to the MySQL datasource

CakePHP's built-in MySQL datasource provides some basic transaction support by sending all unknown method calls directly to the datasource. However, this only enables us to use some basic transaction commands, and any locking would have to be performed through manual SQL queries.

Note

Table locking is a mechanism to effectively manage concurrent access to table contents by different client sessions. More information about locking in MySQL is available at http://dev.mysql.com/doc/refman/5.5/en/internal-locking.html.

This recipe shows how to modify an existing datasource by implementing better transaction support to the MySQL driver, adding locking operations, and finally allowing a recovery procedure for locked queries.

Note

More information about transaction support in MySQL databases is available at http://dev.mysql.com/doc/refman/5.5/en/commit.html.

Getting ready

To go through this recipe we need a sample table to work with. Create a table named profiles using the following SQL statement:

CREATE TABLE `profiles`(
`id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
`name` VARCHAR(255) default NULL,
PRIMARY KEY(`id`)
) ENGINE=InnoDb;

Note

The above query includes the specification of the MySQL database engine. Even when MyISAM (another available engine) can handle table level locking, row level locking is only possible on InnoDb tables. Furthermore, transactions are only supported on InnoDb. More information about the different engines and their supported features is available at http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html.

Add some sample data using the following SQL statements: 

INSERT INTO `profiles`(`id`, `name`) VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),

We proceed now to create the required model. Create the model Profile in a file named profile.php and place it in your app/models folder with the following contents:

<?php
class Profile extends AppModel {
}
?>

Create its appropriate controller ProfilesController in a file named profiles_controller.php and place it in your app/controllers folder with the following contents:

<?php
class ProfilesController extends AppController {
public function index() {
}
}
?>

How to do it...

  1. We start by creating the skeleton of our datasource. Create a folder named dbo inside your app/models/datasources folder. In the dbo folder, create a file named dbo_mysql_transaction.php with the following contents:
    <?php
App::import('Core', 'DboMysql'),
class DboMysqlTransaction extends DboMysql {
protected $backAutoCommit;
protected $lockTimeoutErrorCode = 1205;
public function __construct($config = null, $autoConnect = true) {
$this->_baseConfig = Set::merge(array(
'lock' => array(
'log' => LOGS . 'locks.log',
'recover' => true,
'retries' => 1
),
'autoCommit' => null
), $this->_baseConfig);
$this->_commands = array_merge(array(
'lock' => 'LOCK TABLES {$table} {$operation}',
'unlock' => 'UNLOCK TABLES',
'setAutoCommit' => 'SET @@autoCommit={$autoCommit}'
), $this->_commands);
parent::__construct($config, $autoConnect);
if (
!is_null($this->config['autoCommit']) &&
!$this->setAutoCommit($this->config['autoCommit'])
) {
trigger_error('Could not set autoCommit', E_USER_WARNING);
}
}
}
?>
  2. We continue by adding methods to lock and unlock tables. Edit your app/models/datasources/dbo/dbo_mysql_transaction.php file and add the following methods to the DboMysqlTransaction class:
    public function lock($model = null, $options = array()) {
if (!is_object($model) && empty($options)) {
$options = $model;
$model = null;
}
if (empty($options) && !isset($model)) {
trigger_error('Nothing to lock', E_USER_WARNING);
return false;
} elseif (!is_array($options)) {
$options = array('table' => $options);
} elseif (Set::numeric(array_keys($options))) {
if (count($options) > 1) {
$options = array('table' => $options[0], 'operation' => $options[1]);
} else {
if (!empty($options[0]) && is_array($options[0])) {
$options = $options[0];
} else {
$options = array('table' => $options[0]);
}
}
}
if (empty($options['table']) && isset($model)) {
$options = array_merge(array(
'table' => $model->table,
'alias' => $model->alias
), $options);
if (!empty($options['operation']) && $options['operation'] == 'read') {
unset($options['alias']);
}
}
$options = array_merge(array('alias'=>null, 'operation'=>'read', 'local'=>false, 'low'=>false), $options);
if (!in_array(strtolower($options['operation']), array('read', 'write'))) {
trigger_error(sprintf('Invalid operation %s for locking', $options['operation']), E_USER_WARNING);
return false;
}
$table = $this->fullTableName($options['table']);
if (!empty($options['alias'])) {
$table .= ' AS ' . $this->name($options['alias']);
}
$operation = strtoupper($options['operation']);
if ($options['operation'] == 'read' && $options['local']) {
$operation .= ' LOCAL';
} elseif ($options['operation'] == 'write' && $options['low']) {
$operation = 'LOW_PRIORITY ' . $operation;
}
$sql = strtr($this->_commands['lock'], array(
'{$table}' => $table,
'{$operation}' => $operation
));
return ($this->query($sql) !== false);
}
public function unlock($model = null, $options = array()) {
return ($this->query($this->_commands['unlock']) !== false);
}
While still editing the DboMysqlTransaction class, add the following methods to allow us to get and change the auto commit status:public function getAutoCommit($model = null) {
if (is_null($this->config['autoCommit'])) {
if (!$this->isConnected() && !$this->connect()) {
trigger_error('Could not connect to database', E_USER_WARNING);
return false;
}
$result = $this->query('SELECT @@autocommit AS ' . $this->name('autocommit'));
if (empty($result)) {
trigger_error('Could not fetch autoCommit status from database', E_USER_WARNING);
return false;
}
$this->config['autoCommit'] = !empty($result[0][0]['autocommit']);
}
return $this->config['autoCommit'];
}
public function setAutoCommit($model, $autoCommit = null) {
if (!$this->isConnected() && !$this->connect()) {
trigger_error('Could not connect to database', E_USER_WARNING);
return false;
}
if (is_bool($model)) {
$autoCommit = $model;
$model = null;
} elseif (is_array($autoCommit)) {
list($autoCommit) = $autoCommit;
}
$this->config['autoCommit'] = !empty($autoCommit);
$sql = strtr($this->_commands['setAutoCommit'], array(
'{$autoCommit}' => ($this->config['autoCommit'] ? '1' : '0')
));
return ($this->query($sql) !== false);
}
  3. We will now add our basic transaction commands. Edit your app/models/datasources/dbo/dbo_mysql_transaction.php file and add the following methods to the DboMysqlTransaction class:
    public function begin($model) {
$this->_startTransaction();
return parent::begin($model);
}
public function commit($model) {
$result = parent::commit($model);
$this->_endTransaction();
return $result;
}
public function rollback($model) {
$result = parent::rollback($model);
$this->_endTransaction();
return $result;
}
protected function _startTransaction() {
if ($this->getAutoCommit()) {
$this->backAutoCommit = $this->getAutoCommit();
$this->setAutoCommit(false);
}
}
protected function _endTransaction() {
if (isset($this->backAutoCommit)) {
$this->setAutoCommit($this->backAutoCommit);
$this->backAutoCommit = null;
}
}
public function query() {
$args = func_get_args();
if (!empty($args) && count($args) > 2 && in_array($args[0], array_keys($this->_commands))) {
list($command, $params, $model) = $args;
if ($this->isInterfaceSupported($command)) {
return $this->{$command}($model, $params);
}
}
return call_user_func_array(array('parent', 'query'), $args);
}
  4. We end by adding methods to recover from a locked query, and to log those locks. Once again, edit your app/models/datasources/dbo/dbo_mysql_transaction.php file and add the following methods to the DboMysqlTransaction class:
    public function _execute($sql, $retry = 0) {
$result = parent::_execute($sql);
$error = $this->lastError();
if (
!empty($error) &&
$this->config['lock']['recover'] &&
preg_match('/^' . preg_quote($this->lockTimeoutErrorCode) . '/', $error)
) {
if ($retry == 0) {
$message = 'Got lock on query [' . $sql . ']';
$queries = array_reverse(Set::extract($this->_queriesLog, '/query'));
if (!empty($queries)) {
$message .= " Query trace (newest to oldest): 
	";
$message .= implode("
	", array_slice($queries, 0, 5));
}
$this->lockLog($message);
}
if ($retry < $this->config['lock']['retries']) {
$result = $this->_execute($sql, $retry + 1);
} elseif (!empty($this->config['lock']['log'])) {
$this->lockLog('Failed after ' . number_format($retry) . ' retries'),
}
} elseif (empty($error) && $retry > 0 && !empty($this->config['lock']['log'])) {
$this->lockLog('Succeeded after ' . number_format($retry) . ' retries'),
}
if (empty($error) && !$this->fullDebug && !empty($this->config['lock']['log'])) {
$this->logQuery($sql);
}
return $result;
}
protected function lockLog($message) {
$message = '['.date('d/m/Y H:i:s') . '] ' . $message . "
";
$handle = fopen($this->config['lock']['log'], 'a'),
if (!is_resource($handle)) {
trigger_error(sprintf('Could not open log file %s', $this->config['lock']['log']), E_USER_WARNING);
return false;
}
fwrite($handle, $message);
fclose($handle);
return true;
}
  5. To test what happens when you reach a lock, edit your app/controllers/profiles_controller.php file and add the following method to the ProfilesController class:
    public function index() {
$this->Profile->setAutoCommit(false);
if ($this->Profile->lock()) {
$profile = $this->Profile->find('all'),
debug($profile);
$this->Profile->unlock();
}
exit;
}
  6. Open your MySQL client and issue the following SQL commands (don't close the client after you issue these commands as you may want to release the lock as shown later):
    SET @@autocommit=0;
LOCK TABLE `profiles` WRITE;
  7. If we now browse to http://localhost/profiles we should get an SQL error message that reads SQL Error: 1205: Lock wait timeout exceeded; try restarting transaction. A file named locks.log should have been created in your app/tmp/logs folder with the following contents (the database name cookbook_chapter5_transaction should change to the name of the database you are using):
    [23/06/2010 09:14:11] Got lock on query [LOCK TABLES `profiles` AS `Profile` READ] Query trace (newest to oldest):
SET @@autocommit=0
DESCRIBE `profiles`
SHOW TABLES FROM `cookbook_chapter5_transaction`;
[23/06/2010 09:14:17] Failed after 1 retries
  8. To test the recovery of locked queries, we could release the lock in our MySQL client by issuing the following command:
    UNLOCK TABLES;

    and do so somewhere between the first failed transaction and the next recovery attempt. To change how much time MySQL waits to see if a lock can be obtained, access MySQL documentation for the server setting innodb_lock_wait_timeout.

How it works...

As we are extending a DBO based datasource, we name our class using the Dbo prefix (DboMysqlTransaction), and place it in the dbo folder which is itself in our app/models/datasources folder.

The initial implementation includes two class properties:

  • backAutoCommit:Utilized by the helper methods _startTransaction() and _endTransaction(), is used to temporarily change the auto commit setting.
  • lockTimeoutErrorCode: Specifies MySQL's code number for identifying deadlock time expired errors.

Our first method is the class constructor, which is overridden to add our own connection settings, and the actual SQL commands to lock and unlock tables and to change the auto commit setting. The connection settings we added are:

  • lock: It is a set of settings that specify what to do when dealing with locked queries. Its subset of settings are:
    • log: It is path to the file where to store logging information. If set to false, logging will be disabled. Defaults to a file named locks.log that is created in the app/tmp/logs directory.
    • recover: It decides whether to try to recover from locked queries. If set to false, no recovery will be attempted. Defaults to true.
    • retries: It decides if recover is set to true, how many attempts to rerun the failed (locked) query. Defaults to 1.
  • autoCommit: It gives the initial autocommit value (true for enabled, false for disabled). If set to null, it will get its value from the database server.

We then implement the lock() and unlock() methods. The lock() method allows us to lock a table for a certain operation. We can use it directly from a model to lock its underlying table for a read operation:

$this->Profile->lock();

We can change the locking operation to be write: 

$this->Profile->lock(array('operation'=>'write'))

We can also use it to lock a specific table, using either the lock() method available on all models using this datasource, or directly invoking the method in the datasource:

$this->Profile->getDataSource()->lock(array(
'table' => 'profiles',
'operation'=>'write'
));

The unlock() method is used similarly, either through the model, or directly using the datasource) and unlocks all locked tables.

Note

When you do lock table, make sure you disable auto commit, by using the setAutoCommit() method, like so: $this->Profile->setAutoCommit(false);

In the next block of code, we add the implementation for starting, committing and rolling back transactions. There is not much detail needed for these methods except that they take care of disabling auto-commit upon starting a transaction, and reset its status after a transaction is finished.

The query() method is overridden to allow executing some of our datasource methods directly from our models. That is the case for the three methods we added: lock(), unlock(), and setAutoCommit().

Finally, we override the _execute() method to detect when a lock wait timeout error is thrown. In these cases, we use the lockLog() method to LOG the situation, and we proceed to retry the query if we were told to do so.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.138.37.191