Oracle Database - Deadlock

Card Puncher Data Processing


A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Oracle Database automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks.

Oracle Database is designed to avoid deadlocks, and they are not common. Most often they occur when transactions explicitly override the default locking of the database. Deadlocks can affect the performance of your database, so Oracle provides some scripts and views that enable you to monitor locks.

Oracle Database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock, releasing one set of the conflicting row locks. The database returns a corresponding message to the transaction that undergoes statement-level rollback. The statement rolled back belongs to the transaction that detects the deadlock. Usually, the signalled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting. Illustration of two transactions in a deadlock

Oracle Database never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. Assume that a system is trying to escalate locks on behalf of transaction 1 but cannot because of the locks held by transaction 2. A deadlock is created if transaction 2 also requires lock escalation of the same data before it can proceed.

Because Oracle Database:

  • does not escalate locks
  • and does not use read locks for queries,
  • but does use row-level (rather than page-level) locking,

deadlocks occur infrequently.

Discover More
Toad Lock
Oracle Database - Locks

Lock Mechanism in Oracle Oracle Database provides: data concurrency, consistency. The data a session is viewing or changing must not be changed by other sessions until the user is finished. and...
Oracle Database Lock Foreign Key Unindexed
Oracle Database - Locks and Foreign Keys - Concurrency control of parent keys

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behaviour depends on whether foreign key columns are indexed. If foreign keys are not indexed,...

Share this page:
Follow us:
Task Runner