Oracle8 Parallel Server Concepts & Administration Release 8.0 A58238-01 |
Library | Product | Contents | Index

# 10
Non-PCM Instance Locks

This chapter describes some of the most common non-PCM instance locks. It covers the following information:

- Overview
- Transaction Locks (TX)
- Table Locks (TM)
- System Change Number (SC)
- Library Cache Locks (N[A-Z])
- Dictionary Cache Locks (Q[A-Z])
- Database Mount Lock (DM)

See Also: Chapter 16, "Ensuring IDLM Capacity for All Resources & Locks", for details on how to calculate the number of non-PCM resources and locks to configure in the Integrated DLM.

## Overview

Figure 10-1 highlights non-PCM locks in relation to other locks used in Oracle.

#### Figure 10-1 Oracle Locking Mechanisms: Non-PCM Locks

Whereas PCM locks are static (you allocate them when you design your application), non-PCM locks are very dynamic. Their number and corresponding space requirements will change as your system's initialization parameter values change.

See Also: Oracle8 Reference for descriptions of all non-PCM locks.

## Transaction Locks (TX)

Row locks are locks that protect selected rows. A transaction acquires a global enqueue and an exclusive lock for each individual row modified by one of the following statements:

- INSERT
- UPDATE
- DELETE
- SELECT with the FOR UPDATE clause

These locks are stored in the block, and each lock refers back to the global transaction enqueue.

A transaction lock is acquired in exclusive mode when a transaction initiates its first change. It is held until the transaction does a COMMIT or ROLLBACK. It is also acquired exclusive by SMON when doing recovery (undo) of a transaction. Transaction locks are used as a queuing mechanism for processes which are awaiting the release of an object that is locked by a transaction in progress.

## Table Locks (TM)

Table locks are DML locks that protect entire tables. A transaction acquires a table lock when a table is modified by one of the following statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. A table lock can be held in any of several modes: null (N), row share (RS), row exclusive (RX), share lock (S), share row exclusive (SRX), and exclusive (X).

When an instance attempts to mount the database, a table lock is used to ensure that all participating instances either have DML_LOCKS = 0 or DML_LOCKS != 0. If they do not, than error ORA-61 is returned and the mount attempt fails. Table locks are acquired during the execution of a transaction when referencing a table with a DML statement so that the object is not dropped or altered during the execution of the transaction. This occurs if and only if the DML_LOCKS parameter is non-zero.

You can also selectively turn table locks on or off for a particular table, using the statement

    ALTER TABLE tablename DISABLE|ENABLE TABLE LOCK

Note that if DML_LOCKS is set to zero, then no DDL operations are allowed. The same is true for tables which have disabled table locks.

See Also: "Minimizing Table Locks to Optimize Performance" on page 16-8 to consider disabling table locks for improved performance.

## System Change Number (SC)

The System Change Number (SCN) is a logical time stamp Oracle uses to order events within a single instance, and across all instances. One of the schemes Oracle uses to generate SCNs is the lock scheme.

The lock SCN scheme keeps the global SCN in the value block of the SCN lock. This value is incremented in response to many database events, most notably COMMIT WORK. A process incrementing the global SCN will get the SCN lock in exclusive mode, increment the SCN, write the lock value block, and downgrade the lock. Access to the SCN lock value is batched. Oracle keeps a cache copy of the global SCN in memory. A process may get an SCN without any communication overhead by reading the SCN fetched by other processes.

The SCN implementation can differ from platform to platform. On most platforms, Oracle uses the lock SCN scheme when the MAX_COMMIT_PROPAGATION_DELAY initialization parameter is smaller than a platform-specific threshold (typically 7). Oracle uses the Lamport SCN scheme when MAX_COMMIT_PROPAGATION_DELAY is larger than the threshold.You can examine the alert log after an instance is started to see which SCN generation scheme has been picked.

See Also: Your Oracle system-specific documentation for information about the SCN implementation.

## Library Cache Locks (N[A-Z])

When a database object (table, view, procedure, function, package, package body, trigger, index, cluster, synonym) is referenced during parsing or compiling of a SQL (DML/DDL) or PL/SQL statement, the process parsing or compiling the statement acquires the library cache lock in the correct mode. In Oracle8 the lock is held only until the parse or compilation completes (for the duration of the parse call).

## Dictionary Cache Locks (Q[A-Z])

The data dictionary cache contains information from the data dictionary, the meta-data store. This cache provides efficient access to the data dictionary.

Creating a new table, for example, causes the meta-data of that table to be cached in the data dictionary. If a table is dropped, the meta-data needs to be removed from the data dictionary cache. To synchronize access to the data dictionary cache, latches are used in exclusive mode and in single shared mode. Instance locks are used in multiple shared (parallel) mode.

In the case of parallel server, the data dictionary cache on all nodes may contain the meta-data of a table that gets dropped on one instance. The meta-data for this table needs to be flushed from the data dictionary cache of every instance. This is performed and synchronized by instance locks.

## Database Mount Lock (DM)

The mount lock shows whether or not any instance has mounted a particular database. This lock is only used with Oracle Parallel Server. It is the only multi-instance lock used by OPS in exclusive mode, where it prevents another instance from mounting the database in shared mode.

In Oracle Parallel Server single shared mode, this lock is held in shared mode. Another instance can successfully mount the same database in shared mode. In OPS exclusive mode, however, another instance will not able to get the lock.

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