|
|
|
|
Oracle server uses locks to provide concurrent access to shared resources whereas it uses latches to provide exclusive and short-term access to memory structures inside the SGA. Latches also prevent more than one process to execute the same piece of code, which other process might be executing. Latch is also a simple lock, which provides serialize and only exclusive access to the memory area in SGA. Oracle doesn’t use latches to provide shared access to resources because it will increase CPU usage. Latches are used for big memory structure and allow operations required for locking the sub structures. Shared resources can be tables, transactions, redo threads, etc. Enqueue can be local or global. If it is a single instance then enqueues will be local to that instance. There are global enqueus also like ST enqueue, which is held before any space transaction can be occurred on any tablespace in RAC. ST enqueues are held only for dictionary-managed tablespaces. These oracle locks are generally known as Enqueue, because whenever there is a session request for a lock on any shared resource structure, it's lock data structure is queued to one of the linked list attached to that resource structure (Resource structure is discussed later).
Before proceeding further with this topic, here is little brief about Oracle locks. Oracle locks can be applied to compound and simple objects like tables and the cache buffer. Locks can be held in different modes like shared, excusive, null, sub-shared, sub-exclusive and shared sub-exclusive. Depending on the type of object, different modes are applied. Foe example, a compound object like a table with rows, all above mentioned modes could be applicable whereas for simple objects only the first three will be applicable. These lock modes don’t have any importance of their own but the importance is how they are being used by the subsystem. These lock modes (compatibility between locks) define how the session will get a lock on that object.
Exclusive: As it is exclusive, it prevents any concurrent access. This means when a session holds an exclusive lock on an object, no other session can have concurrent access to that shared resource.
Shared: Sessions hold shared mode of a lock on a resource when it simply wants to inspect the resource and does not want to allow another session to modify this resource while providing concurrent access to that shared resource.
Null: This lock mode doesn’t implement any restrictions for other session to acquire a lock but has the very important function of invalidating the session’s (holding the null lock) private cached information upon invalidation of the resource structure.
Sub-Shared: To provide shared access to a part of the shared resource rather than the whole object, sub-shared lock mode is used so that exclusive access to other parts of an object can be provided.
Sub-Exclusive: This mode is inline with Sub-Shared. This mode is held to provide exclusive access to a part of a shared resource, while other parts of the resource are being concurrently accessed.
Shared-Sub-Exclusive: This mode is a combination of Sub-Shared and Sub-Exclusive modes. This mode provides shared access to a part of a resource as well shared access to the whole resource.
Locks Mode Compatibility Table
Depending upon the compatibility matrix in the above table, Oracle decides at runtime whether to allow the session to lock a particular resource or to enqueue the request of the lock in one of the linked lists (Waiter, Converter) described in details further.
Oracle Kernel Enqueue Service layer (KSQ) is responsible for all managed
enqueues.
Whenever a session requires access on a resource, that session will have to hold a lock on
that resource structure Before holding a lock on a resource structure, a lock data
structure will be linked to one of the linked list (owner, waiter and converter) attached to
the
resource structure (will be discussed later). Enqueue resource is uniquely identified by
Each resource structure has three linked lists attached with them, which are owner, waiter and converter list. Each session which wants to hold a lock on a resource will have to be a member of these linked lists depending upon the mode requested by session and how many sessions already holding lock, their compatibility with others and how many sessions are already in the converter list. To understand this mechanism better, suppose there is a resource TM-256-0 as shown in the figure below and three linked lists are also attached with this resource.
Figure 1 (Resource Structure)
As mentioned earlier, each enqueue resource name identifies its address and the activities it is responsible for. In this example TM shows that it is a DML lock and the second identifier points to the object id of the table in DBA_OBEJCTS, which have been locked by this enqueue. There are three linked lists attached to resource as shown in the diagram. The small rectangle boxes are lock data structures, which were linked by a session before holding a lock on a resource. Sessions owning the lock on this resource are shown in the OWNER linked list and sessions waiting to acquire a lock and to convert the existing lock are in the other respective linked lists.
Oracle server allocates the enqueues resource structure and enqueue lock data structure in the shared pool memory area at instance startup depending upon the value of the parameters ENQUEUE_RESOURCES and _ENQUEUE_LOCKS. The default value of these parameters is derived from other parameters link session, process, number of datafile etc. Because enqueue resource does not take much space in memory, it is advisable to have some extra enqueue resources allocated.
Apart from the material already discussed, there are two more linked lists which come into the picture while acquiring or releasing a lock on resource structure. These are resource free list and lock structure free list. Resource free list keeps all unused resources which can be reused. Lock data structure free list contains unused lock data structures. Resources and lock data structures are kept on these lists based on an LRU mechanism such as buffer cache.
Acquiring a lock is a full cycle of getting an index number to identify the hash bucket in the hash table to releasing free resource structures and lock data structures. Following are steps:
Use Oracle server to find quickly the resource structure attached with named resource using the hashing algorithm. In the hashing algorithm Oracle uses hash table (Array of hash buckets), which is controlled by the parameter _ENQUEUE_HASH. The size of hash table depends upon the value set by this parameter. Hash chain contains the resource structures for that hash value. When a session tries to acquire an enqueue, Oracle server applies hash function to convert the resource name to an index number in array of hash bucket. Each hash bucket has one linked list attached with it, which is called hash chain. Before accessing the hash bucket, session acquire an enqueue hash chain as show in the following diagram to find the resource structure on hash chain attached to this hash bucket.
Figure 2
As shown in figure 2, Oracle converts resource TM-256-0 into number 3 after applying hash function. After this session acquire an enqueue hash chain latch and traverse the hash chain attached to the bucket 3 to locate the required resource structure. At this point because session acquires an enqueue hash chain so it will get recorded, miss or spin get in V$LATCH view depending upon the result of latch operation. There can be situations where the resource structure is not available on the hash chain. I mean to say resource structure is not allocated in the system yet. In this case where resource structure is not in the system, the session will acquire enqueue latch and will record statistics about the latch operation in V$LATCH. After acquiring enqueue latch, the session will unlink the head of the resource free list and link it with a hash chain attached with hash bucket 3. Enqueue latch will be held while resource is allocated to resource table.
Until this point, the session has located the resource structure. (Whether the resource structure was available on hash chain while initial search or allocated later by acquiring enqueue latch.). Enqueue hash chain latch is still held, which was acquired in the last step. Now the session will acquire the enqueue latch again and will unlink the head of lock free list. It will populate the information related to the resource being requested like mode of lock etc. Now after populating this information, the session will link this lock structure to one of the linked lists (owner, waiter or converter) attached with the resource structure, depending upon the other sessions owning that resource structure or waiting to own lock or waiting to convert the existing held lock for that resource. By this time oracle server will record the statistics in V$LATCH according to the result of the enqueue latch operation. By this time both latches enqueue and enqueue hash chain are held by this session and the session will release the enqueue latch first then enqueue hash chain latch after linking the lock data structure with the resource structure.
Now if the session is waiting in any queue (owner, waiter or converter) for another sessions to complete, enqueue wait event will be recorded in V$SESSION_EVENT.
The method to release a lock is mostly same as of acquiring a lock. First of all Oracle server will use hash function to determine the hash bucket, where resource structure is allocated. It will acquire enqueue hash chain latch and record statistics of latch operation in V$LATCH. Then it will locate the resource in hash chain (linked list attached to hash bucket identified by hash function). The session will acquire the enqueue latch and will unlink the lock data structure from resource structure and link lock data structure to lock free list and release the enqueue latch. After releasing the enqueue latch, the session will post the next process (waiter or converter) to proceed if any. Depending upon the LRU algorithm, oracle will decide whether to unlink the resource structure from hash chain and link to resource free list or not. After all of this, the session will release the enqueue hash chain and the lock will be released.
