Oracle latch, lock, and concurrency

The database system itself is a multi-user concurrent processing system, at the same time, there may be more than one user can operate the database simultaneously. Here are two very important questions to be addressed.

The operations between these users do not destroy each other. For example, two users can not overwrite each other when they write data in the same physical location at the same time. This is called serialization, that is, even if two users write at the same time, must have successively, one user finished, another user to continue to write. Serialization reduces the concurrency of the system, but it is necessary to protect the data structures from being corrupted.

In the context of the serialization, how to increase the concurrency to the maximum.

In an Oracle database, these two issues are resolved through latches (latch) and locks (lock). Latches and locks have both the same point and different points. The same point is that they are all resources used to implement serialization. The difference is that the latch is a low-level, lightweight lock that gets and releases quickly and is implemented in a similar way to a semaphore. Locking is likely to last for a long time, by using queues, to be implemented in a first-in, first-out manner. It can also be simply understood that the latch is in the microscopic field, while the lock-in is the macroscopic domain.

After reading this chapter, we can learn:

What the latch is, and how the latch protects the resource;

How locks (including TX locks and TM Locks) protect resources;

How to detect and resolve locking conflicts;

The concept of DDL locking;

How to create our own locks.

Latch (LATCH) Overview

The Oracle database uses latches to manage the allocation and release of memory. Suppose that a user process (assuming that it is a) emits an UPDATE statement to update a record in block 58th. The server process corresponding to the user process, when writing memory, finds the number 58th block and writes the contents to it. A in the process of writing block 58th, another user process B emits an INSERT statement to insert a new record into the number 58th block of data. Without a certain protection mechanism, the space that a is about to write may be preempted by B, or, conversely, the space to be written by B may be preempted by a. Whichever user writes first, the result is that the data in block 58th is confusing because the data between A and B are intertwined.

Therefore, you must use latch to protect this. In short, any process to write a block of data, must first obtain latch, in the writing process, always hold the latch, after writing, release the latch. For the above example, when a is writing the number 58th block, the first gets the latch and then starts writing. And when a is in the process of writing, B will also write a number 58th data block. When B tries to get latch, it finds that the latch is being held by another user (i.e. a), so B enters the wait state. Until a writes a block of data and releases the latch, B can get latch, latch, and then write the data in block 58th.

This is just an example of writing a block of data to illustrate why you should use latch. In fact, latch is not just for writing blocks of data, for example, for shared pool, the unit of memory is not a block of data. Latch is also not only used for write operations, as long as the memory address of the read and write, need to obtain latch to achieve serialization, only one server process at a time to read or write memory address.

Oracle has introduced a variety of latch in instance management, whether buffer cache, shared pool, or log buffer.

When implementing latch, it is actually done by the operating system's semaphore (semaphore: also called semaphore). To make it easier to understand, they can be imagined as being realized through the change of a variable value. A variable value of 0 indicates that latch is not currently being fetched by another process, or if it is a non-0 value, it is already acquired by another process. Oracle defines it as a lightweight lock when designing latch, so it operates very quickly and is computed in microseconds (microsecond, which is one out of 10,000 seconds).