Do you know about Database Transactions

The power of Information Technology has reached to many new areas for past few decades.software systems hold a large portion of that production
Almost all those systems have a back end with a database..but do all developers concern about the integrity and the robustness.

To implement integrity among vital database tables you should implement database transactions.

what is a transaction -
A transaction is a unit of activity wchich will be done completely or not at all

what a transaction does -
Normally we have to update several database tables to complete a single task.what happen if one table fails to accomodate relevant data while other tables accept their data..the whole system become unstable incorrect.
If we enable transactions and execute all necessary queries within a transaction it guarantees that all queries will execute or none of them do

Characteristics of a transaction -
A transaction comprices "ACID" properties

*Atomic - transaction complete fully or not at all

*Consistency - database enters from one consistent stage to another consistent stage

*Isolation - elements in a transaction isolated until it is completed

*Durability -once the user has been notified of success, the transaction will persist


Simple mysql transaction example -

start transaction;
update account set balance = balance - 1000 where number = 2;
update account set balance = balance + 1000 where number = 1;
commit;

in any error we could have use "rollback" keyword to completely rollback the transaction

Database support for transaction-

Almost all database supports transactions.but mysql MyISAM database engine does not support transaction..luckily mysql InnoDB engine supports transaction.so if you work in a mysql environment and need transaction support you should use the innodb engine instead of the default MyISAM engine.

MySQL -ADOdb - PHP example with transactions -

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');

$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")');

if ($ok) $db->CommitTrans();
else $db->RollbackTrans();

?>

you should note that adodb need "mysqli" or "mysqlt" drivers instead of general "mysql" driver inorder to enforce transactions