Oracle7 Server Application Developer'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 Oracle7 Server Reference manual 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 Oracle7 Server Distributed Systems, Volume I.

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.

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.