OPS Locking Considerations

Database-level locking within an Oracle database using OPS is identical to locking in a non-OPS environment. In addition to database-level locking, we now have to consider locking caused at an operating system level, which is now initiated by the IDLM (or DLM in Oracle 7.x).

Before I discuss instance locking in detail and present an example, let’s talk about why we should care. Instance locks take much longer and require considerably more system resources than the database locks you are familiar with in the non-OPS world. Internal database locks require latches and enqueues. On most platforms, it can take as long as 1/10 of a second to acquire an instance lock. This is about 1,000 times as long as it takes to acquire both latches and enqueues, which are required for an internal database lock. Furthermore, instance locks are required in addition to internal database locks. It is because of the inordinate amount of time it takes to acquire instance locks that we need to examine instance locking in some detail.

IDLM/DLM locks are often referred to as instance locks because they are assigned to the instance, not an individual user. The significance of this is that if two users within the same instance update rows that are next to each other on the same block, only one instance lock is necessary to perform the operations.

Instance locks are often created at instance startup. Each lock has three possible states: null, shared, and exclusive. A null lock has been created but is currently unassigned. Null locks exist because of the overhead needed to create and terminate them. A shared lock allows all nodes in the cluster to read data within the lock, but no nodes can write data within the lock. You should note that this differs considerably from the way reads (or selects) are handled internally within the Oracle instance. In the non-OPS world, reads do not initiate locks and do not block writers. Hence, in the OPS world, readers can effectively prohibit or block writers.

Instance locks in exclusive mode can write data contained within the lock. Instance locks are assigned to an entire instance, so multiple users within the instance can update, insert, or delete data within the lock, but users within other nodes cannot.

There are two classes of instance locks: Parallel Cache Management (PCM) locks and Non-PCM locks. PCM locks are instance locks associated with tables, indexes, and clusters. Essentially, they are instance locks on data files. Non-PCM locks are locks used on dictionary cache and control files.

When one instance attempts to read or write data, that instance requests the appropriate PCM lock from the IDLM. If the requested lock conflicts with an existing PCM lock already held by another instance, the IDLM notifies the instance holding the existing PCM lock while the instance requesting the lock waits. This method of inter-instance communication to resolve lock conflicts through the IDLM is called pinging. When the instance holding the PCM lock acknowledges the ping and releases the requested block, the requesting instance is granted a PCM lock.

IDLM locks also differ by when they are unassigned (become null locks). Using hash locking, an IDLM lock isn’t actually unassigned until another node requests it to be unassigned. With fine-grain locking, an instance lock is unassigned after the unit of work is completed. Fine-grain locking will hurt read-only scans of tables with large result sets, but will reduce pinging. Hash-locking will potentially increase pinging activity, but will help in systems with little instance contention.