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 View Code window.

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.