Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 3
Processing SQL Statements

This chapter describes how Oracle processes Structured Query Language (SQL) statements. Topics include the following:

- SQL Statement Execution
- Controlling Transactions
- Read-Only Transactions
- The Use of Cursors
- Explicit Data Locking
- Explicitly Acquiring Row Locks
- SERIALIZABLE and ROW_LOCKING Parameters
- Creating User Locks
- Sample User Locks
- Viewing and Monitoring Locks
- Concurrency Control Using Serializable Transactions

Although some Oracle tools and applications simplify or mask the use of SQL, all database operations are performed using SQL. Any other data access method would circumvent the security built into Oracle and potentially compromise data security and integrity.

## SQL Statement Execution

Figure 3-1 outlines the stages commonly used to process and execute a SQL statement. In some cases, these steps might be executed in a slightly different order. For example, the `DEFINE` stage could occur just before the `FETCH` stage, depending on how your code is written.

For many Oracle tools, several of the stages are performed automatically. Most users need not be concerned with or aware of this level of detail. However, you might find this information useful when writing Oracle applications. Refer to Oracle8 Concepts for a description of each stage of SQL statement processing for each type of SQL statement.

### FIPS Flagging

The Federal Information Processing Standard for SQL (FIPS 127-2) requires a way to identify SQL statements that use vendor-supplied extensions. Oracle provides a FIPS flagger to help you write portable applications.

When FIPS flagging is active, your SQL statements are checked to see whether they include extensions that go beyond the ANSI/ISO SQL92 standard. If any non-standard constructs are found, the Oracle Server flags them as errors and displays the violating syntax.

The FIPS flagging feature supports flagging through interactive SQL statements submitted using Enterprise Manager or SQL*Plus. The Oracle Precompilers and SQL*Module also support FIPS flagging of embedded and module language SQL.

When flagging is on and non-standard SQL is encountered, the message returned is

    ORA-00097: Use of Oracle SQL feature not in SQL92 level Level

where level can be either `ENTRY, INTERMEDIATE,` or `FULL`.

#### Figure 3-1 The Stages in Processing a SQL Statement

## Controlling Transactions

In general, only application designers using the programming interfaces to Oracle are concerned with which types of actions should be grouped together as one transaction. Transactions must be defined properly so work is accomplished in logical units and data is kept consistent. A transaction should consist of all of the necessary parts for one logical unit of work, no more and no less. 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.

A transfer of funds between two accounts (the transaction or logical unit of work), for example, should include the debit to one account (one SQL statement) and the credit to another account (one SQL statement). Both actions should either 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 transfer of funds transaction.

### Improving Performance

In addition to determining which types of actions form a transaction, when you design an application you must also determine if you can take any additional measures to improve performance. You should consider the following performance enhancements when designing and writing your application. Unless otherwise noted, each of these features is described in Oracle8 Tuning.

- Use the `BEGIN_DISCRETE_TRANSACTION` procedure to improve the performance of short, non-distributed transactions.
- Use the `SET TRANSACTION` command with the `USE ROLLBACK SEGMENT` parameter to explicitly assign a transaction to an appropriate rollback segment. This can eliminate the need to dynamically allocate additional extents, which can reduce overall system performance.
- Use the `SET TRANSACTION` command with the `ISOLATION LEVEL` set to `SERIALIZABLE` to get `ANSI/ISO` serializable transactions.

  See Also: "Serializable Transaction Interaction" on page 3-31. Oracle8 Concepts.

- Establish standards for writing SQL statements so that you can take advantage of shared SQL areas. Oracle recognizes identical SQL statements and allows them to share memory areas. This reduces memory storage usage on the database server, thereby increasing system throughput.
- Use the `ANALYZE` command to collect statistics that can be used by Oracle 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 what 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 on page 10-44.
- Create explicit cursors when writing a PL/SQL application.
- When writing precompiler programs, increasing the number of cursors using `MAX_OPEN_CURSORS` can often reduce the frequency of parsing and improve performance. The use of cursors is described on page 3-9 of this Guide.

### Committing a Transaction

To commit a transaction, use the COMMIT command. The following two statements are equivalent and commit the current transaction:

    COMMIT WORK;
    COMMIT;

The `COMMIT` command allows you to include the `COMMENT` parameter along with a Comment (less than 50 characters) 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';

For additional information about committing in-doubt distributed transactions, see Oracle8 Distributed Database Systems.

### Rolling Back a Transaction

To roll back an entire transaction or a part of a transaction (that is, to a savepoint), use the `ROLLBACK` command. 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, the `TO` option of the `ROLLBACK` command must be used. For example, either of the following statements rolls back the current transaction to the savepoint named `POINT1`:

    ROLLBACK TO SAVEPOINT point1;
    ROLLBACK TO point1;

For additional information about rolling back in-doubt distributed transactions

See Also: Oracle8 Distributed Database Systems.

### Defining a Transaction Savepoint

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 a savepoint has been created, you can roll back to the savepoint.

There is no limit on the number of active savepoints per session. An active savepoint is one that has been specified since the last commit or rollback.

#### An Example of COMMIT, SAVEPOINT, and ROLLBACK

The following series of SQL statements illustrates the use of COMMIT, SAVEPOINT, and ROLLBACK statements within a transaction:

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 had been rolled back before the COMMIT. The savepoint A is no longer active.

###

### Privileges Required for Transaction Management

No privileges are required to control your own transactions; any user can issue a `COMMIT`, `ROLLBACK`, or `SAVEPOINT` statement within a transaction.

## Read-Only Transactions

By default, the consistency model for Oracle guarantees statement-level read consistency, but does not guarantee transaction-level read consistency (repeatable reads). If you want transaction-level read consistency and your transaction does not require updates, 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 control 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.

Changed data blocks queried by a read-only transaction are reconstructed using data from rollback segments. Therefore, long running read-only transactions sometimes receive a "snapshot too old" error (ORA-01555). Create more, or larger, rollback segments to avoid this. Alternatively, you could issue long-running queries when online transaction processing is at a minimum, or you could obtain a shared lock on the table you were querying, prohibiting any other modifications during the transaction.

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

## The Use of Cursors

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 PL/SQL User's Guide and Reference.

Although most Oracle users rely on the automatic cursor handling of the Oracle 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.

### Declaring and Opening Cursors

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 system-wide limit of cursors per session is set by the initialization parameter named `OPEN_CURSORS` found in the parameter file (such as INIT.ORA). Parameters are described in Oracle8 Reference.

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, you can make sure you can open that many simultaneously.

### Using a Cursor to Re-Execute Statements

After each stage of execution, the cursor retains enough information about the SQL statement to re-execute the statement without starting over, as long as no other SQL statement has been associated with that cursor. This is illustrated in Figure on page 3-3. Notice that the statement can be re-executed 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.

### Closing Cursors

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:

- The user program terminates its connection to the server.
- If the user program is an OCI program or precompiler application, it explicitly closes any open cursor during the execution of that program. (However, when this program terminates, any cursors remaining open are implicitly closed.)

### Cancelling Cursors

Cancelling a cursor frees resources from the current fetch.The information currently in the associated private area is lost but the cursor remains open, parsed, and associated with its bind variables.

Note: You cannot cancel cursors using Pro*C or PL/SQL.

See Also: For more information about cancelling cursors, see Oracle Call Interface Programmer's Guide.

## Explicit Data Locking

Oracle always performs necessary locking to ensure data concurrency, integrity, and statement-level read consistency. However, options are available to override the default locking mechanisms. Situations where it would be advantageous to override the default locking of Oracle include the following:

- An application desires transaction-level read consistency or "repeatable reads"-transactions must query a consistent set of data for the duration of the transaction, knowing that the data has not been changed by any other transactions of the system. Transaction-level read consistency can be achieved by using explicit locking, read-only transactions, serializable transactions, or overriding default locking for the system.
- An application requires a transaction to have 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 two different levels:

transaction level | Transactions including the following SQL statements override Oracle's default locking: the `LOCK TABLE` command, the `SELECT` command including the `FOR UPDATE` clause, and the `SET TRANSACTION` command with the `READ ONLY` or `ISOLATION LEVEL SERIALIZABLE` options. Locks acquired by these statements are released after the transaction is committed or rolled back.
system level | An instance can be started with non-default locking by adjusting the initialization parameters `SERIALIZABLE` and `ROW_LOCKING`.

The following sections describe each option available for overriding the default locking of Oracle. The initialization parameter `DML_LOCKS` determines the maximum number of DML locks allowed (see the Oracle8 Reference for a discussion of parameters). The default value should be sufficient; however, if you are using additional manual locks, you may need to increase this value.

WARNING: If you override the default locking of Oracle at any level, be sure that the overriding locking procedures operate correctly; that is, be sure that data integrity is guaranteed, data concurrency is acceptable, and deadlocks are not possible or are appropriately handled.

### Explicitly Acquiring Table Locks

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` and `DEPT` tables on behalf of the containing transaction:

    LOCK TABLE emp, dept
        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 per `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, 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, the transaction does not proceed until the requested table lock is acquired. If the wait for a table lock is excessive, you might want to cancel the lock operation and retry at a later time; you can code this logic into your applications.

Note: A distributed transaction waiting for a table lock can timeout waiting for the requested lock if the elapsed amount of time reaches the interval set by the initialization parameter `DISTRIBUTED_LOCK_TIMEOUT.` Because no data has been modified, no actions are necessary as a result of the time-out. Your application should proceed as if a deadlock has been encountered. For more information on distributed transactions, refer to Oracle8 Distributed Database Systems.

The following paragraphs provide guidance on when it can be advantageous to acquire each type of table lock using the LOCK TABLE command.

#### ROW SHARE and ROW EXCLUSIVE

    LOCK TABLE table IN ROW SHARE MODE;
    LOCK TABLE table IN ROW EXCLUSIVE MODE;

Row share and row exclusive table locks offer the highest degree of concurrency. Conditions that possibly warrant the explicit acquisition of a row share or row exclusive table lock include the following:

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

#### SHARE

    LOCK TABLE table IN SHARE MODE;

Share table locks are rather restrictive data locks. The following conditions could warrant the explicit acquisition of a share table lock:

- Your transaction only queries the table and requires a consistent set of the table's data for the duration of the transaction (that is, requires transaction-level read consistency for the locked table).
- It is acceptable if other transactions attempting to update the locked table concurrently must wait until all transactions with the share table locks commit or roll back.
- It is acceptable to allow other transactions to acquire concurrent share table locks on the same table, also allowing them the option of transaction-level read consistency.

  WARNING: 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 a SELECT... FOR UPDATE statement). Therefore, if concurrent share table locks on the same table are common, updates cannot proceed and deadlocks will be common. In this case, use share row exclusive or exclusive table locks instead.

For example, assume that two tables, `EMP` and `BUDGET`, require a consistent set of data in a third table, `DEPT`. That is, 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` table in `SHARE MODE`, as shown in the following example. Because the `DEPT` table is not highly volatile, few, if any, users would need to update it while it was locked for the updates to `EMP` and `BUDGET`.

    LOCK TABLE dept IN SHARE MODE
    UPDATE EMP
        SET sal = sal * 1.1
        WHERE deptno IN
          (SELECT deptno FROM dept WHERE loc = 'DALLAS')
    UPDATE budget
        SET totsal = totsal * 1.1
        WHERE deptno IN
          (SELECT deptno FROM dept WHERE loc = 'DALLAS')

    COMMIT /* This releases the lock */

#### SHARE ROW EXCLUSIVE

    LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

Conditions that warrant the explicit acquisition of a share row exclusive table lock include the following:

- Your transaction requires both transaction-level read consistency for the specified table and the ability to update the locked table.
- You are not concerned about explicit row locks being obtained (that is, via SELECT...`FOR UPDATE`) by other transactions, which may or may not make `UPDATE` and `INSERT` statements in the locking transaction wait to update the table (that is, deadlocks might be observed).
- You only want a single transaction to have the above behavior.

#### EXCLUSIVE

    LOCK TABLE table IN EXCLUSIVE MODE;

Conditions that warrant the explicit acquisition of an exclusive table lock include the following:

- Your transaction requires immediate update access to the locked table. Therefore, if your transaction holds an exclusive table lock, other transactions cannot lock specific rows in the locked table.
- Your transaction also observes 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.

### Privileges Required

You can automatically acquire any type of table lock on tables in your schema; however, to acquire a table lock on a table in another schema, you must have the `LOCK` `ANY TABLE` system privilege or any object privilege (for example, `SELECT` or `UPDATE`) for the table.

## Explicitly Acquiring Row Locks

You can override default locking with a `SELECT` statement that includes the `FOR` `UPDATE` clause. `SELECT... FOR UPDAT`E is used to acquire exclusive row locks for selected rows (as an UPDATE statement does) in anticipation of actually updating the selected rows.

You can use a `SELECT... FOR UPDATE` statement to lock a row without actually changing it. For example, several triggers in Chapter 13, "Using Database Triggers", show how to implement referential integrity. In the `EMP_DEPT_CHECK` trigger (see page 13-29), 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 on the rows 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 before the first fetch, when the cursor is opened; rows are not individually locked as they are fetched from the cursor. Locks are only released when the transaction that opened the cursor is committed or rolled back; locks are not released when a 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. Therefore, if a `SELECT... FOR UPDATE` statement locks many rows in a table and the table experiences reasonable update activity, it would most likely improve performance if you instead acquired an exclusive table lock.

When acquiring row locks with `SELECT... FOR UPDATE`, you can indicate if you do or do not want to wait to acquire the lock. If you specify the `NOWAIT` option, you only acquire the row lock if it is immediately possible. Otherwise, an error is returned to notify you that the lock is not possible at this time. In this case, you can attempt to lock the row later. If `NOWAIT` is omitted, the transaction does not proceed until the requested row lock is acquired. If the wait for a row lock is excessive, users might want to cancel the lock operation and retry later; you can code such logic into your applications.

As described on page 3-11, a distributed transaction waiting for a row lock can timeout waiting for the requested lock if the elapsed amount of time reaches the interval set by the initialization parameter `DISTRIBUTED_LOCK_TIMEOUT.`

## SERIALIZABLE and ROW_LOCKING Parameters

Two factors determine how an instance handles locking: the `SERIALIZABLE` option of the `SET TRANSACTION` or `ALTER SESSION` command and the `ROW_LOCKING` initialization parameter. By default, `SERIALIZABLE` is set to `FALSE` and `ROW_LOCKING` is set to `ALWAYS`.

In almost every case, these parameters should not be altered. They are provided for sites that must run in ANSI/ISO compatible mode, or that want to use applications written to run with earlier versions of Oracle. Only these sites should consider altering these parameters, as there is a significant performance degradation caused by using other than the defaults.

See Also: For detailed explanations of these parameters, see Oracle8 Reference.

The settings for these parameters should be changed only when an instance is shut down. If multiple instances are accessing a single database, all instances should use the same setting for these parameters.

### Summary of Non-Default Locking Options

Three combinations of settings for `SERIALIZABLE` and `ROW_LOCKING`, other than the default settings, are available to change the way locking occurs for transactions. Table 3-1 summarizes the non-default settings and why you might choose to execute your transactions in a non-default way.

##### Table 3-1 Summary of Non-Default Locking Options

Case | Description | SERIALIZABLE | ROW_LOCKING
1 | Equivalent to Version 5 and earlier Oracle releases (no concurrent inserts, updates, or deletes in a table). | Disabled (default) | INTENT
2 | ANSI compatible. | Enabled | ALWAYS
3 | ANSI compatible, with table-level locking (no concurrent inserts, updates, or deletes in a table). | Enabled | INTENT

Table 3-2 illustrates the difference in locking behavior resulting from the three possible settings of the `SERIALIZABLE` option and `ROW_LOCKING` initialization parameter, as shown in Table 3-1.

##### Table 3-2 Non-default Locking Behavior

STATEMENT | CASE 1 | CASE 2 | CASE 3
| row | table | row | table | row | table
SELECT | - | - | - | S | - | S
INSERT | X | SRX | X | RX | X | SRX
UPDATE | X | SRX | X | SRX | X | SRX
DELETE | X | SRX | X | SRX | X | SRX
SELECT...FOR UPDATE | X | RS | X | S | X | S
LOCK TABLE . . . IN . . |  |  |  |  |  |
ROW SHARE MODE | RS | RS | RS | RS | RS | RS
ROW EXCLUSIVE MODE | RX | RX | RX | RX | RX | RX
SHARE MODE | S | S | S | S | S | S
SHARE ROW EXCLUSIVE MODE | SRX | SRX | SRX | SRX | SRX | SRX
EXCLUSIVE MODE | X | X | X | X | X | X
DDL statements | - | X | - | X | - | X

## Creating User Locks

You can use Oracle Lock Management services for your applications. 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 lock, it has all the functionality of an Oracle lock, such as deadlock detection. Be certain that any user locks used in distributed transactions are released upon COMMIT, or an undetected deadlock may occur.

### The DBMS_LOCK Package

The Oracle Lock Management services are available through procedures in the `DBMS_LOCK` package. Table 3-3Summarizes the procedures available in the `DBMS_LOCK` package.

##### Table 3-3 DBMS_LOCK Package Functions and Procedures

Function/Procedure | Description | Refer to
--- | --- | ---
    ALLOCATE_UNIQUE
| Allocate a unique lock ID to a named lock. | page 3-19
    REQUEST
| Request a lock of a specific mode. | page 3-21
    CONVERT
| Convert a lock from one mode to another. | page 3-23
    RELEASE
| Release a lock. | page 3-25
    SLEEP
| Put a procedure to sleep for a specified time. | page 3-25

User locks never conflict with Oracle locks because they are identified with the prefix "UL". You can view these locks using the Enterprise Manager lock monitor screen or the appropriate fixed views.

User locks are automatically released when a session terminates.

WARNING: This implementation does not efficiently support more than a few hundred locks per session. Oracle strongly recommends that you develop a standard convention be developed for using these user locks. This avoids conflicts among procedures trying to use the same locks. For example, you might want to include your company name as part of the lock name to ensure that your lock names do not conflict with lock names used in any Oracle supplied applications.

### Security

There might be operating system-specific limits on the maximum number of total locks available. This must be considered when using locks or making this package available to other users. Consider granting the `EXECUTE` privilege only to specific users or roles.

A better alternative would be to create a cover package limiting the number of locks used and grant `EXECUTE` privilege to specific users. An example of a cover package is documented in the `DBMSLOCK.SQL` package specification file.

See Also: See the Commented-out package `LOCK_100_TO_200`.

### Creating the DBMS_LOCK Package

To create the `DBMS_LOCK` package, submit the `DBMSLOCK.SQL` and `PRVTLOCK`.`PLB` scripts when connected as the user `SYS`. These scripts are run automatically by the `CATPROC.SQL` script.

See Also: See page 10-62 for information on granting the necessary privileges to users who will be executing this package.

### ALLOCATE_UNIQUE Procedure

Lock identifiers are used to allow applications to coordinate their use of locks. User-assigned lock identifiers can be a number in the range of 0 to 1073741823, or locks can be identified by name. If you choose to identify locks by name, you can use `ALLOCATE_UNIQUE` to generate a unique lock identification number for these named locks.

WARNING: Named user locks may be less efficient, as Oracle uses SQL to determine the lock associated with a given name.

The parameters for the `ALLOCATE_UNIQUE` procedure are described in Table 3-4. The syntax for this procedure is shown below.

    DBMS_LOCK.ALLOCATE_UNIQUE(lockname         IN VARCHAR2,
                              lockhandle      OUT VARCHAR2,
                              expiration_secs  IN INTEGER
                                      DEFAULT 864000);

##### Table 3-4 DBMS_LOCK.ALLOCATE_UNIQUE Procedure Parameters

Parameter | Description
--- | ---
    LOCKNAME
| Specify the name of the lock for which you want to generate a unique `ID`. The first session to call `ALLOCATE_UNIQUE` with a new lock name causes a unique lock ID to be generated and stored in the `DBMS_LOCK_ALLOCATED` table. The handle to this `ID` is then returned for this call, and all subsequent calls (usually by other sessions). Lock `IDs` assigned by ALLOCATE_UNIQUE are in the range of 1073741824 to 1999999999. Do not use lock names beginning with `ORA$`; these names are reserved for products supplied by Oracle Corporation.
    LOCKHANDLE
| Returns to the caller the handle to the lock ID generated by `ALLOCATE_UNIQUE`. You can use this handle in subsequent calls to `REQUEST, CONVERT,` and `RELEASE.` `LOCKHANDLE` can be up to `VARCHAR2(128).` A handle is returned instead of the actual lock `ID` to reduce the chance that a programming error can accidentally create an incorrect, but valid, lock `ID`. This provides better isolation between different applications that are using this package. All sessions using a lock handle returned by `ALLOCATE_UNIQUE` using the same lock name are referring to the same lock. Different sessions can have different lock handles for the same lock, so do not pass lock handles from one session to another.
    EXPIRATION_SECS
| Specify the number of seconds to wait after the last `ALLOCATE_UNIQUE` has been performed on a given lock, before allowing that lock to be deleted from the `DBMS_LOCK_ALLOCATED` table. The default waiting period is 10 days. You should not delete locks from this table. Subsequent calls to `ALLOCATE_UNIQUE` may delete expired locks to recover space.

### REQUEST Function

To request a lock with a given mode, use the `REQUEST` function. `REQUEST` is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the `ALLOCATE_UNIQUE` procedure.

The parameters for the REQUEST function are described in Table 3-5 and the possible return values and their meanings are described in Table 3-6. The syntax for this function is shown below.

    DBMS_LOCK.REQUEST(id                IN INTEGER ||
                     lockhandle         IN VARCHAR2,
                     lockmode           IN INTEGER DEFAULT X_MODE,
                     timeout            IN INTEGER DEFAULT MAXWAIT,
                     release_on_commit  IN  BOOLEAN DEFAULT FALSE,
    RETURN INTEGER;

The default values, such as X_MODE and MAXWAIT, are defined in the DBMS_LOCK package specification. See the package specification, available on-line, for the current default values.

##### Table 3-5 DBMS_LOCK.REQUEST Function Parameters

Parameter | Description
--- | ---
    ID
    or
    LOCKHANDLE
| Specify the user assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by `ALLOCATE_UNIQUE`, of the lock whose mode you want to change.
    LOCKMODE
| Specify the mode that you are requesting for the lock. The available modes and their associated integer identifiers are listed below. The abbreviations for these locks, as they appear in the V$ views and Enterprise Manager monitors are shown in parentheses. 1 - null mode 2 - row share mode (`ULRS`) 3 - row exclusive mode (`ULRX`) 4 - share mode (`ULS`) 5 - share row exclusive mode (`ULRSX`) 6 - exclusive mode (`ULX`) Each of these lock modes is explained in Oracle8 Concepts.
    TIMEOUT
| Specify the number of seconds to continue trying to grant the lock. If the lock cannot be granted within this time period, the call returns a value of 1 (timeout).
    RELEASE_ON_COMMIT
| Set this parameter to `TRUE` to release the lock on commit or rollback. Otherwise, the lock is held until it is explicitly released or until the end of the session.

##### Table 3-6 DBMS_LOCK.REQUEST Function Return Values

Return Value | Description
--- | ---
0 | success
1 | timeout
2 | deadlock
3 | parameter error
4 | already own lock specified by ID or LOCKHANDLE
5 | illegal lock handle

### CONVERT Function

To convert a lock from one mode to another, use the `CONVERT` function. `CONVERT` is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the `ALLOCATE_UNIQUE` procedure.

The parameters for the CONVERT function are described in Table 3-7 and the possible return values and their meanings are described in Table 3-8. The syntax for this function is shown below.

    DBMS_LOCK.CONVERT(
            id         IN INTEGER ||
            lockhandle IN VARCHAR2,
            lockmode   IN INTEGER,
            timeout    IN NUMBER DEFAULT MAXWAIT)
    RETURN INTEGER;

`

##### Table 3-7 DBMS_LOCK.CONVERT Function Parameters

Parameter | Description
--- | ---
    ID
    or
    LOCKHANDLE
| Specify the user assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by `ALLOCATE_UNIQUE`, of the lock whose mode you want to change.
    LOCKMODE
| Specify the new mode that you want to assign to the given lock. The available modes and their associated integer identifiers are listed below. The abbreviations for these locks, as they appear in the V$ views and Enterprise Manager monitors are shown in parentheses. 1 - null mode 2 - row share mode (`ULRS`) 3 - row exclusive mode (`ULRX`) 4 - share mode (`ULS`) 5 - share row exclusive mode (`ULRSX`) 6 - exclusive mode (`ULX`) Each of these lock modes is explained in Oracle8 Concepts.
    TIMEOUT
| Specify the number of seconds to continue trying to change the lock mode. If the lock cannot be converted within this time period, the call returns a value of 1 (timeout).

`

`

##### Table 3-8 DBMS_LOCK.CONVERT Function Return Values

Return Value | Description
--- | ---
             0
| success
             1
| timeout
             2
| deadlock
             3
| parameter error
             4
| don't own lock specified by `ID` or `LOCKHANDLE`
             5
| illegal lock handle

`

### RELEASE Function

To explicitly release a lock previously acquired using the `REQUEST` function, use the `RELEASE` function. Locks are automatically released at the end of a session. `RELEASE` is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the `ALLOCATE_UNIQUE` procedure.

The parameters for the RELEASE function are described in Table 3-9 and the possible return values and their meanings are described in Table 3-10. The syntax for this function is shown below.

    DBMS_LOCK.RELEASE(id         IN INTEGER)
    RETURN INTEGER;
    DBMS_LOCK.RELEASE(lockhandle IN VARCHAR2)
    RETURN INTEGER;

`

##### Table 3-9 DBMS_LOCK.RELEASE Function Parameter

Parameter | Description
--- | ---
    ID
    or
    LOCKHANDLE
| Specify the user-assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by `ALLOCATE_UNIQUE`, of the lock that you want to release.

` `

##### Table 3-10 DBMS_LOCK.RELEASE Function Return Values

Return Value | Description
--- | ---
    0
| success
    3
| parameter error
    4
| do not own lock specified by `ID` or `LOCKHANDLE`
    5
| illegal lock handle

`

## SLEEP Procedure

To suspend the session for a given period of time, use the `SLEEP` procedure.

The parameters for the `SLEEP` procedure are described in Table 3-11. The syntax for the `SLEEP` procedure is shown below.

    DBMS_LOCK.SLEEP(seconds  IN NUMBER);

`

##### Table 3-11 DBMS_LOCK.SLEEP Procedure Parameters

Parameter | Description
--- | ---
    SECONDS
| Specify the amount of time, in seconds, to suspend the session. The smallest increment can be entered in hundredths of a second; for example, 1.95 is a legal time value.

`

## Sample User Locks

Some uses of user locks are:

- providing exclusive access to a device, such as a terminal
- providing application-level enforcement of read locks
- detect when a lock is released and cleanup after the application
- synchronizing 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, above that 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.

## Viewing and Monitoring Locks

Oracle provides two facilities to display locking information for ongoing transactions within an instance

:

Enterprise Manager Monitors (Lock and Latch Monitors) | The Monitor feature of Enterprise Manager provides two monitors for displaying lock information of an instance. Refer to Oracle Server Manager User's Guide for complete information about the Enterprise Manager monitors.
`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`.)

## Concurrency Control Using Serializable Transactions

By default, the Oracle Server 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. In some cases, however, it is advantageous to allow 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. In other words, concurrent transactions executing in serialized mode are only permitted to make database changes that they could have made if the transactions were scheduled to run one after the other.

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 3-12.

##### Table 3-12 ANSI Isolation Levels

Isolation Level | Dirty Read (1) | Non-Repeatable Read (2) | Phantom Read (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

Notes: | (1) A transaction can read uncommitted data changed by another transaction.
| (2) A transaction re-read data committed by another transaction and sees the new data
| (3) A transaction can re-execute a query, and discover new rows inserted by another committed transaction

The behavior of Oracle with respect to these isolation levels is summarized below

.

`READ UNCOMMITTED` | Oracle never permits "dirty reads." This is not required for high throughput with Oracle.
`READ COMMITTED` | Oracle meets the READ COMMITTED isolation standard. This is the default mode for all Oracle applications. Note that since an Oracle query only sees data that was committed at the beginning of the query (the snapshot time), Oracle offers more consistency than actually required by the ANSI/ISO SQL92 standards for READ COMMITTED isolation.
`REPEATABLE READ` | Oracle does not support this isolation level, except as provided by SERIALIZABLE.
`SERIALIZABLE` | You can set this isolation level using the SET TRANSACTION command or the ALTER SESSION command, as described on page 3-31.

#### Figure 3-2 Time Line for Two Transactions

### Serializable Transaction Interaction

Figure 3-2 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 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.

### Setting the Isolation Level

You can change the isolation level of a transaction using the `ISOLATION LEVEL` clause of the `SET TRANSACTION` command. The `SET TRANSACTION` command must be the first command issued in a transaction. If it is not, the following error is issued:

    ORA-01453: SET TRANSACTION must be first statement of transaction

Use the `ALTER SESSION` command to set the transaction isolation level on a session-wide basis.

See Also: Oracle8 SQL Reference for the complete syntax of the `SET TRANSACTION` and `ALTER SESSION` commands.

#### The INITRANS Parameter

Oracle stores control information in each data block to manage access by concurrent transactions. Therefore, if you set the transaction isolation level to serializable, you must use the `ALTER TABLE` command to set `INITRANS` to at least 3. This parameter will cause Oracle 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.

### Referential Integrity and Serializable Transactions

Because Oracle 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 3-3 two different transactions that perform application-level checks to maintain the referential integrity parent/child relationship between two tables. One transaction reads the parent table to determine that a row with a specific primary key value exists before inserting corresponding child rows. The other transaction checks to see that no corresponding detail rows exist before proceeding to delete a parent row. In this case, both transactions assume (but do not ensure) that data they read will not change before the transaction completes.

#### Figure 3-3 Referential Integrity Checks

Note that the read issued by transaction A does not prevent transaction B from deleting the parent row. Likewise, transaction B's query for child rows does not prevent the insertion of child rows by transaction A. Therefore the above scenario leaves in the database a child row with no corresponding parent row. This result would occur 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 illustrates, for some transactions, application developers must specifically 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.

#### Using SELECT FOR UPDATE

Fortunately, it is straightforward in Oracle to prevent the anomaly described above. 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 using database triggers, instead of a separate query as in Transaction A above. For example, an `INSERT` into the child table can fire a `PRE-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) will remain 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's 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 will correctly enforce referential integrity as explained above.

### READ COMMITTED and SERIALIZABLE Isolation

Oracle gives the application developer 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 provide the contention-reducing benefits of Oracle's "read consistency" multi-version concurrency control model and exclusive row-level locking implementation, and are designed for real-world application deployment. The rest of this section compares the two isolation modes and provides information helpful in choosing between them.

#### Transaction Set Consistency

A useful way to describe the `READ COMMITTED` and `SERIALIZABLE` isolation levels in Oracle is to consider the following:

- a collection of database tables (or any set of data)
- a particular sequence of reads of rows in those tables
- the set of transactions committed at any particular time

An operation (a query or a transaction) is "transaction set consistent" if all its reads return data written by the same set of committed transactions. In an operation that is not transaction set consistent, some reads reflect the changes of one set of transactions, and other reads reflect changes made by other transactions. An operation that is not transaction set consistent in effect sees the database in a state that reflects no single set of committed transactions.

Oracle provides transactions executing in `READ COMMITTED` mode with transaction set consistency on a per-statement basis (since all rows read by a query must have been committed before the query began). Similarly, Oracle `SERIALIZABLE` mode provides transaction set consistency on a per-transaction basis, since all statements in a `SERIALIZABLE` transaction execute with respect to an image of the database as of the beginning of the transaction.

In other database systems (unlike in Oracle), 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. This means, for example, that 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. Oracle's `READ` `COMMITTED` mode will not experience this effect, 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.

#### Functionality Comparison Summary

Table 3-13 summarizes key similarities and differences between READ COMMITTED and SERIALIZABLE transactions.

##### Table 3-13 Read Committed vs. Serializable Transaction

| 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

#### Choosing an Isolation Level

Application designers and developers should choose an isolation level that is appropriate to the specific application and workload, and may choose different isolation levels for different transactions. The choice should be based on performance and consistency needs, and consideration of application coding requirements.

For environments with many concurrent users rapidly submitting transactions, designers must assess transaction performance requirements in terms of the expected transaction arrival rate and response time demands, and choose an isolation level that provides the required degree of consistency while satisfying performance expectations. Frequently, for high performance environments, the choice of isolation levels involves making a trade-off between consistency and concurrency (transaction throughput).

Both Oracle isolation modes provide high levels of consistency and concurrency (and performance) through the combination of row-level locking and Oracle's multi-version concurrency control system. Because readers and writers don't block one another in Oracle, 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 reads don't block writes in either mode.

### Application Tips

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 results in the following error:

    ORA-08177: Can't serialize access for this transaction.

When you get an `ORA-08177` error, the appropriate action is to roll back the current transaction, and re-execute it. After a rollback, the transaction acquires a new transaction snapshot, and the DML operation is likely to succeed.

Since a rollback and repeat of the transaction is required, it is good development practice to put DML statements that might conflict with other concurrent transactions towards the beginning of your transaction, whenever possible.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index