The Oracle database 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:
Saves all outstanding changes since the last COMMIT or ROLLBACK, and releases all locks.
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.
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.
These statements are explained in more detail in the following sections.
The COMMIT Statement
When you COMMIT, you make permanent any changes made ...
Get Oracle PL/SQL Programming, 5th Edition 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.