Savepoint in an Oracle PL/SQL LOOP to stop deadlocks or record locks contention

Question

I have a simple procedure but I'm unsure on how to best implement a strategy to stop deadlocks or record locks. I'm updating a number of tables in an cursor LOOP while calling a procedure that also updates tables.

There have been issues with deadlocks or record locks, so I've been tasked to cure this problem of the program from crashing once it comes up against a deadlock or record lock but to sleep for 5 minutes and carry on processing any new records.

The perfect solution is that it skips pass the deadlock or record lock and carry's on processing the rest of the records that aren't locked, sleeps for 5 minutes then picks up that record when the cursor is called again. The program continues to run through the day until it's killed.

My procedure is below, I have put in what I think is best but should I have the exception inside the Inner loop rather than the outer loop? While also having a savepoint in the inner loop?

PROCEDURE process_dist_data_fix
IS

   lx_record_locked             EXCEPTION;
   lx_deadlock_detected         EXCEPTION;
   PRAGMA EXCEPTION_INIT(lx_record_locked, -54);
   PRAGMA EXCEPTION_INIT(lx_deadlock_detected, -60);   


   CURSOR c_files
   IS
        SELECT surr_id
          FROM batch_pre_dist_data_fix 
         WHERE status = 'REQ'
      ORDER BY surr_id;

   TYPE file_type IS TABLE OF batch_pre_dist_data_fix.surr_id%TYPE;
   l_file_tab file_type;

BEGIN

   LOOP

        BEGIN

            OPEN c_files;
            FETCH c_files BULK COLLECT INTO l_file_tab;
            CLOSE c_files;

            IF l_file_tab.COUNT > 0
                THEN

                    FOR i IN 1..l_file_tab.COUNT
                    LOOP    

                    -- update main table with start date
                        UPDATE batch_pre_dist_data_fix
                        SET start_dtm = SYSDATE
                        WHERE surr_id = l_file_tab(i);

                    -- update tables
                        update_soundmouse_tables (l_file_tab(i));

                   END LOOP;

           END IF;

        Dbms_Lock.Sleep(5*60); -- sleep for 5 mins before looking for more records to process

        -- if there is a deadlock or a locked record then log the error, rollback and wait 5 minutes, then loop again 
        EXCEPTION
            WHEN lx_deadlock_detected OR lx_record_locked THEN
                ROLLBACK; 
                Dbms_Lock.Sleep(5*60);   -- sleep for 5 minutes before processing records again

        END;

   END LOOP;

END process_dist_data_fix;

Answer 1

First understand that deadlock is a completely differnt issue than a "record locked". So for "record locked" most of the time there should not be anything that you need to do. 9/10 you want the program to wait on a lock. if you are waiting too long then you may have to redefine your transaction boundaries. For example here your code pattern is quite typical. You read a list of "to do" and then you "do it". In such cases it will be rare that you want to do something special for "record locking". if batch_pre_dist_data_fix table row is locked for some reason you should simply wait for lock to release and continue. if reverse is true, that since this job takes so long and you are locking batch_pre_dist_data_fix for so long for another process, then you may want to redefine transaction boundary. i.e. maybe you say that after each loop iteration you commit. But beware of how open cursor behave on commit.

Deadlock is a completely differnt animal. Here you always have "another session" and db detected a situation where you will never be able to get out of the situation and it killed one random session. So it is when Session 1 is waiting on a resource of session 2 and session 2 is waiting on a resource f session 1. That is an infinite wait condition that db can detect so it kills one random sessoin. One simple way to address that is that if all transactions that deal with multiple tables simply lock them in same order we will not have a deadlock. So lets say we have table A,B,C,D. If we simply decide that tables will be locked in this order. i.e. it is ok to do A,B,D or A,C,D or C,D - but never D,C. Then you will not get deadlock. To troubleshoot deadlock see the dump oracle created when it gave the error and find the other session and what was list of tables that session had and see how they should be locked.