PHP OCI extension for Oracle: Execution Mode

11 05 2010

In the PHP world it is common to use an open source database like MySQL for persisting data. But this post is about a less common used database – Oracle – and especially the execution mode found in the PHP OCI extension for Oracle.

The PHP OCI extension for Oracle provides two execution modes: OCI_DEFAULT and OCI_COMMIT_ON_SUCCESS. The default execution mode is OCI_COMMIT_ON_SUCCESS and this mode will automatically commit any (successful) SQL statement. The OCI_DEFAULT mode is mostly used when multiple statements are executed in one batch and should be committed (or rolled back) as one transaction.

Although most PHP developers (and PHP frameworks like Zend Framework) are using the OCI_COMMIT_ON_SUCCESS execution mode as their default mode, I personally think that you should not use this mode: Using the OCI_COMMIT_ON_SUCCESS mode will auto-commit any successful SQL statement, even when a SELECT statement is executed!


When selecting data in Oracle the results will probably come from the Shared Global Area (SGA memory). Only when the requested data does not reside in the SGA some file I/O is needed to fetch the missing data from disk to the SGA.

Now I don’t know why anybody would want to commit a SELECT statement (no data modifications are made), using the auto-commit will probably have an effect on the total performance of the system.

Below is an example proofing that a select statement in auto-commit execution mode is issuing a transactional commit. For the example explained below the following software is used:

The following query describes the table definition used:

create table execution_mode
( mydate date
, some_text varchar2(200)
);

And here is the PHP script (Example.php):
<?php
$conn = oci_connect('username', 'password', '//localhost/XE') or die;
$data = 'Hello world';
// Insert a record
$sql = "INSERT INTO execution_mode
(mydate, some_text)
VALUES
(SYSDATE, :text)";
$stmt = oci_parse($conn, $sql);
oci_bind_by_name($stmt, ':text', $data, 200);
// Use OCI_DEFAULT execution mode,
// when failure occurs rollback any changes
if ( !oci_execute($stmt, OCI_DEFAULT) ) {
oci_rollback($conn);
echo "Transaction failed\n";
exit(1);
}
// Determine total records by select count(*)
$sql = "SELECT COUNT(*) AS num_entries FROM execution_mode";
$stmt = oci_parse($conn,$sql);
// Bind count to $num_entries variable
oci_define_by_name($stmt, "NUM_ENTRIES", $num_entries);
// Whoops forgot OCI_DEFAULT, so OCI_COMMIT_ON_SUCCES is used!
oci_execute($stmt);
// Fetch data
oci_fetch($stmt);
// Issue a rollback
oci_rollback($conn);
// Too bad data has already been commited by the select count(*) statement!
?>

Checking the data in table execution_mode with SQL*Plus reveals that the record is inserted and commited:


Conclusion: always use OCI_DEFAULT as your default execution mode. Commit (or rollback) your transactions manually when and where appropriate.

Vote for my proposed solution to enable to set the default execution mode within the Zend Framework Oracle database adapter!


Actions

Information

One response

7 08 2013
Louis Cunningham

Hello there! I could have sworn I’ve been to this blog before but after reading through some of the post I realized it’s
new to me. Anyways, I’m definitely glad I found it and I’ll be bookmarking and checking back frequently!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: