- skip to content

# Database Abstraction Layer

### User Tools

- Login

### Site Tools

- Recent changes
- Media Manager
- Sitemap

### Sidebar

##### Resources

- Reference Manual
- User Guide
- The Data Dictionary
- ADOdb-xmlschema
- ADOdb Active Record
- Performance Monitoring
- Date/Time Library
- Session Management Library
- Unsupported Addons
- Coding Standards

##### Supported Databases

- Access
- ADO
- CSV
- Firebird
- Frontbase
- IBM DB2
- Informix
- Interbase
- LDAP
- Microsoft SQL Server
- MySQL
- Netezza
- ODBC
- ODBTP
- Oracle
- PDO
- Postgresql
- SAP ADS
- SAP ASE
- SAP Maxdb
- SAP Sqlanywhere
- Sqlite
- Sybase
- Plain Text
- Visual Foxpro

##### Other

- Feature Comparison Matrix
- Connection Matrix
- Include File Guide

v5:userguide:transactions

# Transaction Handling In ADOdb

## Overview

There are 2 ways of overriding the default transaction handling in ADOdb, Granular and Smart. Each way holds benefits but they cannot be mixed. Each method comprises a series of methods.

- Granular transactions allow full control of individual commits
- Smart transactions are easier to control

## Default Behaviour

MySQL myIsam tables are always granular, and ignore any transaction control methods coded. You cannot rollback or fail an update into that table type. Use InnoDB table types instead.

If ADOdb transaction handling is not used, then the transaction handling method is controlled by whatever is set by the database. Possible methods are:

- Automatically creating granular updates
- Automatically rolling back the transactions at the end of procedures
- Failing unless a transaction handling method is assigned on connection

## Granular Transactions

##### Available Methods

begintrans
rollbacktrans
completetrans

When using granular transactions, it is the duty of the programmer to check whether a transaction execution has succeeded or failed. This allows the code to branch as desired in the case of a transaction failure.

### Usage

    /*
    * Start transaction scope
    */
    $conn->beginTrans();

    /*
    * Execute some insert or update SQL
    */
    $ok = $conn->execute($sql);

    if ($ok)
       /*
       * The previous execution succeeded, do some more
       */
       $ok = $conn->execute($sql2);
    else
       /*
       * Branch, do some other work, then return
       */
    if (!$ok)
        /*
        * Test the last insertion, if not successful roll
        * back the entire transaction scope
        */
        $conn->rollbackTrans();
    else
        /*
        * Go ahead and commit
        */
        $conn->commitTrans();

## Smart Transactions

##### Available Methods

starttrans
failtrans
hasfailedtrans
completetrans

Smart Transactions are much easier to use in large procedures because you do not have to track the error status. You start a smart transaction by calling StartTrans():

    /*
    * Begin with startTrans
    */
    $conn->startTrans();

    /*
    * Execute some SQL
    */
    $conn->Execute($sql);

    /*
    * Execute some more SQL
    */
    $conn->Execute($Sql2);

    /*
    * Call completetrans. If any of the executions in the above
    * code have failed, the transactions will roll back automatically
    */
    $conn->completeTrans();

completetrans detects when an SQL error occurs, and will Rollback or Commit the transactions as appropriate.

You can force a rollback even if no error occured, using failtrans. Note that the rollback is done in completeTrans(), so any updates/inserts done after `failTrans()` is called will also be rolled back.

    $conn->startTrans();

    $conn->execute($sql);

    if (!CheckRecords())
        $conn->failTrans();

    $conn->Execute($Sql2);

    $conn->CompleteTrans();

You can also check if a transaction has failed, using hasfailedtrans, which returns true:

- if failtrans was called,
- There was an error in the SQL execution.

Make sure you call `hasFailedTrans()` before you call `completeTrans()`, as it is only works between StartTrans/CompleteTrans.

### Nesting Transactions

Smart Transactions are nestable, which means that you can have multiple groups of startTrans/completeTrans inside each other. This is extremely useful when writing re-usable classes, as the transaction scope is automatically directed to the outermost block.

The only caveat for nesting is that the startTrans/completeTrans pairs must match. If they do not, the database default behaviour for transaction processing will take control.

    /*
    * Outer block, takes control of transactions
    */
    $conn->startTrans();

    $conn->execute($sql);
    /*
    * This is ignored
    */
    $conn->startTrans();
    if (!CheckRecords())
        /*
        * This is applied to the outer block
       */
        $conn->failTrans();

    /*
    * This is ignored
    */
    $conn->completeTrans();

    $conn->execute($Sql2);
    /*
    * This completes the transaction scope
    */
    $conn->completeTrans();

### Savepoints

Savepoints are currently not supported.

v5/userguide/transactions.txt · Last modified: 2016/01/12 17:31 by mnewnham

### Page Tools

- Show pagesource
- Old revisions
- Backlinks
- Rename Page
- Back to top