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.

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.

  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.

• 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.

  To collect statistics unrelated to the cost-based optimizer (such as information about free list blocks), use the SQL statement ANALYZE.

• 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.

• Increase user productivity and query efficiency by including user-written PL/SQL functions in SQL expressions. For details, see.

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 9-1 describes the COMMIT options.

To change the COMMIT options, use either the COMMIT statement or the appropriate initialization parameter.

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.

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

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

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.

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.

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

To set the transaction isolation level for every transaction in your session, use the ALTER SESSION statement.

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

Topics:

• Transaction Set Consistency Differences

• Choosing Transaction Isolation Levels

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 9-7). There is no dependency between the outcome of an autonomous transaction and that of a main transaction.

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

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

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