Why and how the deadlock situation arises?
- A deadlock situation arises when two or more users wait for the same resource locked by one anther or two or more processes wait to update rows which are locked by other processes.
- If Oracle detects a deadlock, rolls back the session chosen by the deadlock victim.
- The deadlocks are usually resolved by RDBMS.
- Some of the common types of deadlock are ORA-00060 (enqueue deadlock) and ORA-04020 (library cache deadlock).
- It can also be resolved manually by the oracle interface.
- IBM DB2 runs a background process called the deadlock detector.
- Another way can be by the LOCK_TIMEOUT for the session.
- It can even be done by setting the DEADLOCK_PRIORITY within the session:
SET DEADLOCK_PRIORITY LOW
