Full introduction to Oracle latches

Oracle has a lot to learn about. Here we mainly introduce Oracle latches, including the read consistency mechanism of Oracle. Oracle locks are divided into two types: Oracle latches and locks. Latches are used to protect access to the memory structure. For example, when applying for a block lock in db buffer, these db buffer blocks are unlocked only after DBWN is completed. Then it is used for other applications.

The Oracle latches cannot be shared between processes. The application for the latches either succeeds or fails, and no latches apply for a queue. Major Oracle latches include shared pool latches, library cache latches, cache buffers lru chain latches, cache buffers chains latches, redo allocation latches, and redo copy latches.

Oracle locks are used to protect data access. The lock restrictions are looser than the latches. For example, multiple users can share a lock on a table when modifying different rows in the same table, lock applications can be queued and applied in sequence according to the application order. This queuing mechanism is called queue ENPUEUE). If two server processes attempt to lock the same row of the same table, all of them enter the lock application queue. The first lock is successful, and the subsequent process will wait until the previous process is unlocked. This is called lock contention. Once the lock is successful, the lock will remain until the user issues the COMMIT or ROOLBACK command.

If two users lock their own row and request the other party to lock the row, they will wait for an indefinite period of time, that is, the deadlock occurs due to lock contention rather than lock contention, in case of a deadlock, Oracle Automatically releases the Lock of one of the users and rolls back the changes of the user. In normal cases, the final storage result of the data is determined by the SCN to determine which process changes will be saved. When two users' server processes apply for multiple row locks in the same table, they can enter the lock application queue in a staggered manner. Wait only when competition arises. The MAXTRANS parameter specified during table creation determines that a data block in the table can be locked by a maximum of several transactions at the same time.

The following describes the read consistency mechanism of Oracle. Oracle read consistency ensures high isolation between transactions. The following are several examples of read consistency and deadlock in rollback segments:

1. Large SELECT and small UPDATE
Session A ---- Select * from test; ---- set scn = 101 ---- execution time 09:10:11
B session ----- Update test set id = 9999999 where id = 1000000 ---- set scn = 102 ----- execution time 09:10:12

We will find that session B is completed before session A. The ID = 100000 displayed in session A is read from the rollback segment, because session A finds transaction information on the BLOCK when reading the BLOCK where ID = 1000000 is located, it must be read from the rollback segment. If the UPDATE statement has been committed before the SELECT statement reads this BLOCK, when the SELECT statement reads this BLOCK, it finds that there is no transaction information on the BLOCK, but it will find that its blick scn is larger than the SELECT statement's own SCN, so it will also use the rollback segment for reconstruction. Find the corresponding undo address based on all itl on the current block and reconstruct the previous block image. The previous block contains its own itl information. if the corresponding scn in the before image does not meet the query requirements, the beforebeforeimage will be generated based on the undo, so that it will continue to return results until a block that matches the query's scn is constructed, or the system really cannot construct a block that matches the query according to undo, until the error of the ora-01555 is reported ..... It should be emphasized that read consistency is achieved by using the rollback segments of all ITL records on the current block) recursively refactor the consistent snapshot of a block to a previous point in time or a SCN. Instead of performing recursive search for a part of the ITL records in a rollback segment, we must understand that recursive reconstruction and recursive search are completely different concepts. Oracle rollback segments ensure high isolation of transactions. That is, as long as the rollback segment is large enough, no matter how long a SELECT statement is executed, all the data it reads will be the value at this point in time when the SELECT statement starts to execute, it will not be affected by whether other users have modified the data during SELECT reading.

Ii. Large UPDATE and small SELECT
Session A ---- Update test set id = 1; ---- set scn = 101 ---- execution time 09:10:11
B session ----- select * from test where id = 1000000 ---- set scn = 102 ----- execution time 09:10:12

We will find that session B is completed before session A. The ID = 1000000 displayed in session B is directly read from the BLOCK, when session B reads the BLOCK where ID = 1000000 is located, session A has not been able to lock it. Therefore, session B does not find transaction information on the BLOCK, it will not find that the SCN on the BLOCK is larger than the SELECT, so it will be directly read from the BLOCK. If the SELECT statement is issued only after the UPDATE locks the BLOCK, when session B reads this BLOCK, it finds that the BLOCK contains transaction information, which is read from the rollback segment.

3. Large UPDATE and small UPDATE
Session A ---- Update test set id = 1; ---- set scn = 101 ---- execution time 09:10:11
B Session 1 ----- Update test set id = 999999 where id = 1000000 ---- set scn = 102 ----- execution time 09:10:12
B Session 2 ----- select * from test where id = 2 ---- set scn = 103 ----- execution time 09:10:14
B Session 3 ----- update test set id = 3 where id = 2 ---- set scn = 104 ----- execution time 09:10:15

We will find that session B 1 will be completed, and session A will remain waiting, because session B 1 will lock the BLOCK where ID = 1000000 is located prior to session A, and rewrite the transaction information in the header, when session A tries to lock this BLOCK and finds transaction information on it, it will wait until session B 1 finishes the transaction and then lock again, the ID = 2 found in session B 2 is read from the rollback segment rather than from the BLOCK. Because session A has locked the BLOCK with ID = 2 and written it into the rollback segment, this can be proved from session B 3. After session B 3 is issued, session B 3 receives the deadlock message because session A is waiting for session B to unlock the BLOCK where ID = 1000000 is located, now session B is waiting for session A to unlock the BLOCK where ID = 2 is located, so A deadlock is formed. This proves that the BLOCK where ID = 2 is located has been locked by session, session A also receives the deadlock information. The Oracle latches are described above.

1. Brief description of Oracle materialized view logs
2. Brief Introduction to Oracle client
3. Five-minute Oracle Tuning
4. Oracle primary Index
5. Oracle Materialized View