Lesson 6 | Preventing database deadlocks |
Objective | Prevent 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.