For Update & Lock Table

Using FOR UPDATE Clause

When you declare a cursor that will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you must use the FOR UPDATE clause to acquire exclusive row locks. An example follows:

DECLARE

   CURSOR c1 IS SELECT employee_id, salary FROM employees

      WHERE job_id = 'SA_REP' AND commission_pct > .10

      FOR UPDATE NOWAIT;

The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure the row is not changed by another user before the update.

The optional keyword NOWAIT tells Oracle not to wait if requested rows have been locked by another user. Control is immediately returned to your program so that it can do other work before trying again to acquire the lock. If you omit the keyword NOWAIT, Oracle waits until the rows are available.

All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. Since the rows are no longer locked, you cannot fetch from a FOR UPDATE cursor after a commit.

When querying multiple tables, you can use the FOR UPDATE clause to confine row locking to particular tables. Rows in a table are locked only if the FOR UPDATE OF clause refers to a column in that table. For example, the following query locks rows in the employees table but not in the departments table:

DECLARE

  CURSOR c1 IS SELECT last_name, department_name FROM employees, departments

    WHERE employees.department_id = departments.department_id 

          AND job_id = 'SA_MAN'

      FOR UPDATE OF salary;

Using LOCK TABLE

You use the LOCK TABLE statement to lock entire database tables in a specified lock mode so that you can share or deny access to them. Row share locks allow concurrent access to a table; they prevent other users from locking the entire table for exclusive use. Table locks are released when your transaction issues a commit or rollback.

LOCK TABLE employees IN ROW SHARE MODE NOWAIT;

The lock mode determines what other locks can be placed on the table. For example, many users can acquire row share locks on a table at the same time, but only one user at a time can acquire an exclusive lock. While one user has an exclusive lock on a table, no other users can insert, delete, or update rows in that table. For more information about lock modes, see Oracle Database Application Developer's Guide - Fundamentals.

A table lock never keeps other users from querying a table, and a query never acquires a table lock. Only if two different transactions try to modify the same row will one transaction wait for the other to complete.

Fetching Across Commits:

DECLARE

-- if "FOR UPDATE OF salary" is included on following line, an error is raised

   CURSOR c1 IS SELECT * FROM employees;

   emp_rec  employees%ROWTYPE;

BEGIN

   OPEN c1;

   LOOP

     FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE

     EXIT WHEN c1%NOTFOUND;

     IF emp_rec.employee_id = 105 THEN

       UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 105;

     END IF;

     COMMIT;  -- releases locks

   END LOOP;

END;

/

Fetching Across COMMITs Using ROWID:

DECLARE

   CURSOR c1 IS SELECT last_name, job_id, rowid FROM employees;

   my_lastname   employees.last_name%TYPE;

   my_jobid      employees.job_id%TYPE;

   my_rowid      UROWID;

BEGIN

   OPEN c1;

   LOOP

      FETCH c1 INTO my_lastname, my_jobid, my_rowid;

      EXIT WHEN c1%NOTFOUND;

      UPDATE employees SET salary = salary * 1.02 WHERE rowid = my_rowid;

      -- this mimics WHERE CURRENT OF c1

      COMMIT;

   END LOOP;

   CLOSE c1;

END;

/