Oracle Manufacturing deadlock and query deadlock

Manufacturing deadlock

Create Environment

Create table t1_deadlock (a int );
Create table t2_deadlock (a int );

Insert into t1_deadlock values (1 );
Insert into t2_deadlock values (2 );

-- Step 1
Update t1_deadlock set a = 1000 where a = 1;

 

-- Step 2
Update t2_deadlock set a = 2000 where a = 2;

 

-- Step 3
Update t2_deadlock set a = 2000 where a = 2;

Here there is a "lock wait" ("blocking") phenomenon, the reason is very simple, because this operation has been performed on this data in session2, the row-Level Lock has been applied to this row in session2.
Note: here is "lock wait", not "deadlock". Pay attention to the difference between the two concepts!

 

-- Step 4
Update t1_deadlock set a = 1000 where a = 1;
There is a long wait, but a "deadlock" occurs here "!!

 

Query deadlocks

Forwarding: http://blog.csdn.net/yongali/article/details/1680768

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 ',
'Redo thread', 'redo thread ',
'Non', 'user name ',
'Tx ', 'Transaction ',
'Tm', 'dml ',
'Ul ', 'pl/SQL USER lock ',
'Dx ', 'stributed 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, 'clusive ',
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, 'clusive ',
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 with 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 ))

---------------------------------------------------------
Check Methods for database deadlocks

I. Database deadlock
When the program is executed, click OK or save. The program does not respond or reports an error.
Ii. deadlock Principle
If you update or delete a column in a table in the database, this statement is not mentioned after the execution.
The statement that updates this column of data is in the waiting state during execution,
In this case, this statement has been executed, but it has not been successfully executed and no error has been reported.
Iii. deadlock locating
By checking the database table, you can check which statement is deadlocked and which machine is causing the deadlock.
1) Use the dba user to execute the following statements
Select username, lockwait, status, machine, program from v $ session where sid in
(Select session_id from v $ locked_object)
If there are output results, it indicates a deadlock exists and you can see which machine is deadlocked. Field description:
Username: the database user used by the deadlock statement;
Lockwait: the status of the deadlock. If there is content, it indicates the deadlock.
Status: Status. active indicates a deadlock.
Machine: The Machine where the deadlock statement is located.
Program: the application that generates the deadlock statement.
2) You can run the following statements with dba to view the statements that have been deadlocked.
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 ))

Iv. Solution to deadlock
Generally, you only need to submit statements that generate deadlocks, but in the actual execution process. Users can
I don't know which statement is used to generate a deadlock. You can close the program and restart it.
This problem is often encountered during the use of Oracle, so a few solutions are also summarized.

1) Find the deadlock process:

Sqlplus "/as sysdba" (sys/change_on_install)
SELECT s. username, l. OBJECT_ID, l. SESSION_ID, s. SERIAL #,
L. ORACLE_USERNAME, l. OS _USER_NAME, l. PROCESS
From v $ LOCKED_OBJECT l, V $ session s where l. SESSION_ID = S. SID;

2) kill the deadlock process:

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

3) if the problem persists:

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

Replace the sid with the sid of the deadlock:

Exit
Ps-ef | grep spid

Spid is the process Number of the process and kill the Oracle process.