HomeOracle Transaction Statements in Oracle and PHP
Transaction Statements in Oracle and PHP
In this second part of a two-part series on how to properly handle transactions with Oracle databases, you'll learn how to use rollback statments, the savepoint statement, and how to complete transactions with PHP. This article 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).
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 implicitCOMMIT, such asCREATE TABLEorALTER INDEX. As a general rule, then, do not perform DDL within your transactions. In a typical OLTP (online transaction processing) environment, this should not be a problem; a well-designed end user application will only have DML statements. ImplicitCOMMITstatements 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; implicitROLLBACKstatements usually occur because of some kind of database, network, or client failure. To elaborate further, an explicitROLLBACK 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 explicitROLLBACKstatement applies to all DML activity since the lastCOMMITorROLLBACKor since the session began. It can roll back 1 or 1,000 DML statements, limited only by the size of yourUNDOtablespace (we cover theUNDO tablespace in Chapter 29). You can also perform a partialROLLBACK; we tell you more about this in the next section on theSAVEPOINT statement.
For example, your last task for the day is to delete employee number 106 from the database; you run thisDELETEstatement in SQL*Plus:
SQL> delete from employees;
107 rows deleted.
Oops, you forgot theWHERE 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 usingROLLBACK (and likely save your job in the process):
TheEMPLOYEEStable is now back to its original state. Any other DML statements executed before theDELETE without an interveningROLLBACKorCOMMITwill also be rolled back. This time you remember theWHERE clause:
SQL> delete from employees where employee_id = 106;
1 row deleted.
During your lapse of judgment, probably due to not enough coffee, your users are none the wiser; during the entire session until theCOMMIT, Oracle provided a read-consistent view of theEMPLOYEEStable. In other words, your users were not aware that theEMPLOYEEStable was empty and would not see your changes, unless you were to perform aCOMMITafter the firstDELETE.