Shulou(Shulou.com)06/01 Report--

This article focuses on "Oracle deadlock monitoring and solutions", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "Oracle deadlock monitoring and solutions"!

1. Find the lock object and the corresponding SID

/ / Lock found

SELECT s.username, l.OBJECT_ID, l.SESSION_ID, s.SERIAL, l.ORACLE_USERNAME, l.OS_USER_NAME, l.PROCESS FROM V$LOCKED_OBJECT L1 Variance session S WHERE l.SESSION_ID=S.SID

2. Solution-kill drops the specified session

Alter system kill session 'sid,serial#'; (where sid=l.session_id)

Kill without session, can also be done in EM, using a graphical interface.

3. Solution-kill drops the specified process

The id information of the corresponding service process is found through sid, and the kill is performed at the operating system level.

Select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr

Where sid is replaced with a deadlocked sid.

Exit

Ps-ef | grep spid

Where spid is the process number of this process, and kill drops the Oracle process.

There are many cases of kill processes at the OS level, such as session called by dbms_job, most of the time you need to kill at the OS level.

Oracle deadlock and Application Design

In general, the operation of the application on the database is basically instantaneous (unless Tunning is required). Monopolization of an object for a long time occurs only when it comes to bulk transaction operations. Once this happens, the exclusive object is requested by another session (bulk operation), and a deadlock may occur.

Long-time batch transactions are generally organized in a special job manager of the system and run according to specific scheduling rules. In order to improve efficiency, job managers usually use multi-threading technology, and multiple threads perform multiple tasks at the same time, in this case, the probability of deadlock is also greatly improved. On this issue, the author has the following ideas:

The main contents are as follows: 1. Threads are divided according to modules, jobs are executed sequentially within modules, and parallel between modules. Generally speaking, there are serious resource requests for sharing between jobs of a module, such as operations on the same batch of data in the same table. This can easily lead to a deadlock. Whether unlocked manually or with the help of Oracle's own mechanism, it will cause at least one job to be rolled back. Therefore, according to the division of the module, the jobs of the same module are executed as sequentially as possible to reduce the sharing of table data. For jobs between different modules, the table scope of operation updates varies greatly, so jobs between different business modules are carried out in parallel.

2. Error 00060 is shown in the job. In the job development, it is necessary to add the handling of the 00060 deadlock error. If you accept the error exception, you should actively roll back the operation from the application level and come into contact with other job blocking situations.

3. With the help of Oracle's own locking mechanism, monitor the status of locked objects and sessions for a long time, and inform DBA to solve the deadlock manually as soon as possible.

To sum up: deadlock is a relatively rare situation in Oracle, and it has its own monitoring and protection mechanism. As application developers, we should also rationalize the application processing structure as much as possible, avoid the display of locked objects and unreasonable parallel operations, and increase the probability of deadlock.

At this point, I believe you have a deeper understanding of "Oracle deadlock monitoring and solutions". 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.