Oracle® Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2) Part Number B14251-01 |
|
|
View PDF |
This chapter describes how Oracle Database processes SQL statements. Before reading this chapter you should read the section "SQL Processing" in Oracle Database Concepts.
Topics include the following:
This section contains the following topics:
In general, only application designers who use the programming interfaces to Oracle Database are concerned with which types of actions should be grouped together as one transaction. You should use the following principles when deciding how to group transactions:
Transactions must be defined properly so that work is accomplished in logical units and data is kept consistent.
Data in all referenced tables should be in a consistent state before the transaction begins and after it ends.
Transactions should consist of only 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 enables users to transfer funds between accounts. The transaction should include the debit to one account, which is executed by one SQL statement, and the credit to another account, which is executed by a second SQL statement. Both statements should fail or succeed together as a unit of work; the credit should not be committed without the debit. Other non-related actions, such as a new deposit to one account, should not be included in the same transaction.
As an application developer, you should consider whether you can improve performance. Consider the following performance enhancements when designing and writing your application:
Use the SET
TRANSACTION
command with the USE
ROLLBACK
SEGMENT
clause to explicitly assign a transaction to a rollback segment. This technique can eliminate the need to allocate additional extents dynamically, which can reduce system performance. Note that this clause is relevant and valid only if you use rollback segments for undo. If you use automatic undo management, then Oracle Database ignores this clause.
Establish standards for writing SQL statements so that you can take advantage of shared SQL areas. Oracle Database recognizes identical SQL statements and allows them to share memory areas. This reduces memory usage on the database server and increases system throughput.
Use the ANALYZE
command to collect statistics that can be used by Oracle Database to implement a cost-based approach to SQL statement optimization. You can supply additional "hints" to the optimizer as needed.
Call the DBMS_APPLICATION_INFO
.SET_ACTION
procedure before beginning a transaction to register and name a transaction for later use when measuring performance across an application. You should specify which type of activity a transaction performs so that the system tuners can later see which transactions are taking up the most system resources.
Increase user productivity and query efficiency by including user-written PL/SQL functions in SQL expressions as described in "Calling Stored Functions from SQL Expressions".
Create explicit cursors when writing a PL/SQL application.
Reduce frequency of parsing and improve performance in precompiler programs by increasing the number of cursors with MAX_OPEN_CURSORS
.
Use the SET
TRANSACTION
command with the ISOLATION
LEVEL
set to SERIALIZABLE
to get ANSI/ISO serializable transactions.
To commit a transaction, use the COMMIT
statement. The following two statements are equivalent and commit the current transaction:
COMMIT WORK; COMMIT;
The COMMIT
statements lets you include the COMMENT
parameter along with a comment that provides information about the transaction being committed. This option is useful for including information about the origin of the transaction when you commit distributed transactions:
COMMIT COMMENT 'Dallas/Accts_pay/Trans_type 10B';
When a transaction updates the database, it generates a redo entry corresponding to this update. Oracle Database buffers this redo in memory until the completion of the transaction. When the transaction commits, the log writer (LGWR) process writes redo for the commit, along with the accumulated redo of all changes in the transaction, to disk. By default Oracle Database writes the redo to disk before the call returns to the client. This behavior introduces a latency in the commit because the application must wait for the redo to be persisted on disk.
Suppose that you are writing an application that requires very high transaction throughput. If you are willing to trade commit durability for lower commit latency, then you can change the default COMMIT
options so that the application does not need to wait for Oracle Database to write data to the online redo logs.
Oracle Database enables you to change the handling of commit redo depending on the needs of your application. You can change the commit behavior in the following locations:
COMMIT_WRITE
initialization parameter at the system or session level
COMMIT
statement
The options in the COMMIT
statement override the current settings in the initialization parameter. Table 2-1 describes redo persistence options that you can set in either location.
Table 2-1 Initialization Parameter and COMMIT Options for Managing Commit Redo
Option | Specifies that . . . |
---|---|
WAIT |
The commit does not return as successful until the redo corresponding to the commit is persisted in the online redo logs (default). |
NOWAIT |
The commit should return to the application without waiting for the redo to be written to the online redo logs. |
IMMEDIATE |
The log writer process should write the redo for the commit immediately (default). In other words, this option forces a disk I/O. |
BATCH |
Oracle Database should buffer the redo. The log writer process is permitted to write the redo to disk in its own time. |
The following example shows how to set the commit behavior to BATCH
and NOWAIT
in the initialization parameter file:
COMMIT_WRITE = BATCH, NOWAIT
You can change the commit behavior at the system level by executing ALTER SYSTEM
as in the following example:
ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT
After the initialization parameter is set, a COMMIT
statement with no options conforms to the options specified in the parameter. Alternatively, you can override the current initialization parameter setting by specifying options directly on the COMMIT
statement as in the following example:
COMMIT WRITE BATCH NOWAIT
In either case, your application specifies that log writer does not have to write the redo for the commit immediately to the online redo logs and should not wait for confirmation that the redo has been written to disk.
Note: You cannot change the defaultIMMEDIATE and WAIT behavior for distributed transactions. |
If your application uses OCI, then you can modify redo behavior by setting the following flags in the OCITransCommit()
function within your application:
OCI_TRANS_WRITEBATCH
OCI_TRANS_WRITENOWAIT
OCI_TRANS_WRITEIMMED
OCI_TRANS_WRITEWAIT
Note that the specification of the NOWAIT
and BATCH
options allows a small window of vulnerability in which Oracle Database can roll back a transaction that your application view as committed. Your application must be able to tolerate the following scenarios:
The database host crashes, which causes the database to lose redo that was buffered but not yet written to the online redo logs.
A file I/O problem prevents log writer from writing buffered redo to disk. If the redo logs are not multiplexed, then the commit is lost.
See Also:
|
To roll back an entire transaction, or to roll back part of a transaction to a savepoint, use the ROLLBACK
statement. For example, either of the following statements rolls back the entire current transaction:
ROLLBACK WORK; ROLLBACK;
The WORK
option of the ROLLBACK
command has no function.
To roll back to a savepoint defined in the current transaction, use the TO
option of the ROLLBACK
command. For example, either of the following statements rolls back the current transaction to the savepoint named POINT1
:
SAVEPOINT Point1; ... ROLLBACK TO SAVEPOINT Point1; ROLLBACK TO Point1;
To define a savepoint in a transaction, use the SAVEPOINT
command. The following statement creates the savepoint named ADD_EMP1
in the current transaction:
SAVEPOINT Add_emp1;
If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After creating a savepoint, you can roll back to the savepoint.
There is no limit on the number of active savepoints for each session. An active savepoint is one that has been specified since the last commit or rollback.
Table 2-4 shows a series of SQL statements that illustrates the use of COMMIT
, SAVEPOINT
, and ROLLBACK
statements within a transaction.
Table 2-2 Use of COMMIT, SAVEPOINT, and ROLLBACK
SQL Statement | Results |
---|---|
SAVEPOINT a; |
First savepoint of this transaction |
DELETE...; |
First DML statement of this transaction |
SAVEPOINT b; |
Second savepoint of this transaction |
INSERT INTO...; |
Second DML statement of this transaction |
SAVEPOINT c; |
Third savepoint of this transaction |
UPDATE...; |
Third DML statement of this transaction. |
ROLLBACK TO c; |
UPDATE statement is rolled back, savepoint C remains defined |
ROLLBACK TO b; |
INSERT statement is rolled back, savepoint C is lost, savepoint B remains defined |
ROLLBACK TO c; |
ORA-01086 error; savepoint C no longer defined |
INSERT INTO...; |
New DML statement in this transaction |
COMMIT; |
Commits all actions performed by the first DML statement (the DELETE statement) and the last DML statement (the second INSERT statement)
All other statements (the second and the third statements) of the transaction were rolled back before the |
By default, the consistency model for Oracle Database guarantees statement-level read consistency, but does not guarantee transaction-level read consistency (repeatable reads). If you want transaction-level read consistency, and if your transaction does not require updates, then you can specify a read-only transaction. After indicating that your transaction is read-only, you can execute as many queries as you like against any database table, knowing that the results of each query in the read-only transaction are consistent with respect to a single point in time.
A read-only transaction does not acquire any additional data locks to provide transaction-level read consistency. The multi-version consistency model used for statement-level read consistency is used to provide transaction-level read consistency; all queries return information with respect to the system change number (SCN) determined when the read-only transaction begins. Because no data locks are acquired, other transactions can query and update data being queried concurrently by a read-only transaction.
Long-running queries sometimes fail because undo information required for consistent read operations is no longer available. This happens when committed undo blocks are overwritten by active transactions. Automatic undo management provides a way to explicitly control when undo space can be reused; that is, how long undo information is retained. Your database administrator can specify a retention period by using the parameter UNDO_RETENTION
.
See Also: Oracle Database Administrator's Guide for information on long-running queries and resumable space allocation |
For example, if UNDO_RETENTION
is set to 30 minutes, then all committed undo information in the system is retained for at least 30 minutes. This ensures that all queries running for 30 minutes or less, under usual circumstances, do not encounter the OER error, "snapshot too old."
A read-only transaction is started with a SET
TRANSACTION
statement that includes the READ
ONLY
option. For example:
SET TRANSACTION READ ONLY;
The SET
TRANSACTION
statement must be the first statement of a new transaction; if any DML statements (including queries) or other non-DDL statements (such as SET ROLE
) precede a SET
TRANSACTION
READ
ONLY
statement, an error is returned. Once a SET
TRANSACTION
READ
ONLY
statement successfully executes, only SELECT
(without a FOR
UPDATE
clause), COMMIT
, ROLLBACK
, or non-DML statements (such as SET
ROLE
, ALTER
SYSTEM,
LOCK
TABLE
) are allowed in the transaction. Otherwise, an error is returned. A COMMIT
, ROLLBACK
, or DDL statement terminates the read-only transaction; a DDL statement causes an implicit commit of the read-only transaction and commits in its own transaction.
PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually.
A cursor is a handle to a specific private SQL area. In other words, a cursor can be thought of as a name for a specific private SQL area. A PL/SQL cursor variable enables the retrieval of multiple rows from a stored procedure. Cursor variables allow you to pass cursors as parameters in your 3GL application. Cursor variables are described in Oracle Database PL/SQL User's Guide and Reference.
Although most Oracle Database users rely on the automatic cursor handling of the database utilities, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a named resource available to a program, which can be specifically used for parsing SQL statements embedded within the application.
There is no absolute limit to the total number of cursors one session can have open at one time, subject to two constraints:
Each cursor requires virtual memory, so a session's total number of cursors is limited by the memory available to that process.
A systemwide limit of cursors for each session is set by the initialization parameter named OPEN_CURSORS
found in the parameter file (such as INIT
.ORA
).
Explicitly creating cursors for precompiler programs can offer some advantages in tuning those applications. For example, increasing the number of cursors can often reduce the frequency of parsing and improve performance. If you know how many cursors may be required at a given time, then you can make sure you can open that many simultaneously.
After each stage of execution, the cursor retains enough information about the SQL statement to reexecute the statement without starting over, as long as no other SQL statement has been associated with that cursor. The statement can be reexecuted without including the parse stage.
By opening several cursors, the parsed representation of several SQL statements can be saved. Repeated execution of the same SQL statements can thus begin at the describe, define, bind, or execute step, saving the repeated cost of opening cursors and parsing.
To understand the performance characteristics of a cursor, a DBA can retrieve the text of the query represented by the cursor using the V$SQL
catalog view. Because the results of EXPLAIN PLAN
on the original query might differ from the way the query is actually processed, a DBA can get more precise information by examining the V$SQL_PLAN
, V$SQL_PLAN_STATISTICS
, and V$SQL_PLAN_STATISTICS_ALL
catalog views.:
The V$SQL_PLAN
view contains the execution plan information for each child cursor loaded in the library cache.
The V$SQL_PLAN_STATISTICS
view provides execution statistics at the row source level for each child cursor.
The V$SQL_PLAN_STATISTICS_ALL
view contains memory usage statistics for row sources that use SQL memory (sort or hash-join). This view concatenates information in V$SQL_PLAN
with execution statistics from V$SQL_PLAN_STATISTICS
and V$SQL_WORKAREA
.
Closing a cursor means that the information currently in the associated private area is lost and its memory is deallocated. Once a cursor is opened, it is not closed until one of the following events occurs:
Oracle Database always performs necessary locking to ensure data concurrency, integrity, and statement-level read consistency. You can override these default locking mechanisms. For example, you might want to override the default locking of Oracle Database if:
You want transaction-level read consistency or "repeatable reads"—where transactions query a consistent set of data for the duration of the transaction, knowing that the data has not been changed by any other transactions. This level of consistency can be achieved by using explicit locking, read-only transactions, serializable transactions, or overriding default locking for the system.
A transaction requires exclusive access to a resource. To proceed with its statements, the transaction with exclusive access to a resource does not have to wait for other transactions to complete.
The automatic locking mechanisms can be overridden at the transaction level. Transactions including the following SQL commands override Oracle Database's default locking:
LOCK
TABLE
SELECT
, including the FOR
UPDATE
clause
SET
TRANSACTION
with the READ
ONLY
or ISOLATION
LEVEL
SERIALIZABLE
options
Locks acquired by these statements are released after the transaction is committed or rolled back.
The following sections describe each option available for overriding the default locking of Oracle Database. The initialization parameter DML_LOCKS
determines the maximum number of DML locks allowed.
Although the default value is usually enough, you might need to increase it if you use additional manual locks.
Caution: If you override the default locking of Oracle Database at any level, be sure that the overriding locking procedures operate correctly: Ensure that data integrity is guaranteed, data concurrency is acceptable, and deadlocks are not possible or are appropriately handled. |
A transaction explicitly acquires the specified table locks when a LOCK
TABLE
statement is executed. A LOCK
TABLE
statement manually overrides default locking. When a LOCK
TABLE
statement is issued on a view, the underlying base tables are locked. The following statement acquires exclusive table locks for the EMP_TAB
and DEPT_TAB
tables on behalf of the containing transaction:
LOCK TABLE Emp_tab, Dept_tab 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.
Note: When a table is locked, all rows of the table are locked. No other user can modify the table. |
You can also indicate if you do or do not want to wait to acquire the lock. If you specify the NOWAIT
option, then you only acquire the table lock if it is immediately available. Otherwise an error is returned to notify that the lock is not available at this time. In this case, you can attempt to lock the resource at a later time. If NOWAIT
is omitted, then the transaction does not proceed until the requested table lock is acquired. If the wait for a table lock is excessive, then you might want to cancel the lock operation and retry at a later time; you can code this logic into your applications.
LOCK TABLE Emp_tab IN ROW SHARE MODE; LOCK TABLE Emp_tab IN ROW EXCLUSIVE MODE;
ROW SHARE
and ROW EXCLUSIVE
table locks offer the highest degree of concurrency. You might use these locks if:
Your transaction needs to prevent another transaction from acquiring an intervening share, share row, or exclusive table lock for a table before the table can be updated in your transaction. If another transaction acquires an intervening share, share row, or exclusive table lock, no other transactions can update the table until the locking transaction commits or rolls back.
Your transaction needs to prevent a table from being altered or dropped before the table can be modified later in your transaction.
LOCK TABLE Emp_tab IN SHARE MODE;
SHARE
table locks are rather restrictive data locks. You might use these locks if:
Your transaction only queries the table, and requires a consistent set of the table data for the duration of the transaction.
You can hold up other transactions that try to update the locked table, until all transactions that hold SHARE
locks on the table either commit or roll back.
Other transactions may acquire concurrent SHARE
table locks on the same table, also allowing them the option of transaction-level read consistency.
Caution: Your transaction may or may not update the table later in the same transaction. However, if multiple transactions concurrently hold share table locks for the same table, no transaction can update the table (even if row locks are held as the result of aSELECT ... FOR UPDATE statement). Therefore, if concurrent share table locks on the same table are common, updates cannot proceed and deadlocks are common. In this case, use share row exclusive or exclusive table locks instead. |
For example, assume that two tables, EMP_TAB
and BUDGET_TAB
, require a consistent set of data in a third table, DEPT_TAB
. For a given department number, you want to update the information in both of these tables, and ensure that no new members are added to the department between these two transactions.
Although this scenario is quite rare, it can be accommodated by locking the DEPT_TAB
table in SHARE
MODE
, as shown in the following example. Because the DEPT_TAB
table is rarely updated, locking it probably does not cause many other transactions to wait long.
Note: You may need to set up data structures similar to the following for certain examples to work:CREATE TABLE dept_tab( deptno NUMBER(2) NOT NULL, dname VARCHAR2(14), loc VARCHAR2(13)); CREATE TABLE emp_tab ( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2)); CREATE TABLE Budget_tab ( totsal NUMBER(7,2), deptno NUMBER(2) NOT NULL); |
LOCK TABLE Dept_tab IN SHARE MODE; UPDATE Emp_tab SET sal = sal * 1.1 WHERE deptno IN (SELECT deptno FROM Dept_tab WHERE loc = 'DALLAS'); UPDATE Budget_tab SET Totsal = Totsal * 1.1 WHERE Deptno IN (SELECT Deptno FROM Dept_tab WHERE Loc = 'DALLAS'); COMMIT; /* This releases the lock */
LOCK TABLE Emp_tab IN SHARE ROW EXCLUSIVE MODE;
You might use a SHARE ROW EXCLUSIVE
table lock if:
Your transaction requires both transaction-level read consistency for the specified table and the ability to update the locked table.
You do not care if other transactions acquire explicit row locks (using SELECT
... FOR
UPDATE
), which might make UPDATE
and INSERT
statements in the locking transaction wait and might cause deadlocks.
You only want a single transaction to have this behavior.
LOCK TABLE Emp_tab IN EXCLUSIVE MODE;
You might use an EXCLUSIVE
table if:
Your transaction requires immediate update access to the locked table. When your transaction holds an exclusive table lock, other transactions cannot lock specific rows in the locked table.
Your transaction also ensures transaction-level read consistency for the locked table until the transaction is committed or rolled back.
You are not concerned about low levels of data concurrency, making transactions that request exclusive table locks wait in line to update the table sequentially.
Letting Oracle Database control table locking means your application needs less programming logic, but also has less control, than if you manage the table locks yourself.
Issuing the command SET
TRANSACTION ISOLATION LEVEL SERIALIZABLE
or ALTER
SESSION ISOLATION LEVEL SERIALIZABLE
preserves ANSI serializability without changing the underlying locking protocol. This technique allows concurrent access to the table while providing ANSI serializability. Getting table locks greatly reduces concurrency.
See Also:
|
The settings for these parameters should be changed only when an instance is shut down. If multiple instances are accessing a single database, then all instances should 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 actually changing it. For example, several triggers in Chapter 9, "Coding Triggers", show how to implement referential integrity. In the EMP_DEPT_CHECK
trigger (see "Foreign Key Trigger for Child Table"), 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 would be violated.
SELECT
... FOR
UPDATE
statements are often used by interactive programs that allow a user to 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 only released 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 a lot of update activity, it might be faster to acquire an EXCLUSIVE
table lock instead.
Note: The return set for aSELECT... FOR UPDATE may change while the query is running; for example, if columns selected by the query are updated or rows are deleted after the query started. When this happens, SELECT... FOR UPDATE acquires locks on the rows that did not change, gets a new read-consistent snapshot of the table using these locks, and then restarts the query to acquire the remaining locks.
This can cause a deadlock between sessions querying the table concurrently with DML operations when rows are locked in a non-sequential order. To prevent such deadlocks, design your application so that any concurrent DML on the table does not affect the return set of the query. If this is not feasible, you may want to serialize queries in your application. |
When acquiring row locks with SELECT
... FOR
UPDATE
, you can specify the NOWAIT
option to indicate that you are not willing to wait to acquire the lock. If you cannot acquire then lock immediately, an error is returned to signal that the lock is not possible at this time. You can try to lock the row again later.
By default, the transaction waits until the requested row lock is acquired. If the wait for a row lock is too long, you can code logic into your application to cancel the lock operation and try again later.
You can use Oracle Lock Management services for your applications by making calls to the DBMS_LOCK
package. It is possible to request a lock of a specific mode, give it a unique name recognizable in another procedure in the same or another instance, change the lock mode, and release it. Because a reserved user lock is the same as an Oracle Database lock, it has all the features of a database lock, such as deadlock detection. Be certain that any user locks used in distributed transactions are released upon COMMIT
, or an undetected deadlock can occur.
See Also: Oracle Database PL/SQL Packages and Types Reference for detailed information on theDBMS_LOCK package |
User locks can help to:
Provide exclusive access to a device, such as a terminal
Provide application-level enforcement of read locks
Detect when a lock is released and cleanup after the application
Synchronize applications and enforce sequential processing
The following Pro*COBOL precompiler example shows how locks can be used to ensure that there are no conflicts when multiple people need to 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. The one printer is opened by all * * the cashiers to avoid the overhead of opening and closing it * * for every check. This means that lines of output from multiple* * cashiers could become interleaved if we don't 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. * We 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.
Table 2-5 describes Oracle Database facilities to display locking information for ongoing transactions within an instance.
Table 2-3 Ways to Display Locking Information
Tool | Description |
---|---|
Oracle Enterprise Manager 10g Database Control |
From the Additional Monitoring Links section of the Database Performance page, click Database Locks to display user blocks, blocking locks, or the complete list of all database locks. Refer to Oracle Database 2 Day DBA for more information. |
UTLLOCKT.SQL |
The UTLLOCKT .SQL script displays a simple character lock wait-for graph in tree structured fashion. Using any ad hoc SQL tool (such as SQL*Plus) to execute the script, it prints the sessions in the system that are waiting for locks and the corresponding blocking locks. The location of this script file is operating system dependent. (You must have run the CATBLOCK .SQL script before using UTLLOCKT .SQL .) |
By default, Oracle Database permits concurrently executing transactions to modify, add, or delete rows in the same table, and in the same data block. Changes made by one transaction are not seen by another concurrent transaction until the transaction that made the changes commits.
If a transaction A attempts to update or delete a row that has been locked by another transaction B (by way of a DML or SELECT
... FOR
UPDATE
statement), then A's DML command blocks until B commits or rolls back. Once B commits, transaction A can see changes that B has made to the database.
For most applications, this concurrency model is the appropriate one, because it provides higher concurrency and thus better performance. But some rare cases require transactions to be serializable. Serializable transactions must execute in such a way that they appear to be executing one at a time (serially), rather than concurrently. Concurrent transactions executing in serialized mode can only make database changes that they could have made if the transactions ran one after the other.
Figure 2-1 shows a serializable transaction (B) interacting with another transaction (A).
The ANSI/ISO SQL standard SQL92 defines three possible kinds of transaction interaction, and four levels of isolation that provide increasing protection against these interactions. These interactions and isolation levels are summarized in Table 2-4.
Table 2-4 Summary of ANSI Isolation Levels
Isolation Level | Dirty ReadFoot 1 | Non-Repeatable ReadFoot 2 | Phantom ReadFoot 3 |
---|---|---|---|
READ UNCOMMITTED |
Possible | Possible | Possible |
READ COMMITTED |
Not possible | Possible | Possible |
REPEATABLE READ |
Not possible | Not possible | Possible |
SERIALIZABLE |
Not possible | Not possible | Not possible |
The behavior of Oracle Database with respect to these isolation levels is summarized in Table 2-5.
Table 2-5 ANSI Isolation Levels and Oracle Database
Isolation Level | Description |
---|---|
READ UNCOMMITTED |
Oracle Database never permits "dirty reads." Although some other database products use this undesirable technique to improve thoughput, it is not required for high throughput with Oracle Database. |
READ COMMITTED |
Oracle Database meets the READ COMMITTED isolation standard. This is the default mode for all Oracle Database applications. Because an Oracle Database query only sees data that was committed at the beginning of the query (the snapshot time), Oracle Database actually offers more consistency than is required by the ANSI/ISO SQL92 standards for READ COMMITTED isolation. |
REPEATABLE READ |
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE . |
SERIALIZABLE |
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE . |
Figure 2-1 shows how a serializable transaction (Transaction B) interacts with another transaction (A, which can be either SERIALIZABLE
or READ
COMMITTED
).
When a serializable transaction fails with an ORA-08177
error ("cannot serialize access"), the application can take any of several actions:
Commit the work executed to that point
Execute additional, different, statements, perhaps after rolling back to a prior savepoint in the transaction
Roll back the entire transaction and try it again
Oracle Database stores control information in each data block to manage access by concurrent transactions. To use the SERIALIZABLE
isolation level, you must use the INITRANS
clause of the CREATE
TABLE
or ALTER
TABLE
command to set aside storage for this control information. To use serializable mode, INITRANS
must be set to at least 3.
You can change the isolation level of a transaction using the ISOLATION
LEVEL
clause of the SET
TRANSACTION
command, which must be the first command issued in a transaction.
Use the ALTER
SESSION
command to set the transaction isolation level on a session-wide basis.
See Also: Oracle Database Reference for the complete syntax of theSET TRANSACTION and ALTER SESSION commands |
Oracle Database stores control information in each data block to manage access by concurrent transactions. Therefore, if you set the transaction isolation level to SERIALIZABLE
, then you must use the ALTER
TABLE
command to set INITRANS
to at least 3. This parameter causes Oracle Database to allocate sufficient storage in each block to record the history of recent transactions that accessed the block. Higher values should be used for tables that will undergo many transactions updating the same blocks.
Because Oracle Database does not use read locks, even in SERIALIZABLE
transactions, data read by one transaction can be overwritten by another. Transactions that perform database consistency checks at the application level should not assume that the data they read will not change during the execution of the transaction (even though such changes are not visible to the transaction). Database inconsistencies can result unless such application-level consistency checks are coded carefully, even when using SERIALIZABLE
transactions. Note, however, that the examples shown in this section are applicable for both READ
COMMITTED
and SERIALIZABLE
transactions.
Figure 2-2 shows two different transactions that perform application-level checks to maintain the referential integrity parent/child relationship between two tables. One transaction checks that a row with a specific primary key value exists in the parent table before inserting corresponding child rows. The other transaction checks to see that no corresponding detail rows exist before deleting a parent row. In this case, both transactions assume (but do not ensure) that data they read will not change before the transaction completes.
The read issued by transaction A does not prevent transaction B from deleting the parent row, and transaction B's query for child rows does not prevent transaction A from inserting child rows. This scenario leaves a child row in the database with no corresponding parent row. This result occurs even if both A and B are SERIALIZABLE
transactions, because neither transaction prevents the other from making changes in the data it reads to check consistency.
As this example shows, sometimes you must take steps to ensure that the data read by one transaction is not concurrently written by another. This requires a greater degree of transaction isolation than defined by SQL92 SERIALIZABLE
mode.
Fortunately, it is straightforward in Oracle Database to prevent the anomaly described:
Transaction A can use SELECT
FOR
UPDATE
to query and lock the parent row and thereby prevent transaction B from deleting the row.
Transaction B can prevent Transaction A from gaining access to the parent row by reversing the order of its processing steps. Transaction B first deletes the parent row, and then rolls back if its subsequent query detects the presence of corresponding rows in the child table.
Referential integrity can also be enforced in Oracle Database using database triggers, instead of a separate query as in Transaction A. For example, an INSERT
into the child table can fire a BEFORE INSERT
row-level trigger to check for the corresponding parent row. The trigger queries the parent table using SELECT
FOR
UPDATE
, ensuring that parent row (if it exists) remains in the database for the duration of the transaction inserting the child row. If the corresponding parent row does not exist, the trigger rejects the insert of the child row.
SQL statements issued by a database trigger execute in the context of the SQL statement that caused the trigger to fire. All SQL statements executed within a trigger see the database in the same state as the triggering statement. Thus, in a READ
COMMITTED
transaction, the SQL statements in a trigger see the database as of the beginning of the triggering statement execution, and in a transaction executing in SERIALIZABLE
mode, the SQL statements see the database as of the beginning of the transaction. In either case, the use of SELECT
FOR
UPDATE
by the trigger correctly enforces referential integrity.
Oracle Database gives you a choice of two transaction isolation levels with different characteristics. Both the READ
COMMITTED
and SERIALIZABLE
isolation levels provide a high degree of consistency and concurrency. Both levels reduce contention, and are designed for deploying real-world applications. The rest of this section compares the two isolation modes and provides information helpful in choosing between them.
A useful way to describe the READ
COMMITTED
and SERIALIZABLE
isolation levels in Oracle Database is to consider:
A collection of database tables (or any set of data)
A sequence of reads of rows in those tables
The set of transactions committed at any moment
An operation (a query or a transaction) is transaction set consistent if its read operations all return data written by the same set of committed transactions. When an operation is not transaction set consistent, some reads reflect the changes of one set of transactions, and other reads reflect changes made by other transactions. Such an operation sees the database in a state that reflects no single set of committed transactions.
Oracle Database transactions executing in READ
COMMITTED
mode are transaction-set consistent on an individual-statement basis, because all rows read by a query must be committed before the query begins.
Oracle Database transactions executing in SERIALIZABLE
mode are transaction set consistent on an individual-transaction basis, because all statements in a SERIALIZABLE
transaction execute on an image of the database as of the beginning of the transaction.
In other database systems, a single query run in READ COMMITTED
mode provides results that are not transaction set consistent. The query is not transaction set consistent, because it may see only a subset of the changes made by another transaction. For example, a join of a master table with a detail table could see a master record inserted by another transaction, but not the corresponding details inserted by that transaction, or vice versa. The READ
COMMITTED
mode avoids this problem, and so provides a greater degree of consistency than read-locking systems.
In read-locking systems, at the cost of preventing concurrent updates, SQL92 REPEATABLE
READ
isolation provides transaction set consistency at the statement level, but not at the transaction level. The absence of phantom protection means two queries issued by the same transaction can see data committed by different sets of other transactions. Only the throughput-limiting and deadlock-susceptible SERIALIZABLE
mode in these systems provides transaction set consistency at the transaction level.
Table 2-6 summarizes key similarities and differences between READ
COMMITTED
and SERIALIZABLE
transactions.
Table 2-6 Read Committed Versus Serializable Transaction
Operation | Read Committed | Serializable |
---|---|---|
Dirty write | Not Possible | Not Possible |
Dirty read | Not Possible | Not Possible |
Non-repeatable read | Possible | Not Possible |
Phantoms | Possible | Not Possible |
Compliant with ANSI/ISO SQL 92 | Yes | Yes |
Read snapshot time | Statement | Transaction |
Transaction set consistency | Statement level | Transaction level |
Row-level locking | Yes | Yes |
Readers block writers | No | No |
Writers block readers | No | No |
Different-row writers block writers | No | No |
Same-row writers block writers | Yes | Yes |
Waits for blocking transaction | Yes | Yes |
Subject to "can't serialize access" error | No | Yes |
Error after blocking transaction aborts | No | No |
Error after blocking transaction commits | No | Yes |
Choose an isolation level that is appropriate to the specific application and workload. You might choose different isolation levels for different transactions. The choice depends on performance and consistency needs, and consideration of application coding requirements.
For environments with many concurrent users rapidly submitting transactions, you must assess transaction performance against the expected transaction arrival rate and response time demands, and choose an isolation level that provides the required degree of consistency while performing well. Frequently, for high performance environments, you must trade-off between consistency and concurrency (transaction throughput).
Both Oracle Database isolation modes provide high levels of consistency and concurrency (and performance) through the combination of row-level locking and Oracle Database's multi-version concurrency control system. Because readers and writers do not block one another in Oracle Database, while queries still see consistent data, both READ COMMITTED
and SERIALIZABLE
isolation provide a high level of concurrency for high performance, without the need for reading uncommitted ("dirty") data.
READ
COMMITTED
isolation can provide considerably more concurrency with a somewhat increased risk of inconsistent results (due to phantoms and non-repeatable reads) for some transactions. The SERIALIZABLE
isolation level provides somewhat more consistency by protecting against phantoms and non-repeatable reads, and may be important where a read/write transaction executes a query more than once. However, SERIALIZABLE
mode requires applications to check for the "can't serialize access" error, and can significantly reduce throughput in an environment with many concurrent transactions accessing the same data for update. Application logic that checks database consistency must take into account the fact that reads do not block writes in either mode.
When a transaction runs in serializable mode, any attempt to change data that was changed by another transaction since the beginning of the serializable transaction causes an error:
ORA-08177: Can't serialize access for this transaction.
When you get this error, roll back the current transaction and execute it again. The transaction gets a new transaction snapshot, and the operation is likely to succeed.
To minimize the performance overhead of rolling back transactions and executing them again, try to put DML statements that might conflict with other concurrent transactions near the beginning of your transaction.
This section gives a brief overview of autonomous transactions and what you can do with them.
See Also: Oracle Database PL/SQL User's Guide and Reference and Chapter 9, "Coding Triggers" for detailed information on autonomous transactions |
At times, you may want to commit or roll back some changes to a table independently of a primary transaction's final outcome. For example, in a stock purchase transaction, you may want to commit a customer's information regardless of whether the overall stock purchase actually goes through. Or, while running that same transaction, you may want to log error messages to a debug table even if the overall transaction rolls back. Autonomous transactions allow you to do such tasks.
An autonomous transaction (AT) is an independent transaction started by another transaction, the main transaction (MT). It lets you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.
An autonomous transaction executes within an autonomous scope. An autonomous scope is a routine you mark with the pragma (compiler directive) AUTONOMOUS_TRANSACTION
. The pragma instructs the PL/SQL compiler to mark a routine as autonomous
(independent). In this context, the term routine includes:
Top-level (not nested) anonymous PL/SQL blocks
Local, standalone, and packaged functions and procedures
Methods of a SQL object type
PL/SQL triggers
Figure 2-3 shows how control flows from the main routine (MT) to an autonomous routine (AT) and back again. As you can see, the autonomous routine can commit more than one transaction (AT1 and AT2) before control returns to the main routine.
When you enter the executable section of an autonomous routine, the main routine suspends. When you exit the routine, the main routine resumes. COMMIT
and ROLLBACK
end the active autonomous transaction but do not exit the autonomous routine. As Figure 2-3 shows, when one transaction ends, the next SQL statement begins another transaction.
A few more characteristics of autonomous transactions:
The changes autonomous transactions effect do not depend on the state or the eventual disposition of the main transaction. For example:
An autonomous transaction does not see any changes made by the main transaction.
When an autonomous transaction commits or rolls back, it does not affect the outcome of the main transaction.
The changes an autonomous transaction effects are visible to other transactions as soon as that autonomous transaction commits. This means that users can access the updated information without having to wait for the main transaction to commit.
Autonomous transactions can start other autonomous transactions.
Figure 2-4 illustrates some of the possible sequences autonomous transactions can follow.
Figure 2-4 Possible Sequences of Autonomous Transactions
The two examples in this section illustrate some of the ways you can use autonomous transactions.
As these examples illustrate, there are four possible outcomes that can occur when you use autonomous and main transactions. Table 2-7 presents these possible outcomes. As you can see, there is no dependency between the outcome of an autonomous transaction and that of a main transaction.
Table 2-7 Possible Transaction Outcomes
Autonomous Transaction | Main Transaction |
---|---|
Commits | Commits |
Commits | Rolls back |
Rolls back | Commits |
Rolls back | Rolls back |
In this example, illustrated by Figure 2-5, a customer enters a buy order. That customer's information (such as name, address, phone) is committed to a customer information table—even though the sale does not go through.
In this example, a customer tries to make a withdrawal from a bank account. In the process, a main transaction calls one of two autonomous transaction scopes (AT Scope 1, and AT Scope 2).
The following diagrams illustrate three possible scenarios for this transaction.
Scenario 1: There are sufficient funds to cover the withdrawal and therefore the bank releases the funds
Scenario 2: There are insufficient funds to cover the withdrawal, but the customer has overdraft protection. The bank therefore releases the funds.
Scenario 3: There are insufficient funds to cover the withdrawal, the customer does not have overdraft protection, and the bank therefore withholds the requested funds.
Scenario 1
There are sufficient funds to cover the withdrawal and therefore the bank releases the funds. This is illustrated by Figure 2-6.
Scenario 2
There are insufficient funds to cover the withdrawal, but the customer has overdraft protection. The bank therefore releases the funds. This is illustrated by Figure 2-7.
Figure 2-7 Example: Bank Withdrawal—Insufficient Funds WITH Overdraft Protection
Scenario 3
There are insufficient funds to cover the withdrawal, the customer does not have overdraft protection, and the bank therefore withholds the requested funds. This is illustrated by Figure 2-8.
Figure 2-8 Example: Bank Withdrawal—Insufficient Funds WITHOUT Overdraft Protection
Note: This section is provided here to round out your general understanding of autonomous transactions. For a more thorough understanding of autonomous transactions, refer to Oracle Database PL/SQL User's Guide and Reference. |
To define autonomous transactions, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION
. The pragma instructs the PL/SQL compiler to mark the procedure, function, or PL/SQL block as autonomous (independent).
You can code the pragma anywhere in the declarative section of a procedure, function, or PL/SQL block. But, for readability, code the pragma at the top of the section. The syntax follows:
PRAGMA AUTONOMOUS_TRANSACTION;
In the following example, you mark a packaged function as autonomous:
CREATE OR REPLACE PACKAGE Banking AS FUNCTION Balance (Acct_id INTEGER) RETURN REAL; -- add 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 --add appropriate code END; -- add additional functions and packages... END Banking;
Note the following restrictions on autonomous transactions.
You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. For example, the following pragma is illegal:
CREATE OR REPLACE PACKAGE Banking AS PRAGMA AUTONOMOUS_TRANSACTION; -- illegal FUNCTION Balance (Acct_id INTEGER) RETURN REAL; END Banking;
You cannot execute a PIPE ROW
statement in your autonomous routine while your autonomous transaction is open. You must close the autonomous transaction before executing the PIPE ROW
statement. This is normally accomplished by committing or rolling back the autonomous transaction before executing the PIPE ROW
statement.
When a long-running transaction is interrupted by an out-of-space error condition, your application can suspend the statement that encountered the problem and resume it after the space problem is corrected. This capability is known as resumable storage allocation. It lets you avoid time-consuming rollbacks, without the need to split the operation into smaller pieces and write your own code to track its progress.
Queries, DML operations, and certain DDL operations can all be resumed if they encounter an out-of-space error. The capability applies if the operation is performed directly by a SQL statement, or if it is performed within a stored procedure, anonymous PL/SQL block, SQL*Loader, or an OCI call such as OCIStmtExecute()
.
Operations can be resumed after these kinds of error conditions:
Out of space errors, such as ORA-01653.
Space limit errors, such as ORA-01628.
Space quota errors, such as ORA-01536.
Certain storage errors cannot be handled using this technique. In dictionary-managed tablespaces, you cannot resume an operation if you run into the limit for rollback segments, or the maximum number of extents while creating an index or a table. Use locally managed tablespaces and automatic undo management in combination with this feature.
When an operation is suspended, your application does not receive the usual error code. Instead, perform any logging or notification by coding a trigger to detect the AFTER SUSPEND
event and call the functions in the DBMS_RESUMABLE
package to get information about the problem. Using this package, you can:
Parse the error message with the DBMS_RESUMABLE.SPACE_ERROR_INFO
function. For details about this function, refer to Oracle Database PL/SQL Packages and Types Reference.
Set a new timeout value with the SET_TIMEOUT
procedure.
Within the body of the trigger, you can perform any notifications, such as sending a mail message to alert an operator to the space problem.
Alternatively, the DBA can periodically check for suspended statements using the data dictionary views DBA_RESUMABLE
, USER_RESUMABLE
, and V$_SESSION_WAIT
.
When the space condition is corrected (usually by the DBA), the suspended statement automatically resumes execution. If it is not corrected before the timeout period expires, the operation causes a SERVERERROR
exception.
To reduce the chance of out-of-space errors within the trigger itself, you must declare it as an autonomous transaction so that it uses a rollback segment in the SYSTEM
tablespace. If the trigger encounters a deadlock condition because of locks held by the suspended statement, the trigger is aborted and your application receives the original error condition, as if it was never suspended. If the trigger encounters an out-of-space condition, the trigger and the suspended statement are rolled back. You can prevent the rollback through an exception handler in the trigger, and just wait for the statement to be resumed.
See Also: Oracle Database Reference for details on theDBA_RESUMABLE , USER_RESUMABLE , and V$_SESSION_WAIT data dictionary views |
This trigger handles applicable storage errors within the database. For some kinds of errors, it aborts the statement and alerts the DBA that this has happened through a mail message. For other errors that might be temporary, it specifies that the statement should wait for eight hours before resuming, with the expectation that the storage problem will be fixed by then.
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 sys.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 := 'Subject: 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;