Skip to main content

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!

Comments

  1. A basic, but very important topic explained in easy to understand language, as usual. Excellent work. I'll keep this as future reference for new developers.

    ReplyDelete
  2. Advanced Oracle PL/SQL Programming with Packages
    Is this book still userful/valid for 11g and 12c.?
    I am not able find the PL/Vision package collection,all I am finding is broken links.
    Can an one share the link to PL/Vision package collection.?
    Greatly appreciate your help.

    Thanks.

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel