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.