title: Savepoint in an Oracle PL/SQL LOOP to stop deadlocks or record locks contention
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <pre><code>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 &gt; 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;
  </code></pre>