![]() ![]() Add INITRANS – In certain conditions, increasing INITRANS for the target tables and indexes(adding slots to the ITL) can relieve deadlocks.Re-scheduling batch update jobs to low-update times an also help. Tune the application – Single-threading related updates and other application changes can often remove deadlocks.There are several remedies for resolving aborted tasks from deadlocks: See my notes here on resolving the deadlock detected error. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock. If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. Retry if necessary.ĭeadlocks in Oracle result in this error: ORA-00060: deadlock detected while waiting for resourceĬause: Transactions deadlocked one another while waiting for resources.Īction: Look at the trace file to see the transactions and resources involved. This deadlock condition is an age-old issue known as the “perpetual embrace”! The doc note that a retry may work: Job stage is READ-> TRANSFORM-> SPLITTER-> INSERT/UPDATEĪUTO PARTITION has been used in all the stages.Whenever you have competing DML running against the same data, you run the risk of a deadlock. 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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |