Transactions handling in LIZ DB

In today's article I will present transaction handling in LIZ DB.

There are two ways developers may handle their transactions:

• automatic - managed by LIZ DB
• programmatic - developers must commit, rollback all transactions themselves
  

1. LIZ DB managed transactions

LIZ DB supports two automatic behaviours, those can be turn on/off by passing true or false values to one of following methods:

• public function SetAutoCommit($commit = true) - data will be committed after each INSERT, UPDATE or DELETE statement
• public function SetCommitOnClose($commit = true) - all data will be committed at the end of current session
  

Default MySQLimp settings are:

private $CommitOnClose = true;
private $AutoCommit = false;

INFO
These two behaviours are mutually explicit! If both set to true, auto commit will work.
INFO
MySQL supports transactions only when InnoDB engine is being used!
MyISAM or any other engines do not support transactions.

Examples of usage.

Example 1. LIZ DB managed transactions.

$db->SetAutoCommit(); // default value true will be used
$db->SetAutoCommit(true); // same as above
$db->SetAutoCommit(false); // no auto commits after DML statements
// if auto commit is set to false, below code will work
// all data will be committed on connection close
$db->SetCommitOnClose(true); // default value true will be used
$db->SetCommitOnClose(); // same as above
// now, data won't be committed on connection close
$db->SetCommitOnClose(false) 

2. User managed transactions

LIZ DB offers following methods for managing transactions programmatic way:

• public function StartTransaction() - starts transaction
  
• public function Commit() - commits all DML statements executed in current transaction
  
• public function AbortTransaction() - rollbacks all changes made to database in current transaction
  
• public function SetSavePoint($name) - sets a new logical savepoint within current transaction
  
• public function RollbackToSavePoint($name) - rollbacks all changes made to database to given logical savepoint
  

Examples of usage.

Example 2. Basic transaction handling.

$db = new MySQLimp($config);

// start the transaction
$db->StartTransaction();

$chars = "some text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$newId = $db->RunQuery();

echo 'new record id: ' . $newId . "\n";

// rollback current transaction
// all changes will be rejected
$db->Rollback();

// explicitly begin new transaction
$db->StartTransaction();

$chars = "some other text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$newId = $db->RunQuery();

echo 'second new record id: ' . $newId . "\n";

// after executing Commit method, all changes
// made within current transaction will be
// visible to other users
$db->Commit();

Example 3. Transaction savepoints handling.

$db = new MySQLimp($config);

// explicitly start new transaction
$db->StartTransaction();

// will store IDs of newly inserted records
$ids = array();

$chars = "some text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$ids[] = $db->RunQuery();

// dirty record
echo 'first new record id: ' . $ids[0] . "\n";

$chars = "some other text";
$int = mt_rand();
$double = mt_rand() / mt_getrandmax();

// everything that will be executed from now on
// can be rollbacked to this point
$db->SetSavePoint('beforeInsertingSecondRecord');

$db->Query = "INSERT INTO `lizdb_test_table` VALUES (NULL, '{$chars}', {$int}, {$double})";
$ids[] = $db->RunQuery();

// second dirty record
echo 'second new record id: ' . $ids[1] . "\n";

// rollback to 'beforeInsertingSecondRecord' savepoint
// second record will be rejected
$db->RollbackToSavePoint('beforeInsertingSecondRecord');

// record was rejected, this will return empty set
$db->Query = "SELECT `id` FROM `lizdb_test_table` WHERE `id` = '{$ids[1]}'";
$second = $db->SelectOne();

echo 'Is rec no ' . $ids[1] .  ' in db now? ' . !empty($second) . '';

// the first one is still dirty (either not committed or not rollbacked)
// abort transaction and rollback all uncommitted data
$db->AbortTransaction();

// verify if both records do not exist in database
$ids = implode(',', $ids);
$db->Query = "SELECT * FROM `lizdb_test_table` WHERE `id` IN ({$ids})";
$added  = $db->Select();

// both records are 'wiped out' without any trace, count($added) == 0
echo 'No of added records: ' . count($added) . '';

3. Live examples

Live examples are available at LIZ DB - Transactions API Examples

INFO
Currently live examples server does not support MySQL InnoDB storage and transactions.