PHP ADODB Database Transaction

StartTrans( )
Start a monitored transaction. As SQL statements are executed, ADOdb will monitor for SQL errors, and if any are detected, when CompleteTrans() is called, we auto-rollback.

To understand why StartTrans() is superior to BeginTrans(), let us examine a few ways of using BeginTrans(). The following is the wrong way to use transactions:

$DB->BeginTrans();
$DB->Execute("update table1 set val=$val1 where id=$id");
$DB->Execute("update table2 set val=$val2 where id=$id");
$DB->CommitTrans();

because you perform no error checking. It is possible to update table1 and for the update on table2 to fail. Here is a better way:

$DB->BeginTrans();
$ok = $DB->Execute("update table1 set val=$val1 where id=$id");
if ($ok) $ok = $DB->Execute("update table2 set val=$val2 where id=$id");
if ($ok) $DB->CommitTrans();
else $DB->RollbackTrans();

Another way is (since ADOdb 2.0):

$DB->BeginTrans();
$ok = $DB->Execute("update table1 set val=$val1 where id=$id");
if ($ok) $ok = $DB->Execute("update table2 set val=$val2 where id=$id");
$DB->CommitTrans($ok);

Now it is a headache monitoring $ok all over the place. StartTrans() is an improvement because it monitors all SQL errors for you. This is particularly useful if you are calling black-box functions in which SQL queries might be executed. Also all BeginTrans, CommitTrans and RollbackTrans calls inside a StartTrans block will be disabled, so even if the black box function does a commit, it will be ignored.

$DB->StartTrans();
CallBlackBox();
$DB->Execute("update table1 set val=$val1 where id=$id");
$DB->Execute("update table2 set val=$val2 where id=$id");
$DB->CompleteTrans();

Note that a StartTrans blocks are nestable, the inner blocks are ignored.
CompleteTrans($autoComplete=true)
Complete a transaction called with StartTrans(). This function monitors for SQL errors, and will commit if no errors have occured, otherwise it will rollback. Returns true on commit, false on rollback. If the parameter $autoComplete is true monitor sql errors and commit and rollback as appropriate. Set $autoComplete to false to force rollback even if no SQL error detected.
FailTrans( )
Fail a transaction started with StartTrans(). The rollback will only occur when CompleteTrans() is called.
HasFailedTrans( )
Check whether smart transaction has failed, eg. returns true if SQL execution failed, or FailTrans() was called. If not within smart transaction, returns false.
SetTransactionMode($mode )
SetTransactionMode allows you to pass in the transaction mode to use for all subsequent transactions for that connection session. Note: if you have persistent connections and using mysql or mssql, you might have to explicitly reset your transaction mode at the beginning of each page request. This is only supported in postgresql, mssql, mysql with InnoDB and oci8 currently. For example:

$db->SetTransactionMode("SERIALIZABLE");
$db->BeginTrans();
$db->Execute(...); $db->Execute(...);
$db->CommiTrans();

$db->SetTransactionMode(""); // restore to default
$db->StartTrans();
$db->Execute(...); $db->Execute(...);
$db->CompleteTrans();

Supported values to pass in:

• READ UNCOMMITTED (allows dirty reads, but fastest)
• READ COMMITTED (default postgres, mssql and oci8)
• REPEATABLE READ (default mysql)
• SERIALIZABLE (slowest and most restrictive)

You can also pass in database specific values such as 'SNAPSHOT' for mssql or 'READ ONLY' for oci8/postgres.
See transaction levels for PostgreSQL, Oracle, MySQL, and MS SQL Server.