PL/SQL Transactions   «Prev  Next»

Lesson 1

Controlling PL/SQL Transactions

In this module, you will discover key concepts and programming techniques involving database transactions. The two main concepts describe
  1. how data integrity and
  2. record locking fit in with PL/SQL sub-programs.
You will then learn how to code PL/SQL to effectively control data integrity and record locking.

Module objectives


By the end of this module, you will know how to:
  1. Describe how data integrity is compromised
  2. Identify when each type of locking is used
  3. Incorporate COMMIT, ROLLBACK, and SAVEPOINT commands within PL/SQL
  4. Describe how to create explicit locks with a cursor or a table lock

Robust Transaction Model

The Oracle database provides a robust transaction model, as you might expect from a relational database. Your application code determines what constitutes a transaction, which is the logical unit of work that must be either saved with a COMMIT statement or rolled back with a ROLLBACK statement. A transaction begins implicitly with the first SQL statement issued since the last COMMIT or ROLLBACK (or with the start of a session), or continues after a ROLLBACK TO SAVEPOINT. PL/SQL provides the following statements for transaction management
  1. COMMIT : Saves all outstanding changes since the last COMMIT or ROLLBACK, and releases all locks.
  2. ROLLBACK: Reverses the effects of all outstanding changes since the last COMMIT or ROLLBACK, and releases all locks.
  3. ROLLBACK TO SAVEPOINT: Reverses the effects of all changes made since the specified savepoint was established, and releases locks that were established within that range of the code.
  4. SAVEPOINT: Establishes a savepoint, which then allows you to perform partial ROLLBACKs.
  5. SET TRANSACTION: Allows you to begin a read-only or read-write session, establish an isolation level, or assign the current transaction to a specified rollback segment.
  6. LOCK TABLE: Allows you to lock an entire database table in the specified mode. This overrides the default row-level locking usually applied to a table.
These statements are explained in more detail in the following sections.
The next lesson describes data integrity.