Oracle Locks   «Prev  Next»
Lesson 5Database deadlocks
ObjectiveIdentify an Oracle deadlock

Database Deadlocks (Oracle)

The purpose of the Oracle locking scenario insures that all database integrity is maintained and that updates do not inadvertently overlay prior updates to the database.
However, a penalty has to be paid for maintaining shared locks.

Lock space Considerations

In Oracle, each lock requires 4 bytes of RAM storage within the Oracle instance storage pool, and large SQL SELECT statements can create SOS (short on storage) conditions that can cripple the entire database. For example, a SELECT statement that retrieves 1,000 rows into the buffer will require 4,000 bytes of lock space.

Oracle deadlock

Multiple competing tasks can also cause a condition called the "deadly embrace," or a database deadlock. A deadlock condition occurs when two tasks are waiting on resources that the other task has locked.
The Slide Show below shows how a deadlock develops.

Oracle DBMS Deadlock

Oracle releases Database Deadlock

In reality, of course, Oracle will release the database deadlock by aborting the task that caused the deadlock, in this case task A. Oracle's Lock Manager will detect the deadly embrace after a task has been waiting and will abort the task with the ORA-60 "deadlock detected while waiting for resource" message. Remember that it is impossible for a single task to cause a deadlock and two tasks must be involved. Now that you know what a deadlock is, the next lesson will show you how to prevent it from happening.