Oracle lock 4: system lock, oracle lock 4 System

Oracle lock 4: system lock, oracle lock 4 System

Oracle databases use multiple system locks to protect internal databases and memory structures. These mechanisms are transparent to users.

Latches)

Latches are simple and low-level serialization mechanisms that coordinate multiple users to use shared data structures, objects, and files. latches protect shared memory resources in parallel processing, the data structure is protected by bolts in the following scenarios:
1) concurrently modified by multiple sessions;
2) When read by one session, it is modified by another session;
3) re-allocate the memory when it is accessed.
Generally, a single bolt in SGA protects multiple objects. For example, backend processes (such as DBWn and LGWR) create data structures by allocating memory from the shared pool. to allocate these memories, these processes use a Shared Pool bolt to prevent both processes from simultaneously checking or modifying the Shared Pool. After the memory is allocated, other processes may need to access the Shared Pool, such as the library cache, which is used for parsing. In this case, the process latches only lock the database cache and does not lock the entire shared pool.
Unlike queue latches, such as row locks, the latches do not allow session queuing. When a latch becomes available, the first session that initiates the request gets the latch. When a process repeatedly requests a Latch in a loop, it is called latch spinning, and a process sleeps and releases the CPU while waiting for the request to be Latch, it is called latch sleeping.
Generally, an Oracle process obtains latches for a very short period of time when operating or viewing a data structure. For example, when an employee's salary is updated, the database can obtain and release thousands of locks. The implementation of latches depends on the operating system, especially on how to handle latches.
The increase in latches means that the degree of parallelism is reduced. For example, the hard parsing operation will compete for the database cache latches. V $ LATCH tries to include statistics for each type of LATCH, including the number of requests for each LATCH and the waiting time.

Mutex)

An object (mutex lock) is a low-level mechanism used to prevent a memory object from expiration or corruption during concurrent operations. Mutex locks are similar to latches, but usually latches protect a group of objects. mutex locks protect a single object.
Mutex lock is usually used:
1) reduce competition;
Because the bolts protect multiple objects, when multiple processes attempt to concurrently enter any of these objects, access to other objects by other processes will be blocked. Mutex locks reduce the scope of the lock and reduce competition.
2) mutex locks consume less memory than latches;
3) when the shared mode is used, a mutex lock can be referenced by multiple sessions in parallel.

Internal lock

Internal locks are more advanced and complex than latches and mutex locks. The database uses the following types of mutex locks:

Dictionary cache locks)

These locks take a short time and are used when the dictionary cache entries are modified or used. They ensure that the parsed statements do not see inconsistent object definitions. The dictionary cache can be shared or exclusive. when the resolution is complete, the shared lock is released, and when the DDL operation is complete, the exclusive lock is released.

File and log management locks

These locks protect various files. For example, an internal lock protects control files so that only one process can change them at a time, another method of locking and coordinating the use and archiving of online redo log files. The data file is locked to ensure that multiple instances load a database in the shared mode or an instance loads a database in the scheduled mode. Because the file and log locks indicate the state of the file, these locks must be held for a long time.

Tablespace and undo segment locks

These locks protect tablespaces and undo segments. For example, all instances of a database must be consistent for whether a tablespace is online or offline. To ensure that only one database can write an undo segment at a time, the undo segment must be locked.