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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

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 perspective…