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 theCOMMIT,ROLLBACK, andSAVEPOINT statements, along with some examples in SQL command line. Finally, we show you how transactions work in PHP, usingoci_execute(),oci_commit(), andoci_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:
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 transaction; 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.