Transactions

If the DBMS supports transactions ,it is possible to carry out a group of queries as a single unit of operation. All queries must execute successfully or none of them at all.There is no threat of some sql statements being failed while other part execute successfully.so it is assured that the database never falls in to that inconsistent state.

Properties of a Transaction

A transaction should manitain 4 main properties,identified by “ACID”

  • Atomicity(A)-Either all tasks execute or none of them execute at all
  • Consistency(C) -Database will be at a legal(consistent) state before and after executing the transaction
  • Isolation(I) -Operations in the transaction will be isolated from all other operations.They are open to other operations after the transaction get completed
  • Durability(D) -Ensures that any transaction committed to the database will not be lost

The need for Transactions

Transactions will be very helpful and might be regarded as a must when it is needed to update several tables or entries once.Transactions guarantees that no queries will be missed or failed.It is better to incorporate transactions to all vital tables or to the whole database.

Why SAHANA needs Transaction support

The SAHANA's architectire is built such that it updates several tables for a single unit of work(eg: insert a new inventory in IMS).So there is always a risk of some queries being faild at any moment.

ADODB Transaction Support

ADOdb has a rich collection of functions that enable the transaction support.Rather than restricting to a native database functionalities it is always better to use an abstraction library such as ADOdb.In recent versions ADOdb has improved transaction capabilities in to a much higher level.

ADOdb API functions

BeginTrans() and StartTrans()

  • Either of these functions can be used to start the transaction.the StartTrans()function is an improvement because it monetors errors it self and the developer is not required to catch errors

CommitTrans()

  • Complete a transaction which starts with the BeginTrans() function

CompleteTrans()

  • Complete a transaction which starts with the StratTrans() function.it will commit if no errors occur and rollback if any error availabel.

RollbackTrans()

  • This will rollback the entire transaction and bring the database to the pre transaction state.

The following example would illustrate the usage of transactions using ADOdb layer

<?php
//make the connection and create InnoDB tables
include('..//adodb/adodb.inc.php');
$db=ADONewConnection('mysqli');
$db->debug =true;
$db->Connect('localhost','root','','test');
$db->Execute('create table tab_temp1(name char(10),age char(10)) type=InnoDB');
$db->Execute('create table tab_temp2(name char(10),age char(10)) type=InnoDB');

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

$ok=$db->Execute('insert into tab_temp1(name,age) values("test","20")');
if ($ok) $ok=$db->Execute('insert into tab_temp2(name,age) values("test1","30")');

//commit or rollback the transaction
if ($ok) $db->CommitTrans();
else $db->RollbackTrans();

?>

Implementing Transaction support in a MySQL environment

It should be noted that if we use mysql as the DBMS we should provide “mysqlt” or “mysqli” as the database driver at the ADOdb connection setting phase

In a mysql environment we should create tables using the InnoDB engine to provide the transaction support.otherwise it will provide a warning message and queries will execute in the normal way(as if transaction support is not included).Rollback will not work and it sets auto commit as true.

Implementing Transaction support in SAHANA

It is better to add transaction support by including a function or functions in “handler_db.inc” which will implement ADOdb API functions.

All developers can use those functions to implement transactions rather than maintaining arbitrary functions

If the process ends up with warning messages (eg:if MyISAM is chosen as the database engine instead of InnoDB in MySQL) there should be a way to display those warnings to the user.the SQL command “SHOW WARNINGS” may be very helpful


Navigation
QR Code
QR Code dev:database_transactions (generated for current page)