Usage of FOR UPDATE in ORACLE

In Oracle, the FOR UPDATE clause is used in a SELECT statement to lock the selected rows so that no other transaction can modify them until the transaction is committed or rolled back. This is useful in scenarios where you need to ensure data consistency and prevent other transactions from updating the same rows you are working with.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
FOR UPDATE [OF column1, column2, ...] [NOWAIT | WAIT n];

Key Components

• FOR UPDATE: Locks the selected rows.
• OF column1, column2, ...: Optional. Specifies which columns to lock.
• NOWAIT: Optional. If specified, the query returns immediately if the requested rows are locked by another transaction.
• WAIT n: Optional. Specifies the number of seconds to wait for the rows to become available.

Example Usage

Basic Example

Suppose we have a table employees and we want to lock a row while updating it.

1. Selecting Rows with FOR UPDATE:

   SELECT employee_id, first_name, last_name
   FROM employees
   WHERE employee_id = 100
   FOR UPDATE;
   

   This locks the row where employee_id = 100 until the transaction is completed.

2. Updating the Locked Rows:

   UPDATE employees
   SET salary = salary + 1000
   WHERE employee_id = 100;
   

3. Committing the Transaction:

   COMMIT;

Using NOWAIT and WAIT

NOWAIT Example

If you want the query to return immediately if the rows are locked already:

SELECT employee_id, first_name, last_name
FROM employees
WHERE employee_id = 100
FOR UPDATE NOWAIT;

If the row with employee_id = 100 is already locked by another transaction, Oracle will return an error immediately.

WAIT Example

If you want the query to wait for a specific number of seconds for the rows to become available:

SELECT employee_id, first_name, last_name
FROM employees
WHERE employee_id = 100
FOR UPDATE WAIT 10;

This query will wait for up to 10 seconds for the row with employee_id = 100 to become available. If it remains locked beyond that time, an error is returned.

Using FOR UPDATE in PL/SQL

In a PL/SQL block, you can use the FOR UPDATE clause in a cursor to lock rows before processing them:

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, salary
    FROM employees
    WHERE department_id = 10
    FOR UPDATE;
BEGIN
  FOR emp_record IN emp_cursor LOOP
    UPDATE employees
    SET salary = emp_record.salary + 1000
    WHERE CURRENT OF emp_cursor;
  END LOOP;
  COMMIT;
END;

In this example, the cursor emp_cursor selects employees from department 10 and locks the selected rows. The WHERE CURRENT OF clause updates the current row of the cursor.

Conclusion

The FOR UPDATE clause is a powerful feature in Oracle that helps maintain data consistency and integrity by locking rows during a transaction. This prevents other transactions from modifying the locked rows until the current transaction is completed. The optional NOWAIT and WAIT clauses provide additional control over how the locking mechanism behaves, allowing for immediate feedback or a specified wait time if the rows are already locked.