Non-PCM Instance Locks

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)

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 Commit Number (SC)

Library Cache Locks (N[A-Z]}

Dictionary Cache Locks (Q[A-Z])

Database Mount Lock (DM)

See Also: "Ensuring DLM Capacity for All Resources & Locks" for details on how to calculate the number of non-PCM resources and locks which your DLM must support.

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: Appendix E, "Enqueue and Lock Names," for descriptions of all non-PCM locks.

Transaction Locks (TX)

Row locks are DML locks that protect selected rows. A transaction acquires an exclusive lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.

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

See Also: "Minimizing Table Locks to Optimize Performance" to consider disabling table locks for improved performance.

System Change Number (SC)

The System Change Number is a unique number assigned to each transaction. It is implemented as a global lock and its value is stored in the lock value block.

The System Change Number (SCN) is used to serialize time within a single instance, and across all instances. This lock resource caches the current value of the SCN. The value is incremented in response to many database events, but most notably COMMIT WORK. Access to the SCN lock value to get and store the SCN is batched on most cluster implementations. In this way, every process that needs a new SCN gets one and stores a new value back on one instance, before the SCN lock is released. Then the SCN lock may be granted to another instance. Processes get the SC lock once and then use conversion operations to manipulate the lock value.

The SCN implementation can differ from platform to platform. Commonly, on the parallel server the DLM maintains the SCN in a lock value block of the SC resource. A process will get an instance lock on the SCN resource in exclusive mode, read the lock value block, increment the SCN, downgrade the instance lock, and write the lock value block.

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 Oracle7 the lock is held only until the parse or compilation completes (for the duration of the parse call).

Note: In V6, these locks are known as parse or DDL locks except that in V6 these locks are held for the duration of the cursor (that is, until the cursor is closed).

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

Library

Product

Contents

Index