Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 33

Now that you have a good understanding of how queries and Data Manipulation Language (DML)
statements work, we will add some structure to these statements by grouping them into transactions
to enhance the logical consistency of your database. Using a classic example, if your PHP application handles automatic teller machine (ATM) transactions, you want to make sure that the withdrawal of funds from your savings account and the subsequent deposit of those funds into your checking account both succeed. Otherwise, both operations must be canceled, or rolled back, to ensure that the total dollar amounts of your checking and savings accounts remain the same.
In this chapter, we first explain the terminology surrounding transaction processing. Next, we give you an overview of how transactions work in an Oracle database using the COMMIT, ROLLBACK, and SAVEPOINT statements, along with some examples in SQL command line. Finally, we show you how transactions work in PHP, using oci_execute(), oci_commit(), and oci_rollback().

Using Transactions: Overview

For starters, you need to know what a transaction is. A transaction is an ordered group of database operations that are perceived as a single unit. A transaction is deemed successful if all operations in the group succeed, and is deemed unsuccessful if even a single operation fails. If all operations
complete successfully, that transaction will be committed, and its changes will be made available to all other database processes. If an operation fails, the transaction will be rolled back, and the effects of all operations comprising that transaction will be annulled.
Any changes effected during the course of a transaction will be made solely available to the thread owning that transaction and will remain so until those changes are committed. This prevents other threads from potentially making use of data that may soon be negated due to a rollback, which would result in a corruption of data integrity.
Transactional capabilities are a crucial part of enterprise databases because many business
processes consist of multiple steps. Take for example a customer’s attempt to execute an online purchase. At checkout time, the customer’s shopping cart will be compared against existing inven- tories to ensure availability. Next, the customer must supply his billing and shipping information, at which point his credit card will be checked for the necessary available funds and then debited. Next, product inventories will be deducted accordingly, and the shipping department will be notified of the pending order. If any of these steps fail, none of them should occur. Imagine the customer’s dismay that his credit card has been debited even though the product never arrived because of inad- equate inventory. Likewise, you wouldn’t want to deduct inventory or even ship the product if the credit card is invalid or if insufficient shipping information is provided.

591

On more technical terms, a transaction is defined by its ability to follow four tenets, embodied in the acronym ACID. These four pillars of the transactional process are defined here:

• Atomicity: All steps of the transaction must be successfully completed; otherwise, none of the steps will be committed.
• Consistency: All integrity constraints must be satisfied for each operation within the transac- tion; even if integrity checking is deferred, all integrity constraints must be satisfied at commit time.
• Isolation: The steps carried out by any as-of-yet incomplete transaction must remain isolated from the system until the transaction has been deemed complete.
• Durability: All committed data must be saved by the system in such a way that in the event of a system failure the data can be successfully returned to a valid state.

As you learn more about Oracle Database XE’s transactional support throughout this chapter, you will understand that these tenets must be followed to ensure database integrity.

Understanding Transaction Components Transactions using SQL command line begin with any single DML statement and end (either successfully or unsuccessfully) when one of the following events occurs:

• Either a COMMIT or a ROLLBACK statement is executed. A COMMIT statement makes the changes to the table permanent, while a ROLLBACK undoes the changes to the table.
• The user exits the SQL command-line interface session using the EXIT command to terminate the SQL command-line session (automatic COMMIT statement executed).
• The user exits the SQL Commands GUI tool with the Autocommit box unchecked by closing the browser or clicking the Logout link (automatic ROLLBACK statement executed).
• A DDL (Data Definition Language) or DCL (Data Control Language) statement is executed
(automatic COMMIT statement executed).

• The database crashes (automatic ROLLBACK statement executed).

• The SQL command-line session crashes (automatic ROLLBACK statement executed).

In addition, you can use the SAVEPOINT statement to subdivide the DML statements further within a transaction before you issue the final COMMIT statement for all DML statements within the transaction. The SAVEPOINT statement essentially allows partial rollbacks within a transaction. We show you the statements for these events in the following sections; later in this chapter, we cover the equivalent steps using PHP scripts.

Explicit COMMIT Statement

There are many situations when you want a given set of DML statements—a transaction—to fail or succeed, ensuring data integrity. Suppose that the management team decides that to keep the salary budget the same next year, all employees who get raises must be offset by employees who get pay cuts. When the updates are made to the database, it is important that the total salary paid out every month remains constant; therefore, the total of the pay increases, and pay cuts must either all succeed or all fail. You believe that by using one of the Oracle Database XE GUI tools you can perform these salary adjustments as a transaction; you can use either the SQL Scripts tool or the SQL Commands tool to perform these tasks.

If you use the SQL Scripts tool shown in Figure 33-1, you perform two pay cuts and three pay increases in a single transaction with the total salary paid remaining constant, which was your original goal; however, you cannot use the COMMIT statement in the SQL Scripts tool. Instead, at the comple- tion of the script, the SQL Scripts tool automatically executes a COMMIT. Although this appears to be an implicit COMMIT (see the next section), it is actually an explicit COMMIT performed on your behalf by the Scripts Editor. If the second SELECT statement had not generated the original total, the payroll employee would have to execute additional UPDATE statements to ensure the final total is the same as the first total rather than just throwing out the entire transaction and restoring the table’s rows to their initial values and starting over.

Figure 33-1. Using the Oracle Database XE Scripts Editor to update salaries

In situations where you want more control over when a transaction completes, you can use SQL Commands to control the transaction by unchecking the Autocommit checkbox (shown in Figure 33-2) and executing the SELECT and UPDATE statements one at a time until you obtain the desired results, at which point you would execute an explicit COMMIT. If you do not uncheck the Autocommit checkbox, the changes you make are saved to the database permanently after each UPDATE statement.
If the database crashes after the second UPDATE statement (Figure 33-1) or after you run the UPDATE statement by itself using SQL Commands (Figure 33-2), the results from all statements in the transaction would be removed from the database. The following statement (run twice, once at the beginning of the script and once at the end) ensures that the total of the monthly salaries is the same before and after the updates:

select sum(salary) from hr.employees;

Once you finish your updates and the total is the same as the first time you calculate it, you can run the COMMIT statement to save the results permanently in the database.

Figure 33-2. Using SQL Commands to process a transaction

Implicit COMMIT Statement

A common mistake you can make when building a transaction is to perform an implicit COMMIT—in other words, a SQL statement that automatically commits your changes, usually when you do not want to. All DDL statements perform an implicit COMMIT, such as CREATE TABLE or ALTER INDEX. As a general rule, then, do not perform DDL within your transactions. In a typical OLTP (online transac- tion processing) environment, this should not be a problem; a well-designed end user application will only have DML statements. Implicit COMMIT statements are more of a concern when you are issuing statements interactively. Then you must take care and pay attention to what you are doing. If you must perform any DDL for a user application, do it before or after a transaction, not during.

Explicit ROLLBACK Statement

Up to now, we have not used ROLLBACK explicitly; implicit ROLLBACK statements usually occur because of some kind of database, network, or client failure. To elaborate further, an explicit ROLLBACK is a great way to change your mind if you make a change to a database table and you do not want to make the change permanent. Running an explicit ROLLBACK statement applies to all DML activity since the last COMMIT or ROLLBACK or since the session began. It can roll back 1 or 1,000 DML state- ments, limited only by the size of your UNDO tablespace (we cover the UNDO tablespace in Chapter 29). You can also perform a partial ROLLBACK; we tell you more about this in the next section on the SAVEPOINT statement.
For example, your last task for the day is to delete employee number 106 from the database; you run this DELETE statement in SQL*Plus:

SQL> delete from employees;

107 rows deleted.

Oops, you forgot the WHERE clause, so all of the employees are deleted. This is not the desired result. However, since you are using Oracle Database XE, you can roll back the deletions using ROLLBACK (and likely save your job in the process):

SQL> rollback;

Rollback complete.

SQL>

The EMPLOYEES table is now back to its original state. Any other DML statements executed before

the DELETE without an intervening ROLLBACK or COMMIT will also be rolled back. This time you remember the WHERE clause:

SQL> delete from employees where employee_id = 106;

1 row deleted. SQL> commit;
Commit complete.

SQL>

During your lapse of judgment, probably due to not enough coffee, your users are none the

wiser; during the entire session until the COMMIT, Oracle provided a read-consistent view of the EMPLOYEES table. In other words, your users were not aware that the EMPLOYEES table was empty and would not see your changes, unless you were to perform a COMMIT after the first DELETE.

The SAVEPOINT Statement

Using the SAVEPOINT statement, you can mark a spot in the middle of a transaction to which you might wish to roll back. A SAVEPOINT statement allows you to undo part of a transaction rather than all of it. Following is the syntax to use in creating a savepoint:

SAVEPOINT savepoint_name;

You uniquely name the savepoint and then may subsequently reference it in a ROLLBACK state- ment as follows:

ROLLBACK TO SAVEPOINT savepoint_name;

Regardless of how many savepoints exist within a transaction, a ROLLBACK statement without a SAVEPOINT reference will automatically roll back the entire transaction. The following SQL*Plus example uses a savepoint to conditionally undo the DML statements since the SAVEPOINT statement was issued. First, the example creates a new region in the REGIONS table because you are opening a branch office in Antarctica:
SQL> insert into regions (region_id, region_name)
2 values (5, 'Antarctica');

1 row created.

SQL> savepoint new_region;

Savepoint created.

The savepoint NEW_REGION may come in handy later if you need to roll back the insertion of the new countries you will be adding in the next few statements:

SQL> insert into countries (country_id, country_name, region_id)
2 values ('NA', 'Northern Antarctica', 5);

1 row created.

SQL> savepoint new_country_1; Savepoint created.
SQL> insert into countries (country_id, country_name, region_id)
2 values ('SA', 'Southern Antarctica', 5);

1 row created.
SQL> savepoint new_country_2; Savepoint created.
After performing these two inserts, you realize that you have a geographic anomaly on your hands, and that for now you are just going to add one country to the COUNTRIES table but keep the new region in the REGIONS table:

SQL> rollback to new_region; Rollback complete.
SQL> insert into countries (country_id, country_name, region_id)
2 values ('IA', 'Inner Circle of Antarctica', 5);

1 row created.

Just to double-check your work before you run the COMMIT statement, you query the REGIONS and
COUNTRIES tables:

SQL> select * from regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
5 Antarctica

SQL> select * from countries;

CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ---------- AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
CA Canada 2
CH Switzerland 1
CN China 3
DE Germany 1
DK Denmark 1
EG Egypt 4
FR France 1

CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ---------- HK HongKong 3
IA Inner Circle of Antarctica 5
IL Israel 4
IN India 3
IT Italy 1
JP Japan 3
KW Kuwait 4
MX Mexico 2
NG Nigeria 4
NL Netherlands 1
SG Singapore 3

CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ---------- UK United Kingdom 1
US United States of America 2
ZM Zambia 4
ZW Zimbabwe 4

26 rows selected.

SQL>

Now that everything looks good, you commit the single-row change to the REGIONS table, and

the single-row change to the COUNTRIES table:

SQL> commit; Commit complete.

Performing Transactions Using PHP

Using the transactional features in OCI8 is fairly straightforward, relying on a small handful of func- tion calls: oci_connect(), oci_close(), oci_execute(), oci_commit(), and oci_rollback(). You have seen three of these function calls before; in this section, we revisit them from a transactional perspective.
When you connect to the database using oci_connect(), you are implicitly starting a transac- tion. None of the parameters of oci_connect(), however, dictate how your transactions are processed;

the oci_execute() function call is where all the transactional fun begins. As you may remember from
Chapter 32, the syntax of oci_execute() is as follows:

bool oci_execute ( resource statement [, int mode] )

The mode parameter is typically one of two values:

• OCI_DEFAULT: The DML statement does not automatically execute a COMMIT statement upon successful execution.
• OCI_COMMIT_ON_SUCCESS: The DML statement will automatically perform a COMMIT upon successful execution of the statement.

The default value for mode is OCI_COMMIT_ON_SUCCESS, which is a bit counterintuitive; you might expect that the default would be OCI_DEFAULT. This is one of the things about oci_execute() that you just have to remember: the default is not OCI_DEFAULT. In all of the examples up to this point, you used oci_execute() without the second parameter, and therefore automatically committed any changes to the database by the statement specified in the first parameter of oci_execute().
When you are done with the transaction, you can either commit the transaction with oci_commit() or roll it back with oci_rollback(). Both functions use the connection handle from oci_connect(); as a result, you can have several ongoing transactions within the same PHP script—one active trans- action per connection handle.
Revisiting the example earlier in the chapter, we use a PHP script to update two more salaries in
the database as a single transaction. In the example in Listing 33-1, you increase the monthly salary by $1,000 for employee number 100 and decrease the monthly salary by the same amount for employee number 102.

Listing 33-1. PHP Code to Perform Two Salary Updates As a Single Transaction (db_update_salary.php)

<?php

// Update salaries: increase one salary and decrease
// another as a single transaction.

// Connect to the server and select the database
$c = @oci_connect('hr', 'hr', '//localhost/xe')
or die("Could not connect to Oracle server");

// Show salaries before update. echo "Previous salaries: <br />";
$s = oci_parse($c, 'select employee_id, last_name, salary from employees'
. ' where employee_id in (100,102)');

oci_execute($s, OCI_DEFAULT);
while ($res = oci_fetch_array($s)) {
echo $res['EMPLOYEE_ID'] . ' -- ' . $res['LAST_NAME']
. ': ' . $res['SALARY'] . "<br />";
}

// add $1000 to first employee's salary

$s = oci_parse($c, "update employees
set salary = salary + 1000 where employee_id = 100");

$result = oci_execute($s, OCI_DEFAULT);

// subtract $1000 from second employee's salary

$s = oci_parse($c, "update employees
set salary = salary - 1000 where employee_id = 102");

$result = oci_execute($s, OCI_DEFAULT);

// end of transaction oci_commit($c);

// Show salaries after update.

echo "<br /><br /><br />New salaries: <br />";
$s = oci_parse($c, 'select employee_id, last_name, salary from employees'
. ' where employee_id in (100,102)');

oci_execute($s, OCI_DEFAULT);
while ($res = oci_fetch_array($s)) {
echo $res['EMPLOYEE_ID'] . ' -- ' . $res['LAST_NAME']
. ': ' . $res['SALARY'] . "<br />";
}

// done. If there are any uncommitted transactions, oci_close()
// will roll back.

oci_close($c);
?>

Any script or database failure up until the oci_commit() statement will force the execution of a
ROLLBACK statement for both UPDATE statements. The output of this script is in Figure 33-3. Notice that you must use OCI_DEFAULT even if you are only displaying rows, otherwise the commit will occur prematurely. In the example in Listing 33-1, you run a SELECT before the transaction begins, and you do not run another SELECT until after the transaction is complete. Using OCI_DEFAULT is not required for the SELECT statements in this example, but it is good practice to use OCI_DEFAULT every time you run oci_execute() in a transaction-based application to ensure you do not accidentally perform a COMMIT statement when you do not want to.

■Note There is no explicit SAVEPOINT in OCI8 or PDO. It is, however, fully supported in PEAR/MDB2 starting with
version 1.0.0.

Finally, oci_close() or the termination of the PHP script will automatically roll back any uncom- mitted transactions; as a result, it is good programming practice to explicitly commit or roll back your transactions, otherwise oci_close() will roll them back when you might not want to.

Figure 33-3. Results of salary update transaction processing

Summary

Now that you’ve read this chapter, you should possess a thorough understanding of transactions, how they’re implemented in Oracle Database XE, and how to incorporate them into your PHP appli- cations. Using transactions in Oracle boils down to three statements: COMMIT, ROLLBACK, and SAVEPOINT. Some statements, such as Oracle DDL statements, force the execution of a COMMIT statement; these should be avoided when you’re in the middle of a transaction. From a PHP perspective, the OCI8 transaction-related function calls give you all the tools you need to create robust Web applications.
Database transactions are of immense use when modeling your business processes because
they help to ensure the integrity of your organization’s most valuable asset: its information and the relationships between different categories and hierarchies of information in your database such as orders with order items and employees within departments. If you use database transactions prudently, they are a great asset when building database-driven applications.
In the next chapter, we delve more deeply into the SELECT statement and how to craft a query for any requirement, including, of course, how to use the results of these queries in a PHP application.