Synchronization among multiple instances is the single most crucial factor in achieving the benefits of Oracle Parallel Server. This chapter explains the various issues involved with synchronization and also explains the locking mechanisms and other techniques used by OPS to attain it.
In a standalone database system, locks play a crucial role in maintaining concurrency, thereby allowing multiple transactions to access the same data at the same time. In a parallel server environment, locks also play an important role in allowing multiple instances to access the same data at the same time. This chapter describes the many types of locks you’ll need to be aware of as you administer an OPS system: transaction and instance locks, Parallel Cache Management (PCM) and non-PCM locks, hashed and fine-grained PCM locks, and fixed and releasable locks. We’ll also discuss lock modes, conversion, and lock allocation.
In an OPS environment, multiple instances execute transactions at the same time. Each instance maintains a buffer cache in its System Global Area (SGA) to hold copies of database blocks read from the datafiles. It is possible that more than one instance may attempt to modify the same database block at the same time. An instance always modifies the copy of the block contained within its buffer cache. If multiple instances were to modify the same block concurrently, each instance would be modifying a different copy of that block. This would result in lost updates unless there was a mechanism in place to consistently propagate changes that one instance made to a data block to other instances that are about to modify that same block.
Tip
Data blocks aren’t the only things subject to the lost update problem. Each Oracle instance maintains copies of frequently used data dictionary information in its data dictionary cache. Each instance also maintains copies of recently issued SQL statements in its shared SQL area. In an OPS environment, this information also needs to be coordinated among the various instances.
Because one instance may update data that is cached in the SGAs of one or more other instances, a mechanism is required to maintain consistency between the various cached copies of an object. This mechanism is called cache coherency. Oracle Parallel Server provides built-in mechanisms to maintain cache coherency, and these remain completely transparent to the end user. Whenever an instance accesses an object, it checks the validity of that object by coordinating with the other instances. To gain access to a database block, an instance has to acquire a lock on that data block. The mode of the lock obtained will depend on the type of access intended. For example, to read a data block, an instance would acquire the lock in shared mode, while to modify a data block, the instance would need to acquire the lock in exclusive mode. We’ll describe various types of locks and the different lock modes in the remainder of this chapter.