The Oracle + PHP Cookbook

Performing Transactions with Oracle and PHP
by Harry Fuecks

How to manage many-to-many relationships reliably using the power of the Oracle Database.

Downloads for this article:
 Oracle Database 10g
 Zend Core for Oracle
 Apache HTTP Server 1.3 and later

Published November 2005

Transactions allow you to make changes to your database reliably and consistently and are critical if your application has to manage many-to-many relationships or execute multiple queries as a batch.

This "Oracle+PHP Cookbook" HowTo explains the need for transactions and examines how to use Oracle's transaction services through PHP's OCI extension, highlighting some of the potential "gotchas" along the way.

The Need for Transactions

Imagine you maintain a weblogging application which uses Oracle to store entries. Part of your database schema defines a table for "blogs" and another for "categories," which has a many-to-many relationship with "blogs" via a table "blogs_to_categories". The following queries describe the tables:

CREATE SEQUENCE blog_id_seq
INCREMENT BY 1;
/

CREATE TABLE blogs (
    id NUMBER PRIMARY KEY,
    title VARCHAR2(200),
    date_published DATE,
    text CLOB
);
/

CREATE SEQUENCE category_id_seq
INCREMENT BY 1;
/

CREATE TABLE categories (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30) UNIQUE
);
/

CREATE TABLE blogs_to_categories (
    blog_id INTEGER NOT NULL
    REFERENCES blogs(id),
    category_id INTEGER NOT NULL
    REFERENCES categories(id),
    PRIMARY KEY (blog_id, category_id)
);
/
For someone using the blog, to publish a blog entry they might assign it to the categories "php," "programming," and "web." Meanwhile, someone reading the blog would have the option of viewing entries by category instead of the default "by date."

Now, if the blog author wants to update the categories assigned to an existing entry—perhaps adding it to the category "oracle" while removing it from the category "web," which they have decided is too general—it would involve deleting and inserting rows from the blogs_to_categories table.

In terms of SQL this might look something like:

-- Delete all existing category links to this blog entry
DELETE FROM blogs_to_categories WHERE blog_id = 5

-- Insert a single link between a blog and a category
INSERT INTO blogs_to_categories
    (blog_id, category_id)
    VALUES
    (5, 2)
The insert query would be repeated once for each category under which the blog entry is filed.

But what if, after the delete query is executed, some failure occurs between the web server and the database, preventing the inserts from taking place? In that case, you now have a blog entry that has lost all its categories.

To prevent this problem, the statements need to be executed as a transaction, which instructs Oracle to handle multiple statements as a single entity.

ACID Transactions

The term ACID, when applied to databases, defines the properties the database must have to handle transactions properly. Loosely described, these are:

  • Atomicity—the transaction must either complete entirely or not complete at all; partial completion of transactions is not permitted.
  • Consistency—database constraints must be respected at all times.
  • Isolation—multiple transactions should not affect each other's data. For example, you don't want to allow transaction A to delete a category if transaction B is in the process inserting into the blogs_to_categories table.
  • Durability—if a transaction is completed (committed) the data must not be lost (should be resilient to failure).
A note about terminology here: To commit a transaction means to complete the transaction, applying and changes to the data that were made by the transaction. To rollback a transaction means ending the transaction and undoing any changes it made so the database returns to the state it was in before the transaction began. In other words, on error, you rollback the transaction, while on success, you commit it.

By using a transaction, you can reliably execute multiple queries knowing there is no risk of data loss due to system failure, while issues with concurrency are being managed. In particular, when considering concurrent access, Oracle will continue to allow "reads" to be made by other users on rows which are being written to, at the same time, by a transaction. The data seen by users issuing read statements will be unmodified: that is, despite any INSERT or UPDATE statements already made inside the transaction, they will continue to "see" the original data until the transaction is committed and the changes are applied. In other words, writers don't block readers, thereby avoiding performance bottlenecks.

Oracle Transactions with PHP

The PHP extension for Oracle has two modes of behavior as far as transactions are concerned. The default "autocommit" mode automatically performs a commit after every statement executed (that is, after every call to the oci_execute() function).

The "commit mode" is controlled by an optional second argument you pass to OCIExecute(), which is a constant and can be either OCI_COMMIT_ON_SUCCESS (the default autocommit behavior) or OCI_DEFAULT, which instructs PHP to wait for explicit calls to oci_commit() or oci_rollback() .

As a simple example, try to put some data in the categories table and see what happens when you violate a constraint in doing so:

<?php
$conn = oci_connect('SCOTT','TIGER') or die;

// Some categories
$categories = array(
    'php',
    'oracle',
    'programming',
    'web',
    'this string is too long for the name column'
);

$sql = "INSERT INTO categories
        (id, name)
        VALUES
        (category_id_seq.nextval,:category)
        ";

$stmt = oci_parse($conn,$sql);

foreach ( $categories as $category ) {
    
    oci_bind_by_name($stmt,':category', $category,30);
    
    // Note the OCI_DEFAULT parameter
    if ( !oci_execute($stmt,OCI_DEFAULT) ) {
    
        // If we have a problem, rollback then die
        oci_rollback($conn);
        die;
    }

}

// If we got this far, it's OK to commit
oci_commit($conn);// etc.
?>
In its current form, this example will fail because the last category it tries to insert violates the constraint of 30 characters for the name column. You should see an error similar to the following:
                               
Warning: oci_execute() [
                              
function.oci_execute]: OCIStmtExecute: 
ORA-01461: can bind a LONG value only for insert into a LONG
column in  
                              
/home/harry/public_html/create_categories.php on  
                              
line
25
                            
If you now try to select the contents of the categories table, you will see that none of the categories were inserted. Try removing the OCI_DEFAULT argument that's passed to oci_execute() in the above script, and what happens? Removing the category name that is too long and re-running this script will insert the records.

Note that whenever you call oci_execute(), from the point where a transaction began up to the point where you explicitly commit or rollback, make sure to pass the OCI_DEFAULT constant. If you fail to do so, oci_execute() with use its default behavior committing not just that single query but all preceding queries from the point where the transaction began. One important exception here is DDL statements (such as those beginning CREATE, DROP and ALTER) will always autocommit, no matter which constant you pass to oci_execute().

Note also that when a PHP script terminates execution, any outstanding transactions will be rolled back by Oracle automatically. With the default autocommit behavior, there's usually no need to consider this approach, but if your script hits something like a memory or time limit and terminates abruptly, you should be aware that open transactions are rolled back. This applies to temporary as well as persistent connections.

The following example illustrates a common problem that can occur if you're not careful about executing queries as part of a single unit of work. Using sample data that might represent a POST from an HTML form, the script below executes three statements. First it inserts a row into the blogs table, then it gets a count on the number of rows in the table, for feedback to the user. In executing the count query, it fails to pass OCI_DEFAULT to oci_execute(). Finally it attempts to insert two category IDs into the blogs_to_categories table, to be associated with the blog entry. One of the category IDs does not exist in the categories table, so Oracle raises an error about the referential constraint violation.

<?php
$conn = oci_connect('SCOTT','TIGER') or die;

/* Start by inserting a blog entry */

// An array representing a submitted blog entry
$blog = array(
    'id'=>NULL,
    'title'=>'Hello World!',
    'text'=>'This is my first blog entry',
    'categories' => array(1,999) // Note second ID does not exist
);

$sql = "INSERT INTO blogs
    (id,title,date_published,text)
    VALUES
    (blog_id_seq.nextval,:title,SYSDATE,:text)
    RETURNING
        id INTO :id ";

$stmt = oci_parse($conn,$sql);

oci_bind_by_name($stmt,':title', $blog['title'],200);
oci_bind_by_name($stmt,':id', $blog['id'],32);
oci_bind_by_name($stmt,':text', $blog['text']);


// Note OCI_DEFAULT - begin a transaction
if ( !oci_execute($stmt,OCI_DEFAULT) ) {
    oci_rollback($conn);
    exit(1);
}

/* Now count the total number of blog entries */



$sql = "SELECT COUNT(*) AS num_entries FROM blogs";

$stmt = oci_parse($conn,$sql);

// Automatically populates the $num_entries variable
oci_define_by_name($stmt,"NUM_ENTRIES",$num_entries);

// Whoops forgot the OCI_DEFAULT!
oci_execute($stmt);

oci_fetch($stmt);

$result = "Blog added. There are now $num_entries blogs\n";

/* Now insert the categories related to the entry */

$sql = "INSERT INTO blogs_to_categories
    (blog_id, category_id)
    VALUES
    (:blog_id, :category_id)";

$stmt = oci_parse($conn,$sql);

// Loop through the categories
foreach ( $blog['categories'] as $category_id ) {

    oci_bind_by_name($stmt,':blog_id', $blog['id'],32);
    oci_bind_by_name($stmt,':category_id', $category_id,32);
    
    // Note OCI_DEFAULT again
    if ( !oci_execute($stmt,OCI_DEFAULT) ) {
        oci_rollback($conn);
        exit(1);

    }
    
}

oci_commit($conn);
echo $result

?>
In executing the SELECT statement, to count the rows in the blog table, this script tells Oracle to autocommit. In doing so, both the SELECT and the previous INSERT, which began a transaction, are completed at once. For the third statement it begins a fresh transaction and on encountering the constraint violation rolls back the inserted category IDs. As a result, the blog entry is inserted but its categories have been lost. The three queries should, instead, have been executed as a single unit of work; on encountering the constraint violation, all inserts rolled back and the user informed that they need to correct the categories they supplied and submit the form again.

To handle the transaction correctly the OCI_DEFAULT constant should have been passed to all three oci_execute() calls. The change below fixes the problem:

figure 1


Transaction Isolation and Connections

When a transaction begins, Oracle takes a partial "snapshot" of the database, known as a rollback segment, to allow a consistent view of the database to be available to other processes, while the transaction is in progress. This means that the process executing the transaction will see the database as being in a different state to other processes running outside of the transaction. In some rarer use cases you may wish to access data from the "external state" at the same time as executing a transaction. To achieve this goal you need to use the oci_new_connect() function, which guarantees you a new connection to the database, whereas repeated calls to oci_connect(), for example, will all return the same database connection resource. Statements executed across the new connection will be isolated transactions running in established connection.

The following script illustrates this:

<?php
$conn = oci_connect("SCOTT", "TIGER") or die;

$categories = array(
    'transactions',
    'sessions',
);

$sql = "INSERT INTO categories
        (id, name)
        VALUES
        (category_id_seq.nextval,:category)
        ";

$stmt = oci_parse($conn,$sql);

foreach ( $categories as $category ) {
    
    oci_bind_by_name($stmt,':category', $category,30);
    
    if ( !oci_execute($stmt,OCI_DEFAULT) ) {
        oci_rollback($conn);
        die;
    }

}

// Establish a new, temporary Oracle connection
$newSess = oci_new_connect("SCOTT", "TIGER") or die;

$sql = "SELECT COUNT(*) AS num_cats FROM categories";

$stmt = oci_parse($newSess,$sql);

oci_define_by_name($stmt,"NUM_CATS",$oldnum_cats);

// No need for OCI_DEFAULT here...
oci_execute($stmt);

oci_fetch($stmt);

// Logoff the session (technically not needed as it will be cleaned up anyway)
oci_close($newSess);

// Do the same for the primary session
$stmt = oci_parse($conn,$sql);

oci_define_by_name($stmt,"NUM_CATS",$newnum_cats);

// Here we do need OCI_DEFAULT
oci_execute($stmt,OCI_DEFAULT);

oci_fetch($stmt);

// If we got this far, it's OK to commit
oci_commit($conn);

echo "Before I had $oldnum_cats. Now I have $newnum_cats\n";
?>
The value of the variable $oldnum_cats, despite being obtained after the transaction was started, contains a count of rows in the categories table before the transaction began. This is because the session I created with OCINLogon() is isolated from the main session I am running the transaction in; it has a separate transaction context.

Conclusion

If you didn't already, you should now understand the need for transactions, allowing you to make changes to the database reliably and consistently. You should also understand how to execute transactions via PHP's OCI extension—bearing in mind potential issues with its default autocommit behavior—as well as how execute queries in separate transaction contexts.


Harry Fuecks [ http://www.phppatterns.com] is a well known PHP developer and writer, since discovering PHP in 1999. He has published numerous introductory and intermediate PHP articles via the Sitepoint Web developer's network, as well as writing The PHP Anthology (SitePoint).

Send us your comments