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

What this article shares with you is about the operation of Oracl deadlock detection query and processing. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article. Without saying much, follow the editor to have a look.

Deadlock query statement:

Deadlock query statements SELECT bs.username "Blocking User", bs.username "DB User", ws.username "Waiting User", bs.SID "SID", ws.SID "WSID", bs.serial# "Serial#", bs.sql_address "address", bs.sql_hash_value "Sql hash", bs.program "Blocking App", ws.program "Waiting App", bs.machine "Blocking Machine" Ws.machine "Waiting Machine", bs.osuser "Blocking OS User", ws.osuser "Waiting OS User", bs.serial# "Serial#", ws.serial# "WSerial#", DECODE (wk.TYPE, 'MR',' Media Recovery', 'RT',' Redo Thread', 'UN',' USER Name' 'TX', 'Transaction',' TM', 'DML',' UL', 'PL/SQL USER LOCK',' DX', 'Distributed Xaction',' CF', 'Control FILE',' IS', 'Instance State',' FS' 'FILE SET', 'IR',' Instance Recovery', 'ST',' Disk SPACE Transaction', 'TS',' Temp Segment', 'IV',' Library Cache Invalidation', 'LS',' LOG START OR Switch', 'RW',' ROW Wait' 'SQ',' Sequence Number', 'TE',' Extend TABLE', 'TT',' Temp TABLE', wk.TYPE) lock_type, DECODE (hk.lmode, 0, 'None', 1,' NULL' 2, 'ROW-S (SS)', 3, 'ROW-X (SX)', 4, 'SHARE', 5,' S/ROW-X (SSX), 6, 'EXCLUSIVE', TO_CHAR (hk.lmode)) mode_held DECODE (wk.request, 0, 'None', 1,' NULL', 2, 'ROW-S (SS), 3,' ROW-X (SX)', 4, 'SHARE', 5,' S/ROW-X (SSX)' 6, 'EXCLUSIVE', TO_CHAR (wk.request)) mode_requested, TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2, DECODE (hk.BLOCK, 0,' NOT Blocking' / * * / * Not blocking any other processes * / 1, 'Blocking', / * * / / * This lock blocks other processes * / 2,' Global', / * * / / * This lock is global, so we can't tell * / TO_CHAR (hk.BLOCK)) blocking_others FROM v$lock hk, v$session bs, v$lock wk V$session ws WHERE hk.BLOCK = 1 AND hk.lmode! = 0 AND hk.lmode! = 1 AND wk.request! = 0 AND wk.TYPE (+) = hk.TYPE AND wk.id1 (+) = hk.id1 AND wk.id2 (+) = hk.id2 AND hk.SID = bs.SID (+) AND wk.SID = ws.SID (+) AND (bs.username IS NOT NULL) AND (bs .username 'SYSTEM') AND (bs.username' SYS') ORDER BY 1

Query select statements that have deadlocks

Select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object)) I. methods for checking database deadlocks 1. The phenomenon of database deadlocks

During the execution of the program, click the OK or Save button, and the program does not respond and there is no error report.

2. The principle of deadlock

When updating or deleting a column of a table in the database, the statement is not submitted after execution, and another statement that updates this column of data will be in a waiting state when it is executed. the phenomenon at this time is that the statement has been executed, but it has not been executed successfully and no error has been reported.

3. The location method of deadlock

By checking the database table, you can find out which statement is deadlocked and which machine is causing the deadlock.

1) execute the following statement with the dba user

Select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object)

If there is an output, it indicates which machine has a deadlock and can see the deadlock. Field description:

Username: the database user used by the deadlock statement

Lockwait: the status of a deadlock. If there is any content, it means that it is deadlocked.

Status: status. Active indicates deadlock

Machine: the machine on which the deadlock statement is located.

Program: which application is the main source of the deadlock statement.

2) execute the following statement with the dba user to view the deadlocked statement

Select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object)) 4. Deadlock solution

In general, you just have to submit the statement that produces the deadlock, but in the actual execution process. The user may not know which statement caused the deadlock. You can just close the program and restart it. This problem is often encountered in the use of Oracle, so it also summarizes some solutions.

1) the process of finding deadlocks:

Sqlplus "/ as sysdba" (sys/change_on_install) SELECT s.usernamecamera l.OBJECTCTID WHERE l.SESSION_ID=S.SID l.SESSION ID authoring L.ORACLEX USERNAMEWriting l.OSSERCESS FROM V$LOCKED_OBJECT l.Variations session S WHERE l.SESSION_ID=S.SID

2) kill the process of dropping the deadlock:

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

3) if it cannot be solved:

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

Where sid is replaced with deadlocked sid:

Exit ps-ef | grep spid

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

The above is the operation method of Oracl deadlock detection query and processing, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.