PL/SQL 101: Defining and managing transactions

If you've got a read-only database, you don't have to worry about transactions. But for almost every application you're ever going to build, that is not the case. Therefore, the concept and managing of transactions is central to the success of your application.

A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are. A transaction implicitly begins with any operation that obtains a TX lock:

• When a statement that modifies data is issued (e.g., insert, update, delete, merge)
• When a SELECT ... FOR UPDATE statement is issued
• When a transaction is explicitly started with a SET TRANSACTION statement or the DBMS_TRANSACTION package

Issuing either a COMMIT or ROLLBACK statement explicitly ends the current transaction.

This post reviews how to define, manage and control the transactions in your application with the following statements and features:

• Commit and Rollback
• Savepoints
• Autonomous transactions
• The SET TRANSACTION statement

You can find lots more details in the Transaction Processing and Control (doc) and in the links to Oracle Live SQL and Oracle Dev Gym resources below.

Commit and Rollback

Recall the definition of a transaction: "A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are." When all of the statements are "performed", that means you committed them or saved them to the database.

Use the COMMIT statement to save all changes, and make them visible to other users. Remember: no one can see the changes made in a particular session until they are committed.  Once committed, every user with access to the affected tables now see the new "state" of the table.

Use the ROLLBACK statement to reverse all changes since the last commit (or since you started the first transaction in your session).

This LiveSQL tutorial  (a part of the Databases for Developers course on the Oracle Dev Gym) demonstrates these basic elements of transaction management.

But wait! What if you only want to reverse some of the changes in your session, but leave others in place, ready to commit at some point in the future? Welcome to the world of savepoints.

Savepoints

Savepoints let you roll back part of a transaction instead of the whole transaction. The number of active savepoints for each session is unlimited.

When you roll back to a savepoint, any savepoints marked after that savepoint are erased. The savepoint to which you roll back is not erased. A simple rollback or commit erases all savepoints.

Savepoint names are undeclared identifiers. Reusing a savepoint name in a transaction moves the savepoint from its old position to the current point in the transaction, which means that a rollback to the savepoint affects only the current part of the transaction.

For the recursive programmers among us: If you mark a savepoint in a recursive subprogram, new instances of the SAVEPOINT statement run at each level in the recursive descent, but you can only roll back to the most recently marked savepoint.

Here is an example of using a savepoint (drawn from the same LiveSQL tutorial):

CREATE TABLE toys
(
   toy_id     INTEGER,
   toy_name   VARCHAR2 (100),
   colour     VARCHAR2 (10)
)
/

DECLARE
   l_count   INTEGER;
BEGIN
   INSERT INTO toys (toy_id, toy_name, colour)
        VALUES (8, 'Pink Rabbit', 'pink');

   SAVEPOINT after_six;

   INSERT INTO toys (toy_id, toy_name, colour)
        VALUES (9, 'Purple Ninja', 'purple');

   SELECT COUNT (*)
     INTO l_count
     FROM toys
    WHERE toy_id IN (8, 9);

   DBMS_OUTPUT.put_line (l_count);

   ROLLBACK TO SAVEPOINT after_six;

   SELECT COUNT (*)
     INTO l_count
     FROM toys
    WHERE toy_id IN (8, 9);

   DBMS_OUTPUT.put_line (l_count);

   ROLLBACK;

   SELECT COUNT (*)
     INTO l_count
     FROM toys
    WHERE toy_id IN (8, 9);

   DBMS_OUTPUT.put_line (l_count);
END;
/

2
1
0

Autonomous Transactions

By default, when you execute a COMMIT statement, all unsaved changes in your session are saved. And when you roll back, all unsaved changes are erased.

Sometimes, though, we'd like to save just one of our changes, but not the others. The most typical use case for this scenario is error logging. I want to write information out to my error log table and save it, but then I need to roll back the transaction (there is, after all, an error).

It's possible I could use save points to do that (see previous section), but that is hard to get right  consistently and dependably when you are calling a reusable logging program. Fortunately, I can simply make my error logging procedure an autonomous transaction. Then I can insert the error information and commit that insert, without affecting the business transaction, which will subsequently be rolled back.

And it's so easy to do!

Simply add this statement to the declaration section of a procedure or function...

PRAGMA AUTONOMOUS_TRANSACTION;

the following rule then applies:

Before the subprogram can be closed and control passed back to the calling block, any DML changes made within that subprogram must be committed or rolled back.

If there are any unsaved changes, the PL/SQL engine will raise the ORA-06519 exception, as shown below:

CREATE OR REPLACE FUNCTION nothing RETURN INTEGER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE employees SET last_name = 'abc';

   RETURN 1;
END;
/

BEGIN
   DBMS_OUTPUT.put_line (nothing);
END;
/

ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "STEVEN.NOTHING", line 10
ORA-06512: at line 2

Here's an example of using this feature in an error logging procedure:

CREATE OR REPLACE PACKAGE BODY error_mgr 
IS 
   PROCEDURE log_error (app_info_in IN VARCHAR2) 
   IS 
      PRAGMA AUTONOMOUS_TRANSACTION; 
      c_code   CONSTANT INTEGER := SQLCODE; 
   BEGIN 
      INSERT INTO error_log (created_on, 
                             created_by, 
                             errorcode, 
                             callstack, 
                             errorstack, 
                             backtrace, 
                             error_info) 
           VALUES (SYSTIMESTAMP, 
                   USER, 
                   c_code, 
                   DBMS_UTILITY.format_call_stack, 
                   DBMS_UTILITY.format_error_stack, 
                   DBMS_UTILITY.format_error_backtrace, 
                   app_info_in); 
 
      COMMIT; 
   END; 
END;

This LiveSQL script contains the full (and very basic) error logging package.

This LiveSQL script demonstrates the effect of an autonomous transaction pragma.

The SET TRANSACTION Statement

Use the SET TRANSACTION statement to establish the current transaction as read-only or read/write, establish its isolation level, assign it to a specified rollback segment, or assign a name to the transaction.

When you set your transaction to read-only, then queries return data as it existed at the time the transaction began, and you can only run select statements. Here's an example of using this option, drawn from Chris Saxon's excellent LiveSQL module:

set transaction read only;

select * from toys;

update toys
set    price = price + 1;

declare 
  pragma autonomous_transaction;
begin
  update toys set price = 99.00;
  commit;
end;
/

select * from toys;
commit;
select * from toys;

Here are the results when run in LiveSQL:

Oracle supports just two isolation levels: Read Committed and Serializable.

Read Committed

This is the default mode for Oracle Database. Using read committed, you have statement-level consistency. This means that each DML command (select, insert, update, or delete) can see all the data saved before it begins. Any changes saved by other sessions after it starts are hidden.

It does this using multiversion concurrency control (MVCC). When you update or delete a row, this stores the row's current state in undo. So other transactions can use this undo to view data as it existed in the past.

Serializable

When you set your transaction to serializable, the database acts as if you are the only user of the database. Changes made by other transactions are hidden from you. Serializable also stops you changing rows modified by other transactions with this error:

ORA-08177 can't serialize access for this transaction

You are, in other words, isolated.

Consider using serializable when a transaction accesses the same rows many times, and you will have many people running the transaction at the same time.

Chris Saxon covers all of these topics and more in his LiveSQL module. Be sure to check it out!