Deadlock Error - background jobs

Question

Hi all,There is a background job running at midnight everyday for create and delivery shipments, most jobs running successfully but a few jobs every time go to a warning state. the error is mentioned below.Shipment 43267   ORA-00060: deadlock detected while waiting for resource Batch queue is 44 which runs this shipment job, there is only one background job running in that queue.I also increased processes up to 3.is anyone know what other actions can I do to prevent this error happen?Thank Yousara

Krister Liljedahl · Accepted Answer

It is difficult to say how to address this based on this information, but the typical cause of a deadlock in a nightly batch job would be that you have multiple posted background jobs and the batch queue is set up with a value for Processes that is larger than 1. This allows multiple background jobs to execute in parallel, which can cause deadlocks. The resolution for this is then to reduce the Processes to 1 for the batch queue.

There could so also be a background job in a different batch queue running at the same time that is causing the deadlock. If possible put both methods/jobs in the same batch queue and set the Processes to 1 so they gets executed sequentially instead of in parallel.

By looking at the Oracle Trace files that are generated when a deadlock is detected and resolved, you can get an idea which other session was involved in the deadlock, which will give clues in how to organise your batch queues to minimise the risk of deadlocks. It is however quite technical to investigate this.

sara19 · Answer

Hi @Krister Liljedahl,As you mentioned, now I increased the process to 1. also we created code to catch up deadlock warnings and convert it to an error state, then those errors execute by another job. now it seems to be okay.

Deadlock Error - background jobs

2 replies

Reply

Reply

Did you find what you're looking for? If not:

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded