The database version is oracle 11.2.0.4.0, dg master-slave architecture is a single instance, physical standby, execute the statement and view the log is in the main library
With the query of deadlock statement:
The SELECT bs. The username "Blocking the User," bs. The username "DB User,"
Ws. The username "Waiting User", bs. SID, "SID" ws. SID "WSID,"
Bs. Serial# serial#, bs. Sql_address "address",
Bs. Sql_hash_value Sql hash, bs. The program "Blocking App,"
Ws. The program "Waiting App," bs. The machine "Blocking machine",
Ws. The machine Waiting "the machine", bs osuser "Blocking OS User,"
Ws. Osuser Waiting OS User, bs. Serial# serial#,
Ws. Serial# WSerial#,
DECODE (wk. A TYPE,
'MR', 'Media Recovery',
'the RT', 'Redo Thread',
'UN', 'USER Name',
'TX', 'Transaction',
'TM', 'DML,
PL/SQL USER 'UL', 'LOCK',
'DX' and 'Distributed Xaction',
'CF', 'Control' FILE ',
'IS', 'the Instance State',
'the FS', 'the FILE SET',
'the IR', 'the Instance Recovery',
'ST', 'Disk SPACE Transaction',
'TS', 'Temp Segment',
'the IV', 'the Library Cache Invalidation',
'LS', 'LOG the START OR the Switch',
'RW', 'ROW Wait',
'SQ', 'the Sequence Number',
'TE', 'the Extend TABLE',
'TT', 'Temp TABLE',
Wk. A TYPE
) lock_type,
DECODE (hk) lmode,
0, 'None',
1, the 'NULL',
2, 'ROW - S (SS),
3, 'ROW - X (SX),
4, the 'SHARE',
5, 'S/ROW - X (SSX),
6, 'EXCLUSIVE'
TO_CHAR (hk) lmode)
) mode_held,
DECODE (wk. A request,
0, 'None',
1, the 'NULL',
2, 'ROW - S (SS),
3, 'ROW - X (SX),
4, the 'SHARE',
5, 'S/ROW - X (SSX),
6, 'EXCLUSIVE'
TO_CHAR (wk. A 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 the processes */
1, 'Blocking',///* * * This lock blocks other the processes */
2, the 'Global',///* * * This lock is Global, so we can 't tell */
TO_CHAR (hk) BLOCK)
) blocking_others
The FROM v $lock hk, v $session bs, v $lock wk, a v $session ws
WHERE hk. BLOCK=1
AND hk lmode!=0
AND hk lmode!=1
AND wk. A request!=0
AND wk. A TYPE (+)=hk. TYPE the
AND wk. A id1 (+)=hk. Id1
AND wk. A id2 (+)=hk. Id2
AND hk. SID=bs. SID (+)
AND wk. A SID=ws. SID (+)
AND (bs. The username IS NOT NULL)
AND (bs) username & lt;> 'the SYSTEM')
AND (bs) username & lt;> 'SYS)
The ORDER BY 1;
Results:
PU_APPS PU_APPS 1654 1712 13879 0000000627 3274778297 e2bea0 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 2167 1199 54359 0000000627 3274778297 e2bea0 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 1771 116 45785 0000000627 3274778297 e2bea0 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 2167 858 54359 0000000627 3274778297 e2bea0 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 1712 1314 2975 0000000627 3274778297 e2bea0 JDBC Thin Client JDBC Thin Client
PU_APPS PU_APPS 2167 1884 54359 0000000627 3274778297 e2bea0 JDBC Thin Client JDBC Thin Client
Indicate a deadlock, but in the database was not found in the alert log of ORA - 00060 log, please advice to analyse the great god came in

CodePudding user response:

Deadlock 3 seconds will be an error, disconnect one of the session, you won't find, basically
Check your script isn't a deadlock, make clear the difference between lock waits and deadlock, because more than 99% of the country's basic development call lock waits a deadlock

CodePudding user response:

Thank you upstairs, that is how to view a deadlock?

CodePudding user response:

reference 1st floor minsic78 response:

deadlock 3 seconds will be an error, disconnect one of the session, you won't find basically,
Check your script isn't a deadlock, make clear the difference between lock waits and deadlock, because more than 99% of the country's basic development call lock waits a deadlock

More than 99% of the country's basic development call lock waits a deadlock

CodePudding user response:

refer to the second floor luoxiulong response:

thank upstairs, that is how to view a deadlock?

Deadlock words mentioned above, there will be 3 seconds session error, and the instance in the alert log will be written to a deadlock ORA - 60 errors,

CodePudding user response:

SQL is found out is block instead of a deadlock, deadlock can only see from the log, SQL is not detected, it can be understand?

CodePudding user response:

reference 5 floor luoxiulong reply:

is the SQL found out are block instead of a deadlock, a deadlock can only see from the log, SQL is not detected, it can be understand?

Lock wait, that is, you're blocking is one of the session request lock resources waiting another hold lock session, the deadlock is waiting for each other, between two sessions for deadlock detection mechanism, in this case, 3 seconds, is bound to have one session broke ORA - 60, and written to the alert log, time is too fast, you want to check also too late,

CodePudding user response:

Thank you for your advice, I see