Transaction Processing - Oracle DBA

As you've learned, constraints created on columns of a table help you to maintain integrity and consistency in the database at the statement level. Transactions go beyond individual INSERT or UPDATE statements and allow you to ensure that multiple DML statements against the database either all succeed or all fail.

Transaction A logical unit of work consisting of one or more SQL statements that must all succeed or all fail to keep the database in a logically consistent state. A transfer of funds from one bank account is a logical transaction, in that both the withdrawal from one account and the deposit to another account must succeed for the transaction to succeed.

From a DBA's perspective, the transaction concept is important to understand when allocating disk space. The more activity that occurs within a transaction, the greater the need for disk space to maintain read consistency in the database. If a user initiates a long-running SELECT statement, the table data seen by the user will appear to be unchanged, even if other users are subsequently making changes to the same rows while the SELECT statement is executing. As a result, additional disk space (known as undo or rollback space) must be allocated to hold both the old and new versions of the rows being read by one user and written to by another user.

Transactions begin with a single DML statement and end (successfully or unsuccessfully) when one of the following events occurs:

  • Either a COMMIT or ROLLBACK statement is executed. A COMMIT statement makes the changes to the table permanent, while the ROLLBACK undoes the changes to the table.
  • The user exits SQL*Plus or iSQL*Plus normally (automatic COMMIT).
  • A DDL (Data Definition Language) or DCL (Data Control Language) statement is executed (automatic COMMIT).
  • The database crashes (automatic ROLLBACK).
  • The SQL*Plus or iSQL*Plus session crashes (automatic ROLLBACK).

In addition, you can use SAVEPOINT to further subdivide the DML statements within a transaction before the final COMMIT of all DML statements within the transaction. SAVEPOINT essentially allows partial rollbacks within a transaction.

The COMMIT Statment

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 boss 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, pay increases and cuts must either all succeed or all fail.

Read Consistency A feature of the Oracle database that ensures a database reader (in a SELECT statement) will see the same data in a table regardless of changes made to the table by database writers that were initiated after the reader initiated the SELECT statement.

In the iSQL*Plus example shown here, Janice performs two pay cuts and one pay increase in a single transaction. If the second SELECT statement had not generated the total the boss wanted, she could have either executed additional UPDATE statements before doing a COMMIT or performed a ROLLBACK to undo the updates and start over again.

If the database had crashed after the second UPDATE statement, the results from all statements in the transaction would be removed from the database. The following statement in the example ensures that the total of the monthly salaries is the same before and after the updates:


The ROLLBACK Statement

The ROLLBACK statement allows you to change your mind about a transaction. It brings back the state of the tables to the state as of the last COMMIT statement or the beginning of the current transaction.

Janice is nearing the end of a busy day. She decides to perform one more task for the boss before leaving. She wants to remove some order detail items from the OE.ORDER_ITEMS table that are more than five years old, since the ORDERS table was recently purged of all orders more than five years old. She runs the DELETE statement as follows:

665 rows deleted.

Janice realizes that she forgot the WHERE clause in the DELETE, so she needs to get back the rows she accidentally deleted:

Another disaster averted. Now she won't need to restore the OE.ORDER_ ITEMS table from a backup.

The SAVEPOINT Statement

The SAVEPOINT statement allows you to discard a subset of the DML statements within a transaction since the SAVEPOINT was issued. The SAVEPOINT itself is named, and it can be referenced in the ROLLBACK statement, as follows:

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 example shows Janice using a savepoint to conditionally undo the DML statements since the savepoint was issued:

Only the REGIONS row with a REGION_ID of 5 is saved in the table after the COMMIT.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle DBA Topics