What is a deadlock and how does oracle handle it?
In multi-user environment, many users may update the same information at the same time. Oralce handly handles this by “Locking Mechanism” which allows only one user to update a particular data block; another person cannot modify the same data at the same time. When two or more users are waiting for access to data that has been locked by each other, it is known as deadlock. When a deadlock occurs, the transactions fail to go ahead – they are stuck. In such cases, Oracle breaks the deadlock by forcing one or more transactions to rollback. Statement rolled back is usually the one which detects the deadlock. Deadlocks are mostly caused by explicit locking because oracle does not do lock escalation nor use read locks. Multitable deadlocks can be avoided by locking the tables in same order in all applications, thus preventing a deadlock condition.
