Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the difference between Latch and Lock". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

This article describes the Latch mechanism of Oracle, Latch, which is translated by Jinshan Ciba as door plug, lock, and the technical term is latch. When I first came into contact, I didn't quite understand why I didn't write Lock, wasn't it all locks? Just because the translation is different? Only after the study did I know that there is a big difference between the two.

The information locked in Oracle is part of a data block, is physical, and does not logically belong to a table or row. Latch is a lightweight lock resource provided by Oracle, which is used to lock resources quickly and for a short time, to prevent multiple concurrent processes from modifying to access a shared resource at the same time, he only works in memory, we can not say exactly, the lock of resources in memory is called latch, and the lock of database objects (tables, indexes, etc.) is called Lock. For example, if a block in the data cache is to be read, we will get the latch of this block. This process is called pin. Another process happens to modify this block, and he also wants to pin this block. At this time, he must wait for the current process to release latch before it can pin live, and then modify it. If multiple processes request at the same time, there will be competition between them, without a queue mechanism, once the previous process releases the set. There is no concept of "first come, first come". This is essentially different from Lock. It all happens very quickly, because the characteristic of Latch is fast and short. Of course, this is only a rough process. The details are discussed later.

Let's take a look at the difference between Latch and Lock.

1. Latch is a mechanism that provides mutually exclusive access to in-memory data structures, while Lock acquires shared resource objects in different modes, and there is compatibility or exclusion among different schemas. From this point, Latch access, including queries, is also mutually exclusive. At any time, only one process can pin a certain piece of memory. Fortunately, this process is quite short, otherwise there will be no guarantee of system performance. Now, starting with 9i, many processes are allowed to query the same block of memory at the same time, but the performance is not as good as expected.

2. Latch only acts in memory and can only be accessed by the current instance, while Lock acts on database objects and allows Lock detection and access between instances in the RAC system.

3. Latch is an instant occupation and release. The release of Lock needs to wait until the transaction ends correctly. The length of time it takes is determined by the size of the transaction.

4. Latch is not on the team, but Lock is on the team.

5. Deadlocks do not exist in Latch, but exist in Lock (deadlocks are very rare in Oracle)

Take a look at the following example, you will feel the existence of Latch

SQL > CREATE TABLE MYTEST AS SELECT OBJECT_NAME FROM USER_OBJECTS WHERE ROWNUM SET TIMING ON

SQL >

DECLARE lv_name VARCHAR2 (25): ='

BEGIN

FOR i IN 1..100000 LOOP

SELECT OBJECT_NAME INTO lv_name FROM MYTEST WHERE ROWNUM = 1

END LOOP

END

/

PL/SQL procedure successfully completed

Executed in 3.359 seconds

This process keeps accessing the same data block on the table. It will first physically read the data block into the data buffer, and then keep getting the latch of this block in memory. Now there is only a single process, and it takes more than 3 seconds to run 100000 times. But when I pull out four windows to run this statement concurrently, there is a problem. Multiple processes PIN the same data block, each taking about 15 seconds. And see the end of them one by one, and in the end, there is only one moment left, because no one has robbed him. This experiment shows the phenomenon of Latch competition. I doubt that the query put forward by 9i can share Latch.

The main reasons for Latch are:

1. Too many sessions access the same data block, resulting in hot blocks.

2. There are too many sql statements in the shared pool that need soft parsing, and no binding variables are used.

Now let's take a look at the detailed process of getting Latch. At any time, only one process can access a certain block in memory (I don't want to consider the Latch sharing proposed by 9i). If a process cannot get Latch because another process is occupying the block, he will spin the CPU. The time is very short. Spin continues to be obtained after spin, but it is still spin if it is not successful. Until the number of spin reaches the threshold limit (this is specified by the implicit parameter _ spin_count), the process will stop the spin, sleep for a short time, and then continue the previous action until the Latch on the block is obtained. There is also an algorithm for the dormancy time of the process, which increases with the number of spin, in centiseconds, such as 1, 1, 2, 2, 4, 4, 8, and 8. The threshold limit of sleep is controlled by the implicit parameter _ max_exponential_sleep. The default is 2 seconds. If the current process already occupies another Latch, its sleep time will not be too long (too long will cause other processes to wait for Latch). At this time, the maximum sleep time is determined by the implicit parameter _ max_sleep_holding_latch, and the default is 4 seconds. This time-limited hibernation is also known as short-term waiting, another case is long-term waiting for latch (Latch Wait Posting), when the waiting process requests Latch is not successful, enter hibernation, he will press a signal to the latch waiting list (Latch Wait List) to indicate the request to obtain Latch, when the occupying process releases Latch, it will check Latch Wait List, send a signal to the requested process and activate the dormant process. Latch Wait List is a list of processes maintained in the SGA area, and it also needs Latch to ensure its normal operation. By default, share pool latch and library cache latch use this mechanism. If the implicit parameter _ latch_wait_posting is set to 2, all Latch adopt this waiting mode. This way can accurately wake up a waiting process, but maintaining Latch Wait List requires system resources. And there may also be bottlenecks in the competition for Latch on Latch Wait List.

If a process requests, rotates, and hibernates the Latch for a long time, it will notify the PMON process to see if the occupied process of the Latch has been unexpectedly terminated or dead, and if so, PMON will clear the Latch resources occupied by the release.

Now you can understand the process of getting Latch, request-SPIN- hibernation-request-SPIN- hibernation. Occupied, someone here will ask why SPIN, why not just hibernate and wait? To understand what dormancy means, it means temporarily abandoning CPU and context switch, so that CPU needs to save some state information of the current process when it is running, such as stack, semaphore and other data structures, and then introduce the state information of subsequent processes, and then switch back to the original process state after processing, if this process occurs frequently in a high transaction. In the processing system of highly concurrent processes, it will be a very expensive resource consumption, so he chose spin, let the process continue to occupy CPU, run some empty instructions, then continue the request, continue spin until the value of _ spin_ count is reached, then give up CPU, have a short sleep, and then continue the action just now. Oracle software is designed like this, the masterpiece of the masters of the world, naturally has his reason. I won't waste any more words on it.

It is inevitable for the system to wait for Latch, because this is how Oracle works. When you see a very high Latch get, it does not mean that your system needs to adjust. Sometimes there is only a very short waiting time behind a very high getvalue. The object we adjust should be delineated by the time consumed, instead of seeing a high secondary value, of course. When the waiting time for getting the value is hundreds of thousands of times higher than other waiting times, we still need to care. Oracle waits for a lot of Latch, mainly including share pool,library cache,cache buffer chains,buffer busy wait. Almost every adjustment can be written on a few pages, and then finish it slowly.

This is the end of the content of "what's the difference between Latch and Lock". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.