Transaction Statements in Oracle and PHP

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 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):

SQL> rollback;

Rollback complete.

SQL>

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.

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