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

This article mainly introduces "how to understand the lock and latch of Oracle". In the daily operation, I believe many people have doubts about how to understand the lock and latch of Oracle. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to understand the lock and latch of Oracle"! Next, please follow the editor to study!

When developing multi-user, database-driven applications, one of the biggest difficulties is: on the one hand, we should strive to maximize concurrent access, and at the same time, we should ensure that each user reads and modifies data on the premise of user consistency. So there is the mechanism of lock.

Lock is used to manage concurrent access to shared resources. To give a very vivid example:

User1 takes a row of data in a transaction in its session, puts it in local memory, and displays it to user1

A transaction of user2 in its session also takes this data and displays it to user2

User1 uses its application to modify this row of data, and then after commit, the transaction in session is completed

User2 also modifies this row of data. After commit, the transaction is completed.

This process is known as "update loss" because all changes made by user1 will be lost. So in this case, Oracle has the lock setting, and the lock has two strategies: pessimistic lock (pessimistic) and optimistic lock (optimistic).

Pessimistic lock

Pessimistic lock, as the name implies, Oracle believes that as long as you have a session connection, you are afraid that other sessions will certainly make changes to the data, so as long as there is a session transaction involving the data table, Oracle will add a row lock to it to prevent other users from modifying it until the transaction in the session is committed.

The session will wait here all the time, and after commit in user1, user2 will execute and report the corresponding result! Pessimistic locks make it very safe for user to manipulate that data, ensuring that no one else does anything else to him before we make any more changes.

Optimistic lock

Optimistic locks are the opposite of pessimistic locks, which think that there may be other sessions that manipulate the data I am working on, while optimistic locks assume that all sessions are friendly and do not manipulate the data. So we can modify our data and not find out whether we are right or not until we commit.

For example, when we execute another update statement, the success of the update statement indicates that we are lucky that no other session has operated on it during this period; if the last display is to update row 0, this means that another user has modified the data. So we need to think about what to do next if this happens.

Let's first discuss a way to use optimistic locks: using optimistic locks for version columns.

To sum up in simple words, make a unique tag for the original data, and then compare the tag after updating the data with the tag of the original data. If it is the same, it means that no one has changed, then the update is successful; if different, the update fails. The difficulty of this optimistic lock with the version column is how to update our version column. But this problem is easy to solve in oracle. Simply add columns of type number or date/timestamp to each corresponding table and maintain them through row triggers or stored procedures on the table. Triggers will add extra overhead in addition to the modification operation, so use it with caution! )

Do you use optimistic locks or pessimistic locks? In fact, pessimistic locks work well in dealing with concurrency, but in today's high concurrency applications, pessimistic locks always keep connected to transactions, which is too expensive, that is, we often say that the advantages outweigh the disadvantages, so, at present, in most applications, most of them use optimistic locking concurrency control, and use the optimistic locking method of version column.

There are three main types of locks in Oracle, DML locks, DDL locks, internal locks and latches: let's first introduce DML locks.

DML locks are used to ensure that only one person can modify a row at a time; Oracle adds the lock in a more transparent way.

TX lock

A TX lock (transaction lock) is obtained when the transaction initiates the first modification, and the TX lock is held until the transaction commits. The TX lock acts as a queuing mechanism that allows other sessions to wait for the transaction to complete. Because a lock is an attribute of a transaction, it is locked as long as the transaction finds the data and the data is not locked. If the data is locked, the session requesting the lock will wait, wait for the transaction that currently owns the lock to execute, and then get the data.

TM lock

TM locks are used to ensure that when you modify the contents of a table, the structure of the table remains the same; for example, when I update a row of data in the table, I also get a TM lock on the table; prevent other users from executing drop or alter commands on the table.

After talking about how locks work and their two lock mechanisms, let's talk about latch.

A latch is a lightweight serial device used to coordinate multi-user access to shared data structures, objects, and files. The latch is a kind of lock, and the lock is a serial device, so in order to better expand the application, we must find a suitable way to reduce the number of latches (locks).

Why a latch is a lightweight lock, because the latch is designed to hold for a short period of time, not because its influence is small is called lightweight, and transactions generally request the latch internally in a "willing to wait" attitude. If no channel latch is requested, the session sleeps for a period of time and then attempts the request operation. The mechanism here is that there is no queue for sleep, while the queue lock (enqueue) allows the requestor to wait for resources, so the requestor will be blocked. Turning back, the latch allows the session to sleep, and then asks the session to request the resource again; this involves the special operation of the latch: spin: you are constantly trying to get the latch. Because context switching (context switching: being kicked out of the CPU and then having to be dispatched back to the cpu) consumes a lot of resources, if the process cannot get the latch immediately, we will keep the process in the cpu and keep trying to get the latch; because the latch is designed to be set in a very short period of time, the session with the latch will quickly abandon the latch, allowing other processes to get the latch If the latch is not available all the time, the session will abandon the cpu and let other processes enjoy the resources and sleep on their own.

This dormant action usually occurs when multiple sessions request the same latch at the same time, although each holding time is very short, but the more the number, the longer the cumulative time.

At this point, the study on "how to understand the lock and latch of Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.