Managing Transactions in PL/SQL
The Oracle RDBMS provides a very 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:
- COMMIT
Saves all outstanding changes since the last COMMIT or ROLLBACK and releases all locks.
- ROLLBACK
Erases all outstanding changes since the last COMMIT or ROLLBACK and releases all locks.
- ROLLBACK TO SAVEPOINT
Erases 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 following sections explore the COMMIT and ROLLBACK statements, as well as the autonomous transaction feature of PL/SQL.
Get Oracle PL/SQL for DBAs now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.