ORA 00060 DEADLOCK DETECTED WHILE WAITING FOR RESOURCE UPDATE
SQL> select * from maclean2 for update wait 8 SQL> select * from maclean1 for update wait 8 SQL> alter system set "_enqueue_deadlock_time_sec"=9 scope=spfile WAIT #12: nam='enq: TX - row lock contention' ela= 2930070 name|mode=1415053318 usn alter system set "_enqueue_deadlock_scan_secs"=4 scope=spfile SQL> alter session set events '10704 trace name context forever,level 10:10046 trace name context forever,level 8' SQL> alter system set "_enqueue_deadlock_scan_secs"=18 scope=spfile _enqueue_deadlock_scan_secs 0 deadlock scan interval SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describĦ AND x.ksppinm='_enqueue_deadlock_scan_secs' Is this really the case? Take a look at the following demo: ORA-00060: deadlock detected while waiting for resourceĮlapsed: 00:00:03.02 It can be seen that Process A did wait for 3s before detecting the deadlock, and this is a repeatable experiment and very convincing. TNS for Linux: Version 10.2.0.5.0-Production Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bi The basis for this is that a simple and repeatable experiment can prove that the process waited 3s before the actual occurrence of the ora-00060 dead lock error:
Some students pointed out that the deadlock detection for enqueue lock should be detected every 3 seconds. Job stage is READ-> TRANSFORM-> SPLITTER-> INSERT/UPDATEĪUTO PARTITION has been used in all the stages.Today, I discussed a question about queue lock deadlock detection in the ORACLE ALLSTAR group.
If we kill and re run, few records gets inserted and it gets dead locked. I tried running the same job with a single node configration, but the job still hangs with a dead lock.
Update is: UPDATE IOA_SOURCE_MOLI SET SOURCE_ORDER_NUMBER = :SOURCE_ORDER_NUMBER, SOURCE_REVISION_NUMBER = :SOURCE_REVISION_NUMBER, SERVICE_NUMBER = :SERVICE_NUMBER, LAST_UPDATE_DATE = :LAST_UPDATE_DATE, LAST_STATUS_CHANGE_DATE = :LAST_STATUS_CHANGE_DATE, COMPLETION_DATE = :COMPLETION_DATE, TELSTRA_COMMIT_DATE = :TELSTRA_COMMIT_DATE, CUST_REQUEST_DATE = :CUST_REQUEST_DATE, PRIORITY_ASSIST_IND = :PRIORITY_ASSIST_IND, RECORD_ONLY_IND = :RECORD_ONLY_IND, PROVISIONABLE_IND = :PROVISIONABLE_IND, ACTION_DESCRIPTION = :ACTION_DESCRIPTION, MISSING_TCD_MOLI_COUNT = :MISSING_TCD_MOLI_COUNT, PRODUCT_SKEY = :PRODUCT_SKEY, PRODUCT_FAMILY_SKEY = :PRODUCT_FAMILY_SKEY, MOLI_STATUS = :MOLI_STATUS, MOLI_STATUS_SKEY = :MOLI_STATUS_SKEY, O2A_STATUS_SKEY = :O2A_STATUS_SKEY, LAST_O2A_STATUS_CHANGE_DATE = :LAST_O2A_STATUS_CHANGE_DATE, O2B_STATUS_SKEY = :O2B_STATUS_SKEY, TRANSFER_TYPE = :TRANSFER_TYPE, PROMOTION_NAME = :PROMOTION_NAME, PROC_BUNDLE_NUM = :PROC_BUNDLE_NUM, INS_JOB_NUM = :INS_JOB_NUM, LAST_CHG_JOB_NUM = :LAST_CHG_JOB_NUM, LAST_CHG_ASAT_DTTM = :LAST_CHG_ASAT_DTTM, DQM_OBJ_ID = :DQM_OBJ_ID, DQ_CLEAN_FLAG = :DQ_CLEAN_FLAG, APPOINTMENT_ID = :APPOINTMENT_ID, PROD_CONSTRUCT_TYPE = :PROD_CONSTRUCT_TYPE, SUB_ACT_SKEY = :SUB_ACT_SKEY, PROMOTION_TYPE = :PROMOTION_TYPE, FULFLMNT_STATUS_CD = :FULFLMNT_STATUS_CD WHERE (SOURCE_MOLI_NUMBER = :SOURCE_MOLI_NUMBER)Įsql complaint: ORA-00060: deadlock detected while waiting for resource. We have a parallel job which reads from a oracle table does some transformation and again does a INSERT/UPDATE in the same table.
0 kommentar(er)
