Oracle Parallel Processing

Non-PCM Locks

In an Oracle Parallel Server environment, several database resources are shared across multiple instances. As we’ve discussed previously, data blocks are protected by PCM locks. All other shared resources are protected by non-PCM locks. When parallel server is enabled, local enqueues become global. These are included in the non-PCM locks. The most important non-PCM lock types are the following:

Transaction locks

These locks are row-level locks acquired by transactions during the execution of INSERT, UPDATE, DELETE, and SELECT FOR UPDATE statements. These locks are acquired in exclusive mode and are held for the life of the transaction. Transaction locks are released when the transaction is committed or rolled back.

DML locks

These locks protect an entire table during a DML or DDL operation. The number of DML locks is controlled by the DML_LOCKS initialization parameter. If this parameter is set to zero in one instance, then it must be set to zero in all of the instances. Setting DML_LOCKS to zero disables table locking for the whole database. The advantage of setting this parameter to zero is that doing so eliminates contention and overhead for these locks. However, the disadvantage is that you cannot perform any DDL operations such as DROP TABLE, CREATE INDEX, LOCK TABLE, and so forth.

System Change Number lock

Oracle uses a number called the System Change Number (SCN) to record the proper sequencing of changes to the database. The SCN is global to all instances in a parallel ...