Controlling PL/SQL Transactions
In this module, you will discover key concepts and programming techniques involving database transactions.
The two main concepts describe
You will then learn how to code PL/SQL to effectively control data integrity and record locking.
- how data integrity and
- record locking fit in with PL/SQL sub-programs.
By the end of this module, you will know how to:
- Describe how data integrity is compromised
- Identify when each type of locking is used
- Incorporate COMMIT, ROLLBACK, and SAVEPOINT commands within PL/SQL
- Describe how to create explicit locks with a cursor or a table lock
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
These statements are explained in more detail in the following sections.
- COMMIT : Saves all outstanding changes since the last COMMIT or ROLLBACK, and releases all locks.
- ROLLBACK: Reverses the effects of all outstanding changes since the last COMMIT or ROLLBACK, and releases all locks.
- 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.
- SAVEPOINT: Establishes a savepoint, which then allows you to perform partial ROLLBACKs.
- 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.
- 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.
The next lesson describes data integrity.