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

This article mainly explains "how to understand the deadlock in Oracle". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand deadlocks in Oracle.

Deadlock Dead Lock

We all first came into contact with the concept of deadlocks probably in the operating system course, saying that deadlocks may occur when multiple processes (threads) request a shareable resource. Deadlock problem can be divided into several sub-problems, such as deadlock detection, processing and so on.

In fact, the problem of deadlock is not limited to operating systems and even computer science. There are two necessary conditions for the existence of deadlock, one is the concurrency of multitasking, and the other is the exclusive requirement of shared resources. As long as these two premises exist in a system (singular system), we think that a deadlock may occur.

A deadlock describes a state. When two or more task units are in the process of execution, the state of waiting for each other because the requested resources are waiting and because the resources can never be obtained. If there is no external force, the deadlock will last forever. Deadlock is accompanied by multi-task, parallel operation, in the case of a single task, a task unit can use and monopolize all resources, there is no resource waiting, so there is no deadlock. In a multi-tasking system environment, deadlocks may occur only when there is a need for resource sharing and monopoly among multiple tasks.

The simplest example of deadlock: task Amemi B, Resource 1Pol 2. Task A monopolizes resource 1, and task B monopolizes resource 2. At this point, Task An asks for Resource 2, makes a request to Task B and waits. Task B requires resource 1 and also waits. Neither of the AB releases the resources it has, resulting in a deadlock.

Deadlock in Oracle

Oracle is the best concurrent database system in the commercial database market at present, and it also has the threat of deadlock. If there is concurrency and resource monopolization, there is a lock lock or a mechanism similar to the concept of lock. Oracle provides mechanisms for multiple types of locks and multiple locks, including shared locks and exclusive locks. Moreover, when carrying out various types of operations, the object is automatically locked and unlocked, and the lock upgrade operation is carried out to ensure data integrity as much as possible.

So what will Oracle do if there is a deadlock?

The locking mechanism of Oracle is established at the row lock level, and exclusive lock content is added when the row-level information is inserted or updated. So, let's try to simulate the state of a deadlock. If two session updates two records and tries to update the other record in one transaction, a deadlock can be raised.

The lab environment is ready:

SQL > desc t

Name Type Nullable Default Comments

-

ID NUMBER

COMM VARCHAR2 (10) Y

SQL > select * from t where rownum select sid from v$mystat where rownum update t set comm='Tst1' where id=1

1 row updated

/ / Session2

SQL > select sid from v$mystat where rownum update t set comm='Tst2' where id=2

1 row updated

At this point, the status of the lock is:

SQL > select * from v$lock where sid in (150152)

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-

333415A4 333415BC 152 TM 54599 0 3 0

33341668 33341680 150 TM 54599 0 3 0

3338A42C 3338A548 150 TX 393251 795 6 0

333B8954 333B8A70 152 TX 327686 779 6 0

At this point, we see that in the two session, row locks are used and LMODE=6 locks two lines. Let's continue to ask each other.

/ / in session1, try to update the record of id=2

SQL > update t set comm='Tst2' where id=2

Session1 (SID=152) is lived by hange.

/ / Lock status

SQL > select * from v$lock where sid in (150152)

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-

33834398 338343AC 152 TX 393251 795 0 6

333415A4 333415BC 152 TM 54599 0 3 0

33341668 33341680 150 TM 54599 0 3 0

3338A42C 3338A548 150 TX 393251 795 6 0

333B8954 333B8A70 152 TX 327686 779 6 0

Note: in Oracle, hange indicates that session is waiting for the resource to be released, which means stopping the operation and constantly polling the resource.

Session2 requests update:

/ / session2 (SID=150)

SQL > update t set comm='Tst1' where id=1

Note: at this point, there should be a deadlock state, and in the system, there is an instant state in which two session hange each other.

At this point, session2 continues to be lived by hange, and the original session1 exits, with the following status:

/ / session1 (SID=152)

SQL > update t set comm='Tst2' where id=2

Update t set comm='Tst2' where id=2

ORA-00060: deadlock detected while waiting for resources

The result is that among the two session with deadlock, Oracle randomly looks for one session, rejects its request to be resided by hange, and maintains the hange state of the other session.

Note: Oracle processing here: only one party's request is rejected, the request is not rolled back, and the other party's hange status is not released.

Consideration: obviously, it is the internal protection mechanism of Oracle that prevents deadlocks in the system during this process. In Oracle, there is some polling mechanism to check the situation that multiple sessions are held by hange in the system at any time. Once it happens, a request that session is held by hange will be returned and a 00060 error will be thrown.

The Oracle deadlock detector can work in the case of two session interlocks. Then, if the deadlock structure is complex, whether the detection mechanism of Oracle will fail. The author uses 4-5 session to check, although the test takes effect for a long time or a short time, but in the end, the deadlock is released.

It is important to note that Oracle solves the deadlock only by rejecting the request, not by rolling back the transaction. Therefore, after solving the deadlock, the other session that hange lives in is still occupied by hange. So, from an application and PL/SQL perspective, if you accept a 60 error, the job you should do is to roll back the current transaction and resolve the overall resource contention.

Oracle deadlock occurs

So, is the probability of deadlock in Oracle that high? The answer is no. This is determined by the nature of the Oracle lock.

1. Oracle does not lock the query. Oracle itself supports consistent reading of multiple versions. If the current data block is being modified (exclusive) and not committed, Oracle will find the log and Undo space according to SCN, find the appropriate version of SCN and return the result. Therefore, when querying, there is no need to add locks.

2. Oracle data operations use row-level locks (essentially transaction locks) to achieve the smallest granularity of exclusive scope. When Oracle is in DML, it only locks the rows of operations exclusively, not overly large data units (such as page page). In this way, it is guaranteed that too many exclusive resources will not be caused.

At the same time, Oracle itself also provides a deadlock monitoring program function, which can find the deadlock status in time and unlock it automatically. Under these mechanisms, Oracle believes that the probability of deadlock occurrence is very low (at least it does not cause deadlock itself). The only situation that can cause a deadlock is when the developer manually increases the level of locking.

Part of Oracle's extension of SQL is the locking of display objects. From lock table XXX to select XXX for update. All of these operations will lead to an increase in the level of object locks, which can lead to deadlocks.

At this point, I believe you have a deeper understanding of "how to understand the deadlock in Oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.