An OPS database may have various types of users. Some users (we’ll call them readers) may be reading the data, and other users (we’ll call them writers) may be modifying the data. Depending on the type of access required, instance locks are acquired in one of the following modes. Note that the null mode has the lowest level of access rights and that the exclusive mode has the highest level of access rights.
- Null mode (NULL)
Locks generally are created in null mode and then converted to other modes as required. A lock in null mode indicates no access rights. Multiple instances can hold a lock in null mode.
- Subshared (SS)
An SS lock allows an unprotected read on an object. When an instance is holding an SS lock on an object, it is allowed to read that object. However, other instances may be able to read as well as modify the object. Multiple instances can hold a lock in SS mode.
- Shared exclusive (SX)
An SX lock allows an unprotected write on an object. When an instance is holding an SX lock on an object, it is allowed to modify (write) that object. However, other instances also may be able to read and modify (write) the same object. Multiple instances can hold a lock in SX mode.
- Shared mode (S)
Shared locks are needed for read operations. Multiple instances can own an instance lock in shared mode and simultaneously can read the associated block(s). Writes are not allowed on a block when it is locked in shared mode.
- Subshared exclusive (SSX)
When an instance is holding an SSX lock on an object, that instance is the only one allowed to modify (write) the object. However, other instances may be able to perform unprotected reads on the object. Only one instance can hold a lock in SSX mode; locks on rollback segment blocks normally are acquired in this mode.
- Exclusive mode (X)
Exclusive mode allows the protected write of a data block. As the name conveys, only one instance can hold a lock in exclusive mode at any given moment. When an instance holds a lock in X mode, no other instance can read or modify the associated block(s).
SS, SX, and SSX lock modes allow unprotected read or write. User operations do not directly involve any unprotected reads or writes. These three lock modes are used as intermediate lock modes. The null, S, and X lock modes directly correspond to user operations. In Table 8.2, we describe the compatibility of the null, S, and X lock modes. The table shows the results that you’ll see when one instance attempts to lock an object that’s already locked by another instance. A result of Success indicates that the lock modes are compatible and that no lock conversion is necessary. More than one instance can own a lock when the modes are compatible. For example, two instances can own a lock in shared mode when both the instances are reading the data. A result of Failure indicates that two lock modes are incompatible, and therefore the instance owning the current lock must release that lock before the requesting instance can acquire the lock that it needs.
Tip
Whenever the requesting instance requests a lock in a mode that is incompatible with the lock mode held by the holding instance, lock mode conversion takes place. The instance holding the lock must downgrade its lock mode. Only then can the requesting instance acquire the lock in the desired mode. The examples of lock mode conversion are noted as footnotes 5, 6, and 7 to Table 8.2.
Table 8-2. Lock Modes
|
Lock Mode Requested |
Lock Mode of the Holding Instance | ||
|---|---|---|---|
|
NULL |
S (reader) |
X (writer) | |
|
NULL |
Success[a] |
Success[b] |
Success[b] |
|
S (reader) |
Success[c] |
Success[d] |
Fail[e] |
|
X (writer) |
Success[c] |
Fail[f] |
Fail[g] |
[a] Multiple instances can hold a lock in NULL mode. Does not cause any cache coherency conflicts. [b] The requesting instance can acquire the lock in NULL mode regardless of the lock mode of the holding instance. That’s because the NULL mode does not convey any access rights. Does not cause any cache coherency conflicts. [c] When one instance holds a lock in NULL mode, another instance can acquire it in any desired mode without requiring lock mode conversion in the owning instance. Does not cause any cache coherency conflicts. [d] Multiple instances can own an instance lock in shared mode and simultaneously can read the associated block(s). Does not cause any cache coherency conflicts. [e] When an instance holds a lock in X mode, no other instance can even read the associated block(s) because doing so would result in an inconsistent read. This is a reader/writer conflict. If the second instance requests the lock in S mode, the request fails and initiates a lock mode conversion in the owning instance. The owning instance then must downgrade its lock mode to null or release the lock before the requesting instance can acquire it. [f] If one instance is reading a block by holding an S lock on it, another instance cannot write on that block. This is a writer/reader conflict. If the second instance requests the lock in X mode, the request fails and initiates a lock mode conversion in the owning instance. The owning instance then must downgrade its lock mode to null or release the lock before the requesting instance can acquire it. [g] When an instance holds a lock in X mode, no other instance can write the associated block(s) because doing so would result in a lost update. This is a writer/writer conflict. If the second instance requests the lock in X mode, the request fails and initiates a lock mode conversion in the owning instance. The owning instance then must downgrade its lock mode to null or release the lock before the requesting instance can acquire it in X mode. | |||