Preventing Database Deadlocks in Oracle: Focusing on Table Indexes

Preventing database deadlocks, particularly those that occur in table indexes in Oracle 11g, involves implementing various strategies to ensure that transactions are managed in a way that minimizes the risk of deadlock situations. Here are some effective methods:

1. Order of Resource Access: Always access tables and indexes in a consistent order in all transactions. This reduces the risk of deadlocks by ensuring that no two transactions lock resources in conflicting orders.
2. Minimize Transaction Duration: Keep transactions short and fast to reduce the time locks are held. The longer a transaction holds a lock, the greater the chance of encountering a deadlock.
3. Use Indexes Efficiently: Ensure that your queries are well-optimized and take advantage of appropriate indexes. Inefficient queries may cause full table scans and lock more rows than necessary, increasing the risk of deadlocks.
4. Avoid Manual Locking Where Possible: Let Oracle manage locks automatically rather than using manual locks, such as SELECT FOR UPDATE, unless absolutely necessary. When manual locking is used, ensure that it is well-thought-out and efficient.
5. Partitioning: Consider partitioning large tables to distribute and isolate rows, thereby reducing lock contention. Partitioning helps ensure that concurrent transactions do not lock the same resources.
6. Proper Use of COMMIT: Use COMMIT statements appropriately to release locks. Avoid long-running transactions that delay commits, as this can increase lock contention and deadlock risk.
7. Analyze and Optimize Index Usage: Make sure indexes are properly maintained and statistics are up-to-date. This allows the query optimizer to make better decisions, reducing lock contention on index structures.
8. Use Lock Monitoring Tools: Oracle provides tools like V$LOCK, V$SESSION, and V$SQL views to monitor locks and help identify potential deadlock-prone transactions.

By employing these strategies, you can significantly reduce the risk of deadlocks in your Oracle 11g database, especially those related to table index contention.

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.
• Using PL/SQL to release Locks frequently:
  Sometimes the application will want to update all of the rows in a table, but it is not practical to lock the entire table. An alternative to the exclusive update is to use the Oracle's SQL C to lock a small segment of the table, perform the update, and then release the locks with a COMMIT statement, as shown in the PL/SQL code below.
  

  DECLARE     CURSOR total_cursor IS
   SELECT emp_name FROM emp_table;
  DECLARE CURSOR update_cursor IS
    SELECT ROWID
    FROM emp_table
    WHERE emp_name = :my_emp_name
    FOR UPDATE OF SALARY;
     BEGIN
      count = 0;
      OPEN total_cursor;
      
      begin_loop;    
       OPEN update_cursor;        
       FETCH total_cursor INTO :my_emp_name;
       FETCH update_cursor INTO :my_rowid;
          IF (update_cursor%found) THEN
          {
              UPDATE emp_table 
                  SET salary = salary * 1.1
              WHERE
                  ROWID = :my_rowid;
              COUNT++;
              IF (COUNT = 20) THEN
              {
                  COMMIT;
                  COUNT = 0;
              }
          }
  }
  CLOSE update_cursor;
  CLOSE total_cursor;
  END;    
  

• locks are set as the rows are fetched:
  As you examine the code show above, notice that the locks are set as the rows are fetched, 20 at a time, and then released with a COMMIT. This technique consumes less memory in the lock pool and also allows other SQL statements to access other rows in the table while the update is in progress. Of course, if this code should fail, it would need to be restarted from the point of the last COMMIT statement. This would require additional logic to be inserted into the update program to record the row ID of the last COMMITted row, and to restart the program from that row.

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.