How to avoid deadlocks on oracle

Asked By 130 points N/A Posted on -
qa-featured

Is it possible to prevent a high priority process from aborting whenever a deadlock occurs? Instead, I need lower priority tasks to be canceled. It should be like configuring a high priority user or session to be still functional during a deadlock. Answers are appreciated.

SHARE
Answered By 0 points N/A #185053

How to avoid deadlocks on oracle

qa-featured

Hi,

Whenever you are running DML transactions that time dead lock appears. And you get  error shown below.

    ORA-00060: deadlock detected while waiting for resource

If user executes more than one query at a time that time server stops and lock system takes place.

 Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

Deadlocks in Oracle result in this error:

ORA-00060: deadlock detected while waiting for resource

  • If you get ORA-60 errors on UPDATE and DELETE statements, where two processes wait for 'S' mode locks on each other's 'TX' enqueues, you are experiencing ITL shortage deadlocks". This cannot happen with INSERT statements, as Oracle doesn't wait on ITL (Interested Transaction List) slots for inserts, it will simply try to insert the row into the next available block.

To fix this, recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required.

 

"ITL waits" can be monitored per segments by querying the sys.v_$segment_statistics view.

Related Questions