Lesson 6	Preventing database deadlocks
Objective	Prevent a database deadlock

Preventing Database Deadlocks in Oracle: Focusing on Table Indexes

Deadlocks within an Oracle Database, particularly involving table indexes, represent a severe impediment to operational efficiency and can have drastic implications for transactional integrity. As an Oracle Database Administrator (DBA), it is imperative to understand and employ strategies to prevent these deadlocks from occurring. This document provides an authoritative guide to best practices and methodologies aimed at mitigating the risk of database deadlocks.

Transaction Design and Order of Operations

Sequential Access: Ensure that all transactions access indexes and tables in a pre-defined, sequential order. This reduces the chances of two transactions locking resources in conflicting orders, thereby reducing deadlock risk.
Short Transactions: Keep transactions as brief as possible to minimize lock duration. This is often achieved by committing transactions immediately after making necessary changes.

Use of Appropriate Indexes

Covering Indexes: Utilize covering indexes for queries that can be satisfied by the index alone without requiring a table scan. This reduces the number of locks needed and subsequently lowers the deadlock potential.
Bitmap Indexes: Use bitmap indexes judiciously, particularly in high-concurrency environments, as they tend to lock more keys than b-tree indexes and can thus increase the risk of deadlocks.

Locking Granularity

Row-Level Locking: Opt for row-level locking as opposed to table-level or page-level locking. This minimizes the amount of data being locked, thus reducing the chances of deadlock.
Use of SELECT FOR UPDATE: Be cautious when using `SELECT FOR UPDATE` as it can result in row-level locks that may contribute to deadlocks. Use it only when absolutely necessary and ensure to commit or rollback promptly.

Monitoring and Diagnostics

Enable Deadlock Tracing: Configure Oracle parameters to generate detailed trace files and logs, allowing you to understand the deadlock scenarios better and adjust strategies accordingly.
Regularly Review V$ Views: Frequently consult Dynamic Performance Views like `V$LOCK` and `V$SESSION` to monitor lock activities and detect patterns that may lead to deadlocks.

Application Code Review

Optimize SQL Statements: Poorly optimized SQL can lock more rows than necessary. Ensure SQL queries are optimized to lock only the needed rows and nothing more.
Avoid Nested Transactions: Nested transactions can hold locks for an extended period, increasing the risk of deadlocks. Refactor such application logic to minimize the use of nested transactions.

Third-Party Tools

Consider employing advanced monitoring and diagnostic tools such as Oracle Enterprise Manager (OEM) for real-time deadlock detection and alerting. These tools can often be configured to suggest or even implement preventive measures.
Preventing deadlocks in Oracle, especially those involving table indexes, requires a multi-pronged approach that encompasses transaction design, index usage, locking strategies, and continuous monitoring. As an Oracle DBA, you must be proficient in these strategies to ensure the integrity, performance, and seamless operation of your database environment. Implementing these best practices will significantly mitigate the risk of encountering database deadlocks, thereby maintaining the robustness and reliability of your Oracle RDBMS.

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.