How to Understand latch and enqueue lock in Oracle databases

How to Understand the latch and enqueue lock problems in Oracle databases. Everyone may have different ideas and different starting points, what is the cause of lock & latch. Then explain the nature of lock & latch from different perspectives. Www.2cto.com Lock: 1. The Source Business also disappears from the business, which is basically caused by unreasonable business process arrangement and a Lock to protect the business. 2. It also comes from concurrency. As the saying goes, there is no lock if there is no concurrency. When a large number of sessions have to compete for a modification right, what we need is an orderly queuing method to obtain access permissions. This is like waiting on a bus and queuing up to get out of the bus, no one can be crowded. 3. exclusive resources. Why do home locks and bank safes exist to protect valuables? oracle locks are also the same, when I modify a record, it is equivalent to having exclusive ownership of myself. Others will not be able to obtain it until I have modified it. This is the lock feature. 4. to ensure read consistency, what is read consistency? In simple terms, the data we see at the same time is the same. In this case, it is more complicated than actually. This benefits everyone can feel that data changes are unified, avoiding misunderstanding caused by data asymmetry. For example, if you want to watch a Super Girl concert, you need to buy a ticket first. If you want to watch the concert with her, you need to buy two tickets. When you are buying a ticket, the conductor needs to lock two tickets (whether fake or not) first. Otherwise, someone else snapped up the money while you were taking the money, at this time, my girlfriend will make today your end of the world so bad! Make sure that what you see is consistent with what you actually see. This is the source of consistency. 5. Maintain transaction integrity. From the transaction point of view, we have discussed the lock. As we all know, relational databases are born for transactions. transactions are only available in relational databases. What is the integrity of the transaction, that is, the transaction that has not been committed is invisible to others. For example, if I modify a record, this record is actually modified in the data block, but it is not submitted. At this time, it is invisible to others. If you want to query it, you can only find the snapshot before the record from the undo segment. Transaction integrity is the patron saint of data security. 6. The lock information is that an attribute on the data block is physical and does not logically belong to a table or several rows. Www.2cto.com Latch: 1. Due to insufficient system resources. For example, sga cpu SQL Parsing is a lock that protects resources. 2. It comes from the database design. I am referring to the internal principle mechanism, which is a mechanism for exclusive resources. Once a resource is grabbed, it will be immediately pinned from the inside, and it will come out after it is used up. This kind of system resources always exist, no matter whether we use them or not. With the latch mechanism, we can manage and use these system resources more effectively. 3. Latch is a lightweight lock. Why is it also a lock because it also has some features of the lock. For example, exclusive resource consistency. Latch will not cause lock-like blocking, but will only lead to waiting. This is two completely different concepts. Blocking is a problem in system design and waiting is a problem in system resource contention, this can be of great help to our judgment system. 4. latch can also be said to be a memory technology used to protect the database memory structure. It is a matter at the system level and has nothing to do with the business. For example, latch contention in share_pool is mostly caused by no Bound variables, inefficient SQL, caused by non-standard SQL. Solution: reduce SQL hard parsing, optimize SQL statements, and standardize coding rules. The latch contention in data_buffer_cache is caused by a large number of sessions accessing the same data block at the same time. We are also called Hot blocks. Solution: scatter records into multiple data blocks to reduce the probability that multiple sessions frequently access one data block at a time, this prevents hot blocks from being generated because all records are concentrated in one data block. 5. There are many latch contention issues in the oltp system due to high concurrency. In OLAP systems, because few tables are frequently modified and few SQL statements are executed, there are not many latch statements.