Oracle Locks   «Prev  Next»
Lesson 6Preventing database deadlocks
ObjectivePrevent a database deadlock

Preventing database deadlocks

Common Causes of Deadlocks

The majority of Oracle programmers do not realize that database deadlocks occur most commonly within a table index.
It is important to note that a SELECT of a single row from the database may cause more than one lock entry to be placed in the storage pool. The individual row receives a lock, but each index node that contains the value for that row will also have locks assigned.

Locking during updates and deletes

When an update or delete is issued against a row that participates in the index, the database will attempt to secure an exclusive lock on the row. This requires the task to check if any shared locks are held against the row, as well as to check on any index nodes that will be affected. Many indexing algorithms allow for the index tree to dynamically change shape, spawning new levels as items are added and condensing levels as items are deleted.

Minimizing Locks

Because most commercial databases only issue automatic locks against a row when they need to lock a row, programmatic solutions can be used to minimize the amount of locking that is used for very large update tasks.
For example, in Oracle SQL, a programmer can use the SELECT...FOR UPDATE clause to explicitly lock a row or a set of rows prior to issuing the UPDATE operation. This will cause the database to issue exclusive locks (sometimes called pre-emptive locks) at the time of retrieval, and hold these exclusive locks until the task has committed or ended.
In the following SQL, an exclusive lock is placed upon the target row, and no other task will be able to retrieve that row until the update operation has completed:

SELECT *
FROM EMPLOYEE
WHERE emp_name = 'Gould'
FOR UPDATE OF SALARY;

For large updates, statements can be issued to lock an entire table for the duration of the operation. This is useful when all rows in the table are going to be affected, as in the following salary adjustment routine:
LOCK TABLE emp_table IN EXCLUSIVE MODE NOWAIT;
UPDATE emp_table
SET salary = salary * 1.1; 

In the next lesson, you'll learn to display internal locks using the V$VIEWS.