Preventing database 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.

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.

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;

LOCK TABLE emp_table IN EXCLUSIVE MODE NOWAIT;
UPDATE emp_table
SET salary = salary * 1.1;