How to avoid deadlocks on oracle

How to avoid deadlocks on oracle

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.