Transactions and Databases

Properly grouped transactions can enhance the logical consistency of your database. This two-part series will give you an overview of how to use transactions with an Oracle database. It is excerpted from chapter 33 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

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 inventories 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 inadequate 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.

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:

  1. Atomicity: All steps of the transaction must be successfully completed; otherwise, none of the steps will be committed.
  2. Consistency: All integrity constraints must be satisfied for each operation within the transaction; even if integrity checking is deferred, all integrity constraints must be satisfied at commit time. 
  3. 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. 
  4. 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.

{mospagebreak title=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:

  1. 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. 
  2. The user exits the SQL command-line interface session using the EXIT command to terminate the SQL command-line session (automatic COMMIT statement executed). 
  3. 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). 
  4. A DDL (Data Definition Language) or DCL (Data Control Language) statement is executed (automatic COMMIT statement executed). 
  5. The database crashes (automatic ROLLBACK statement executed). 
  6. 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 completion 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

Please check back next week for the conclusion to this series.

Google+ Comments

Google+ Comments