6 SQL Processing for Application Developers

6.1.1 Stages of SQL Statement Processing

1. Open or create a cursor.

   A program interface call opens or creates a cursor, in expectation of a SQL statement. Most applications create the cursor implicitly (automatically). Precompiler programs can create the cursor either implicitly or explicitly.

2. Parse the statement.

   The user process passes the SQL statement to Oracle Database, which loads a parsed representation of the statement into the shared SQL area. Oracle Database can catch many errors during parsing.

   Note:

   For a data definition language (DDL) statement, parsing includes data dictionary lookup and execution.

   See Also:

   • Oracle Database Concepts for information about parsing

   • Section 6.1.2, "Shared SQL Areas"

3. Determine if the statement is a query.

4. If the statement is a query, describe its results.

   Note:

   This stage is necessary only if the characteristics of the result are unknown; for example, when a user enters the query interactively.

   Oracle Database determines the characteristics (data types, lengths, and names) of the result.

5. If the statement is a query, define its output.

   You specify the location, size, and data type of variables defined to receive each fetched value. These variables are called define variables. Oracle Database performs data type conversion if necessary.

   See Also:

   Oracle Database Concepts for information about the DEFINE stage

6. Bind any variables.

   Oracle Database has determined the meaning of the SQL statement but does not have enough information to run it. Oracle Database needs values for any bind variable placeholders in the statement. In the example, Oracle Database needs a value for :department_id. The process of obtaining these values is called binding variables.

   A program must specify the location (memory address) of the value. End users of applications may be unaware that they are specifying values for bind variable placeholders, because the Oracle Database utility can prompt them for the values.

   Because the program specifies the location of the value (that is, binds by reference), it need not rebind the variable before rerunning the statement, even if the value changes. Each time Oracle Database runs the statement, it gets the value of the variable from its address.

   You must also specify a data type and length for each value (unless they are implied or defaulted) if Oracle Database must perform data type conversion.

   See Also:

   For more information about specifying a data type and length for a value:

   • Oracle Call Interface Programmer's Guide

   • Pro*C/C++ Programmer's Guide

7. (Optional) Parallelize the statement.

   Oracle Database can parallelize queries and some data definition language (DDL) operations (for example, index creation, creating a table with a subquery, and operations on partitions). Parallelization causes multiple server processes to perform the work of the SQL statement so that it can complete faster.

8. Run the statement.

   Oracle Database runs the statement. If the statement is a query or an INSERT statement, the database locks no rows, because no data is changing. If the statement is an UPDATE or DELETE statement, the database locks all rows that the statement affects, until the next COMMIT, ROLLBACK, or SAVEPOINT for the transaction, thereby ensuring data integrity.

   For some statements, you can specify multiple executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.

9. If the statement is a query, fetch its rows.

   Oracle Database selects rows and, if the query has an ORDER BY clause, orders the rows. Each successive fetch retrieves another row of the result set, until the last row has been fetched.

10. Close the cursor.

    Oracle Database closes the cursor.

6.1.2 Shared SQL Areas

Oracle Database automatically detects when applications send similar SQL statements to the database. The SQL area used to process the first occurrence of the statement is shared—that is, used for processing subsequent occurrences of that same statement. Therefore, only one shared SQL area exists for a unique statement. Because shared SQL areas are shared memory areas, any Oracle Database process can use a shared SQL area. The sharing of SQL areas reduces memory use on the database server, thereby increasing system throughput.

In determining whether statements are similar or identical, Oracle Database compares both SQL statements issued directly by users and applications and recursive SQL statements issued internally by DDL statements.

6.2.1 Deciding How to Group Operations in Transactions

Typically, deciding how to group operations in transactions is the concern of application developers who use programming interfaces to Oracle Database. When deciding how to group transactions:

• Define transactions such that work is accomplished in logical units and data remains consistent.

• Ensure that data in all referenced tables is in a consistent state before the transaction begins and after it ends.

• Ensure that each transaction consists only of the SQL statements or PL/SQL blocks that comprise one consistent change to the data.

For example, suppose that you write a web application that lets users transfer funds between accounts. The transaction must include the debit to one account, executed by one SQL statement, and the credit to another account, executed by another SQL statement. Both statements must fail or succeed as a unit of work; one statement must not be committed without the other. Do not include unrelated actions, such as a deposit to one account, in the transaction.

6.2.2 Improving Transaction Performance

As an application developer, you must try to improve performance. Consider using these performance enhancement techniques when designing and writing your application:

• For each transaction:

  1. If you can use a single SQL statement, then do so.

  2. If you cannot use a single SQL statement but you can use PL/SQL, then use as little PL/SQL as possible.

     For information about PL/SQL, see Part III, "PL/SQL for Application Developers".

  3. If you cannot use PL/SQL (because it cannot do what you must do; for example, read a directory), then use Java.

  4. If you cannot use Java (for example, if it is too slow) or you have existing third-generation language (3GL) code, then use an external C subprogram.

  For information about using Java and C in your application, see Chapter 18, "Developing Applications with Multiple Programming Languages."

• Establish standards for writing SQL statements so that you can take advantage of shared SQL areas.

  Oracle Database recognizes identical SQL statements and lets them share memory areas, reducing memory usage on the database server and increasing system throughput.

• Collect statistics that Oracle Database can use to implement a cost-based approach to SQL statement optimization, and use additional hints to the optimizer as needed.

  To collect most statistics, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and tune your statistics collection in other ways. For more information about this package, see Oracle Database PL/SQL Packages and Types Reference.

  To collect statistics unrelated to the cost-based optimizer (such as information about free list blocks), use the SQL statement ANALYZE. For more information about this statement, see Oracle Database SQL Language Reference.

  For more information about hints, see Oracle Database SQL Language Reference.

• Before beginning a transaction, invoke DBMS_APPLICATION_INFO procedures to record the name of the transaction in the database for later use when tracking its performance with Oracle Trace and the SQL trace facility. For information about the DBMS_APPLICATION_INFO package, see Oracle Database PL/SQL Packages and Types Reference.

• Increase user productivity and query efficiency by including user-written PL/SQL functions in SQL expressions. For details, see Section 11.9, "Invoking Stored PL/SQL Functions from SQL Statements."

6.2.3 Managing Commit Redo Action

When a transaction updates Oracle Database, it generates a corresponding redo entry. Oracle Database buffers the redo entry to the redo log until the transaction completes. When the transaction commits, the log writer process (LGWR) writes redo records to disk for the buffered redo entries of all changes in the transaction. By default, Oracle Database writes the redo entries to disk before the call returns to the client. This action causes a latency in the commit, because the application must wait for the redo entries to be persistent on disk.

Oracle Database lets you change the handling of commit redo to fit the needs of your application. If your application requires very high transaction throughput and you are willing to trade commit durability for lower commit latency, then you can change the default COMMIT options so that the application need not wait for the database to write data to the online redo logs.

Table 6-1 describes the COMMIT options.

To change the COMMIT options, use either the COMMIT statement (described in Oracle Database SQL Language Reference) or the appropriate initialization parameter. For information about initialization parameters, see Oracle Database Reference.

If your application uses Oracle Call Interface (OCI), then you can modify redo action by setting these flags in the OCITransCommit function in your application:

• OCI_TRANS_WRITEWAIT

• OCI_TRANS_WRITENOWAIT

• OCI_TRANS_WRITEBATCH

• OCI_TRANS_WRITEIMMED

The specification of the NOWAIT and BATCH options has a small window of vulnerability in which Oracle Database can roll back a transaction that your application views as committed. Your application must be able to tolerate these scenarios:

• The database host fails, which causes the database to lose redo entries that were buffered but not yet written to the online redo logs.

• A file I/O problem prevents LGWR from writing buffered redo entries to disk. If the redo logs are not multiplexed, then the commit is lost.

6.2.4 Determining Transaction Outcome After a Recoverable Outage

A recoverable outage is a system, hardware, communication, or storage failure that breaks the connection between your application (the client) and Oracle Database (the server). After an outage, your application receives a disconnection error message. The transaction that was running when the connection broke is the in-flight transaction, which may or may not have been committed or run to completion.

To recover from the outage, your application must determine the outcome of the in-flight transaction—whether it was committed and whether it made its intended session state changes. If the transaction was not committed, then the application can either resubmit the transaction or return the uncommitted status to the end user. If the transaction was committed, then the application can return the committed status, rather than the disconnection error, to the end user. If the transaction was both committed and completed, then the application may be able to continue by taking a new session and re-establishing the session state.

The Oracle Database feature that provides your application with the outcome of the in-flight transaction and can be used to ensure that it is not duplicated is Transaction Guard, and its application program interface (API) is the PL/SQL procedure DBMS_APP_CONT.GET_LTXID_OUTCOME.

Topics:

• Understanding Transaction Guard

• Understanding DBMS_APP_CONT.GET_LTXID_OUTCOME

• Using Transaction Guard

6.4.1 Privileges Required to Acquire Table Locks

No special privileges are required to acquire any type of table lock on a table in your own schema. To acquire a table lock on a table in another schema, you must have either the LOCK ANY TABLE system privilege or any object privilege (for example, SELECT or UPDATE) for the table.

6.4.2 Choosing a Locking Strategy

A transaction explicitly acquires the specified table locks when a LOCK TABLE statement is executed. A LOCK TABLE statement explicitly overrides default locking. When a LOCK TABLE statement is issued on a view, the underlying base tables are locked. This statement acquires exclusive table locks for the employees and departments tables on behalf of the containing transaction:

LOCK TABLE employees, departments IN EXCLUSIVE MODE NOWAIT;

You can specify several tables or views to lock in the same mode; however, only a single lock mode can be specified for each LOCK TABLE statement.

In the LOCK TABLE statement, you can also indicate how long you want to wait for the table lock:

• If you do not want to wait, specify either NOWAIT or WAIT 0.

  You acquire the table lock only if it is immediately available; otherwise, an error notifies you that the lock is unavailable now.

• To wait up to n seconds to acquire the table lock, specify WAIT n, where n is greater than 0 and less than or equal to 100000.

  If the table lock is still unavailable after n seconds, an error notifies you that the lock is unavailable now.

• To wait indefinitely to acquire the lock, specify neither NOWAIT nor WAIT.

  The database waits indefinitely until the table is available, locks it, and returns control to you. When the database is running DDL statements concurrently with DML statements, a timeout or deadlock can sometimes result. The database detects such timeouts and deadlocks and returns an error.

Topics:

• When to Lock with ROW SHARE MODE and ROW EXCLUSIVE MODE

• When to Lock with SHARE MODE

• When to Lock with SHARE ROW EXCLUSIVE MODE

• When to Lock with EXCLUSIVE MODE

-- Create and populate table:
 
DROP TABLE budget_tab;
CREATE TABLE budget_tab (
  sal     NUMBER(8,2),
  deptno  NUMBER(4)
);
 
INSERT INTO budget_tab (sal, deptno)
  SELECT salary, department_id
  FROM employees;
 
-- Lock departments and update employees and budget_tab:
 
LOCK TABLE departments IN SHARE MODE;
 
UPDATE employees
  SET salary = salary * 1.1
  WHERE department_id IN
    (SELECT department_id FROM departments WHERE location_id = 1700);
 
UPDATE budget_tab
SET sal = sal * 1.1
WHERE deptno IN
  (SELECT department_id FROM departments WHERE location_id = 1700);
 
COMMIT;  -- COMMIT releases lock

6.4.3 Letting Oracle Database Control Table Locking

If you let Oracle Database control table locking, your application needs less programming logic, but also has less control than if you manage the table locks yourself.

Issuing the statement SET TRANSACTION ISOLATION LEVEL SERIALIZABLE or ALTER SESSION ISOLATION LEVEL SERIALIZABLE preserves ANSI serializability without changing the underlying locking protocol. This technique gives concurrent access to the table while providing ANSI serializability. Getting table locks greatly reduces concurrency.

Change the settings for these parameters only when an instance is shut down. If multiple instances are accessing a single database, then all instances must use the same setting for these parameters.

6.4.4 Explicitly Acquiring Row Locks

You can override default locking with a SELECT statement that includes the FOR UPDATE clause. This statement acquires exclusive row locks for selected rows (as an UPDATE statement does), in anticipation of updating the selected rows in a subsequent statement.

You can use a SELECT FOR UPDATE statement to lock a row without changing it. For example, several triggers in Oracle Database PL/SQL Language Reference show how to implement referential integrity. In the EMP_DEPT_CHECK trigger, the row that contains the referenced parent key value is locked to guarantee that it remains for the duration of the transaction; if the parent key is updated or deleted, referential integrity is violated.

SELECT FOR UPDATE statements are often used by interactive programs that let a user modify fields of one or more specific rows (which might take some time); row locks are acquired so that only a single interactive program user is updating the rows at any given time.

If a SELECT FOR UPDATE statement is used when defining a cursor, the rows in the return set are locked when the cursor is opened (before the first fetch) rather than being locked as they are fetched from the cursor. Locks are released only when the transaction that opened the cursor is committed or rolled back, not when the cursor is closed.

Each row in the return set of a SELECT FOR UPDATE statement is locked individually; the SELECT FOR UPDATE statement waits until the other transaction releases the conflicting row lock. If a SELECT FOR UPDATE statement locks many rows in a table, and if the table experiences much update activity, it might be faster to acquire an EXCLUSIVE table lock instead.

By default, the SELECT FOR UPDATE statement waits until the requested row lock is acquired. To change this behavior, use the NOWAIT, WAIT, or SKIP LOCKED clause of the SELECT FOR UPDATE statement. For information about these clauses, see Oracle Database SQL Language Reference.

6.4.5 Examples of Concurrency Under Explicit Locking

Table 6-2 shows how Oracle Database maintains data concurrency, integrity, and consistency when the LOCK TABLE statement and the SELECT statement with the FOR UPDATE clause are used. For brevity, the message text for ORA-00054 ("resource busy and acquire with NOWAIT specified") is not included. User-entered text is bold.

LOCK TABLE hr.departments
IN ROW SHARE MODE;

Statement processed.

DROP TABLE hr.departments;

DROP TABLE hr.departments
* 
ORA-00054

LOCK TABLE hr.departments
IN EXCLUSIVE MODE
NOWAIT;
 
ORA-00054

SELECT location_id
FROM hr.departments
WHERE department_id = 20
FOR UPDATE OF location_id;
 
LOCATION_ID
-----------
DALLAS
 
1 row selected.

UPDATE hr.departments
SET location_id = 'NEW YORK'
WHERE department_id = 20;

ROLLBACK;

1 row processed.

ROLLBACK;

LOCK TABLE hr.departments
IN ROW EXCLUSIVE MODE;
 
Statement processed.

LOCK TABLE hr.departments
IN EXCLUSIVE MODE
NOWAIT;
 
ORA-00054

LOCK TABLE hr.departments
IN SHARE ROW EXCLUSIVE MODE
NOWAIT;
 
ORA-00054

LOCK TABLE hr.departments
IN SHARE ROW EXCLUSIVE MODE
NOWAIT;
 
ORA-00054

UPDATE hr.departments
SET location_id = 'NEW YORK'
WHERE department_id = 20;
 
1 row processed.

ROLLBACK;

SELECT location_id
FROM hr.departments
WHERE department_id = 20
FOR UPDATE OF location_id;
 
LOCATION_ID
-----------
DALLAS
 
1 row selected.

UPDATE hr.departments
SET location_id = 'NEW YORK'
WHERE department_id = 20;
 
1 row processed.

ROLLBACK;

1 row processed.

ROLLBACK;

LOCK TABLE hr.departments
IN ROW SHARE MODE
 
Statement processed.

LOCK TABLE hr.departments
IN EXCLUSIVE MODE
NOWAIT;
 
ORA-00054

LOCK TABLE hr.departments
IN SHARE ROW EXCLUSIVE MODE
NOWAIT;
 
ORA-00054

LOCK TABLE hr.departments
IN SHARE MODE;
 
Statement processed.

SELECT location_id
FROM hr.departments
WHERE department_id = 20;
 
LOCATION_ID
-----------
DALLAS
 
1 row selected.

SELECT location_id
FROM hr.departments
WHERE department_id = 20
FOR UPDATE OF location_id;
 
LOCATION_ID
-----------
DALLAS
 
1 row selected.

UPDATE hr.departments
SET location_id = 'NEW YORK'
WHERE department_id = 20;

ROLLBACK;

1 row processed.

ROLLBACK;

LOCK TABLE hr.departments
IN SHARE ROW EXCLUSIVE MODE;
 
Statement processed.

LOCK TABLE hr.departments
IN EXCLUSIVE MODE
NOWAIT;
 
ORA-00054

LOCK TABLE hr.departments
IN SHARE ROW EXCLUSIVE MODE
NOWAIT;
 
ORA-00054

LOCK TABLE hr.departments
IN SHARE MODE
NOWAIT;
 
ORA-00054

LOCK TABLE hr.departments
IN ROW EXCLUSIVE MODE
NOWAIT;
 
ORA-00054

LOCK TABLE hr.departments
IN SHARE MODE
NOWAIT;
 
ORA-00054

SELECT location_id
FROM hr.departments
WHERE department_id = 20;
 
LOCATION_ID
-----------
DALLAS
 
1 row selected.

SELECT location_id
FROM hr.departments
WHERE department_id = 20
FOR UPDATE OF location_id;
 
LOCATION_ID
-----------
DALLAS
 
1 row selected.

UPDATE hr.departments
SET location_id = 'NEW YORK'
WHERE department_id = 20;

UPDATE hr.departments
SET location_id = 'NEW YORK'
WHERE department_id = 20;

Cancel operation.

ROLLBACK;

1 row processed.

LOCK TABLE hr.departments
IN EXCLUSIVE MODE;

LOCK TABLE hr.departments
IN EXCLUSIVE MODE;

ORA-00054

LOCK TABLE hr.departments
IN ROW EXCLUSIVE MODE
NOWAIT;

ORA-00054

LOCK TABLE hr.departments
IN SHARE MODE;

ORA-00054

LOCK TABLE hr.departments
IN ROW EXCLUSIVE MODE
NOWAIT;

ORA-00054

LOCK TABLE hr.departments
IN ROW SHARE MODE
NOWAIT;

ORA-00054

SELECT location_id
FROM hr.departments
WHERE department_id = 20;
 
LOCATION_ID
-----------
DALLAS
 
1 row selected.

SELECT location_id
FROM hr.departments
WHERE department_id = 20
FOR UPDATE OF location_id;

UPDATE hr.departments
SET department_id = 30
WHERE department_id = 20;
 
1 row processed.

COMMIT;

0 rows selected.

SET TRANSACTION READ ONLY;

SELECT location_id
FROM hr.departments
WHERE department_id = 10;
 
LOCATION_ID
-----------
BOSTON

UPDATE hr.departments
SET location_id = 'NEW YORK'
WHERE department_id = 10;
 
1 row processed.

SELECT location_id
FROM hr.departments
WHERE department_id = 10;
 
LOCATION_ID
-----------
BOSTON

COMMIT;

SELECT location_id
FROM hr.departments
WHERE department_id = 10;
 
LOCATION_ID
-----------
BOSTON

COMMIT;

SELECT location_id
FROM hr.departments
WHERE department_id = 10;
 
LOCATION_ID
-----------
NEW YORK

6.5.1 When to Use User Locks

User locks can help:

• Provide exclusive access to a device, such as a terminal

• Provide application-level enforcement of read locks

• Detect when a lock is released and clean up after the application

• Synchronize applications and enforce sequential processing

Example 6-2 shows how the Pro*COBOL precompiler uses locks to ensure that there are no conflicts when multiple people must access a single device.

****************************************************************** 
* Print Check                                                    * 
* Any cashier may issue a refund to a customer returning goods.  * 
* Refunds under $50 are given in cash, more than $50 by check.   * 
* This code prints the check. One printer is opened by all       * 
* the cashiers to avoid the overhead of opening and closing it   * 
* for every check, meaning that lines of output from multiple    * 
* cashiers can become interleaved if you do not ensure exclusive * 
* access to the printer. The DBMS_LOCK package is used to        * 
* ensure exclusive access.                                       * 
****************************************************************** 
CHECK-PRINT 
*    Get the lock "handle" for the printer lock. 
   MOVE "CHECKPRINT" TO LOCKNAME-ARR. 
   MOVE 10 TO LOCKNAME-LEN. 
   EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.ALLOCATE_UNIQUE ( :LOCKNAME, :LOCKHANDLE ); 
      END; END-EXEC. 
*   Lock the printer in exclusive mode (default mode).
   EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.REQUEST ( :LOCKHANDLE ); 
      END; END-EXEC. 
*   You now have exclusive use of the printer, print the check. 
  ... 
*   Unlock the printer so other people can use it 
EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.RELEASE ( :LOCKHANDLE ); 
      END; END-EXEC.

6.5.2 Viewing and Monitoring Locks

Table 6-3 describes the Oracle Database facilities that display locking information for ongoing transactions within an instance.

6.6.1 Transaction Interaction and Isolation Level

The ANSI/ISO SQL standard defines three kinds of transaction interaction:

The kinds of interactions that a transaction can have is determined by its isolation level. The ANSI/ISO SQL standard defines four transaction isolation levels. Table 6-4 shows what kind of interactions are possible at each isolation level.

Table 6-5 shows which ANSI/ISO SQL transaction isolation levels Oracle Database provides.

Figure 6-1 shows how an arbitrary transaction (that is, one that is either SERIALIZABLE or READ COMMITTED) interacts with a serializable transaction.

Figure 6-1 Interaction Between Serializable Transaction and Another Transaction

Description of "Figure 6-1 Interaction Between Serializable Transaction and Another Transaction"

6.6.2 Setting Isolation Levels

To set the transaction isolation level for every transaction in your session, use the ALTER SESSION statement, described in Oracle Database SQL Language Reference.

To set the transaction isolation level for a specific transaction, use the ISOLATION LEVEL clause of the SET TRANSACTION statement. The SET TRANSACTION statement, described in Oracle Database SQL Language Reference, must be the first statement in the transaction.

6.6.3 Serializable Transactions and Referential Integrity

Because Oracle Database does not use read locks, even in SERIALIZABLE transactions, data read by one transaction can be overwritten by another. Therefore, transactions that perform database consistency checks at the application level must not assume that the data they read does not change during the transaction (even though such changes are invisible to the transaction). Code your application-level consistency checks carefully, even when using SERIALIZABLE transactions.

In Figure 6-2, transactions A and B (which are either READ COMMITTED or SERIALIZABLE) perform application-level checks to maintain the referential integrity of the parent/child relationship between two tables. Transaction A queries the parent table to check that it has a row with a specific primary key value before inserting corresponding child rows into the child table. Transaction B queries the child table to check that no child rows exist for a specific primary key value before deleting the corresponding parent row from the parent table. Both transactions assume (but do not ensure) that the data they read does not change before the transaction completes.

Figure 6-2 Referential Integrity Check

Description of "Figure 6-2 Referential Integrity Check"

The query by transaction A does not prevent transaction B from deleting the parent row, and the query by transaction B does not prevent transaction A from inserting child rows. Therefore, this can happen:

1. Transaction A queries the parent table and finds the specified parent row.

2. Transaction B queries the child table and finds no child rows for the specified parent row.

3. Having found the specified parent row, transaction A inserts the corresponding child rows into the child table.

4. Having found no child rows for the specified parent row, transaction B deletes the specified parent row from the parent table.

   Now the child rows that transaction A inserted in step 3 have no parent row.

The preceding result can occur even if both A and B are SERIALIZABLE transactions, because neither transaction prevents the other from changing the data that it reads to check consistency.

Ensuring that data queried by one transaction is not concurrently changed or deleted by another requires more transaction isolation than the ANSI/ISO SQL standard SERIALIZABLE isolation level provides. However, in Oracle Database:

• Transaction A can use a SELECT FOR UPDATE statement to query and lock the parent row, thereby preventing transaction B from deleting it.

  For information about the FOR UPDATE clause of the SELECT statement, see Oracle Database SQL Language Reference.

• Transaction B can prevent transaction A from finding the parent row (thereby preventing A from inserting the child rows) by reversing the order of its processing steps. That is, transaction B can:

  1. Delete the parent row.

  2. Query the child table.

  3. If the deleted parent row has child rows in the child table, then roll back the deletion of the parent row.

Alternatively, you can enforce referential integrity with a trigger. Instead of having transaction A query the parent table, define on the child table a row-level BEFORE INSERT trigger that does this:

• Queries the parent table with a SELECT FOR UPDATE statement, thereby ensuring that if the parent row exists, then it remains in the database for the duration of the transaction that inserts the child rows.

• Rejects the insertion of the child rows if the parent row does not exist.

A trigger runs SQL statements in the context of the triggering statement (that is, the triggering and triggered statements see the database in the same state). Therefore, if a READ COMMITTED transaction runs the triggering statement, then the triggered statements see the database as it was when the triggering statement began to execute. If a SERIALIZABLE transaction runs the triggering statement, then the triggered statements see the database as it was at the beginning of the transaction. In either case, using SELECT FOR UPDATE in the trigger correctly enforces referential integrity.

6.6.4 READ COMMITTED and SERIALIZABLE Isolation Levels

Oracle Database provides two transaction isolation levels, READ COMMITTED and SERIALIZABLE. Both levels provide a high degree of consistency and concurrency, reduce contention, and are designed for real-world applications. This topic compares them and explains how to choose between them.

Topics:

• Transaction Set Consistency Differences

• Choosing Transaction Isolation Levels

6.8.1 Examples of Autonomous Transactions

This section shows examples of autonomous transactions.

Topics:

• Ordering a Product

• Withdrawing Money from a Bank Account

As these examples show, there are four possible outcomes when you use autonomous and main transactions (see Table 6-7). There is no dependency between the outcome of an autonomous transaction and that of a main transaction.

6.8.1.1 Ordering a Product

Figure 6-5 shows an example of a customer ordering a product. The customer information (such as name, address, phone) is committed to a customer information table—even though the sale does not go through.

Figure 6-5 Example: A Buy Order

Description of "Figure 6-5 Example: A Buy Order"

6.8.1.2 Withdrawing Money from a Bank Account

In this example, a customer tries to withdraw money from a bank account. In the process, a main transaction invokes one of two autonomous transaction scopes (AT Scope 1 or AT Scope 2).

The possible scenarios for this transaction are:

• Section 6.8.1.2.1, "Scenario 1: Sufficient Funds"

• Section 6.8.1.2.2, "Scenario 2: Insufficient Funds with Overdraft Protection"

• Section 6.8.1.2.3, "Scenario 3: Insufficient Funds Without Overdraft Protection"

6.8.1.2.1 Scenario 1: Sufficient Funds

There are sufficient funds to cover the withdrawal, so the bank releases the funds (see Figure 6-6).

Figure 6-6 Bank Withdrawal—Sufficient Funds

Description of "Figure 6-6 Bank Withdrawal—Sufficient Funds"

6.8.1.2.2 Scenario 2: Insufficient Funds with Overdraft Protection

There are insufficient funds to cover the withdrawal, but the customer has overdraft protection, so the bank releases the funds (see Figure 6-7).

Figure 6-7 Bank Withdrawal—Insufficient Funds with Overdraft Protection

Description of "Figure 6-7 Bank Withdrawal—Insufficient Funds with Overdraft Protection"

6.8.1.2.3 Scenario 3: Insufficient Funds Without Overdraft Protection

There are insufficient funds to cover the withdrawal and the customer does not have overdraft protection, so the bank withholds the requested funds (see Figure 6-8).

Figure 6-8 Bank Withdrawal—Insufficient Funds Without Overdraft Protection

Description of "Figure 6-8 Bank Withdrawal—Insufficient Funds Without Overdraft Protection"

6.8.2 Declaring Autonomous Routines

To declare an autonomous routine, use PRAGMA AUTONOMOUS_TRANSACTION, which instructs the PL/SQL compiler to mark the routine as autonomous.

In Example 6-3, the function balance is autonomous.

-- Create table for package to use:
 
DROP TABLE accounts;
CREATE TABLE accounts (account INTEGER, balance REAL);
 
-- Create package:
 
CREATE OR REPLACE PACKAGE banking AS
  FUNCTION balance (acct_id INTEGER) RETURN REAL;
  -- Additional functions and packages
END banking;
/
CREATE OR REPLACE PACKAGE BODY banking AS
  FUNCTION balance (acct_id INTEGER) RETURN REAL IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    my_bal  REAL;
  BEGIN
    SELECT balance INTO my_bal FROM accounts WHERE account=acct_id;
    RETURN my_bal;
  END;
  -- Additional functions and packages
END banking;
/

6.9.1 What Operations Have Resumable Storage Allocation?

Queries, DML statements, and some DDL statements have resumable storage allocation after these kinds of errors:

• Out-of-space errors, such as ORA-01653.

• Space-limit errors, such as ORA-01628.

• Space-quota errors, such as ORA-01536.

Resumable storage allocation is possible whether the operation is performed directly by a SQL statement or within SQL*Loader, a stored subprogram, an anonymous PL/SQL block, or an OCI call such as OCIStmtExecute.

In dictionary-managed tablespaces, you cannot resume an index- or table-creating operation that encounters the limit for rollback segments or the maximum number of extents. You must use locally managed tablespaces and automatic undo management in combination with resumable storage allocation.

6.9.2 Handling Suspended Storage Allocation

When a statement in an application is suspended because of a storage allocation error, the application does not receive an error code. Therefore, either the application must use an AFTER SUSPEND trigger or the DBA must periodically check for suspended statements.

After the problem is corrected (usually by the DBA), the suspended statement automatically resumes execution. If the timeout period expires before the problem is corrected, then the statement raises a SERVERERROR exception.

Topics:

• Using an AFTER SUSPEND Trigger in the Application

• Checking for Suspended Statements

-- Create table used by trigger body
 
DROP TABLE rbs_error;
CREATE TABLE rbs_error (
  SQL_TEXT VARCHAR2(64),
  ERROR_MSG VARCHAR2(64),
  SUSPEND_TIME VARCHAR2(64)
);
 
-- Resumable Storage Allocation

CREATE OR REPLACE TRIGGER suspend_example
  AFTER SUSPEND
  ON DATABASE
DECLARE
  cur_sid           NUMBER;
  cur_inst          NUMBER;
  err_type          VARCHAR2(64);
  object_owner      VARCHAR2(64);
  object_type       VARCHAR2(64);
  table_space_name  VARCHAR2(64);
  object_name       VARCHAR2(64);
  sub_object_name   VARCHAR2(64);
  msg_body          VARCHAR2(64);
  ret_value         BOOLEAN;
  error_txt         VARCHAR2(64);
  mail_conn         UTL_SMTP.CONNECTION;
BEGIN
 SELECT DISTINCT(SID) INTO cur_sid FROM V$MYSTAT;
 cur_inst := USERENV('instance');
 ret_value := DBMS_RESUMABLE.SPACE_ERROR_INFO
              (err_type,
              object_owner,
              object_type,
              table_space_name,
              object_name,
              sub_object_name);
 IF object_type = 'ROLLBACK SEGMENT' THEN
   INSERT INTO rbs_error
     (SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
      FROM DBA_RESUMABLE
      WHERE SESSION_ID = cur_sid
      AND INSTANCE_ID = cur_inst);

    SELECT ERROR_MSG INTO error_txt
    FROM DBA_RESUMABLE
    WHERE SESSION_ID = cur_sid
    AND INSTANCE_ID = cur_inst;

    msg_body :=
     'Space error occurred: Space limit reached for rollback segment '
     || object_name || ' on ' || to_char(SYSDATE, 'Month dd, YYYY, HH:MIam')
     || '. Error message was: ' || error_txt;

    mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);
    UTL_SMTP.HELO(mail_conn, 'localhost');
    UTL_SMTP.MAIL(mail_conn, 'sender@localhost');
    UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');
    UTL_SMTP.DATA(mail_conn, msg_body);
    UTL_SMTP.QUIT(mail_conn);
    DBMS_RESUMABLE.ABORT(cur_sid);
  ELSE
    DBMS_RESUMABLE.SET_TIMEOUT(3600*8);
  END IF;
  COMMIT;
END;
/