PL/SQL - do not update changed rows

Question

I'm planning to do a long running update on my huge table (more than billion rows). This update will multiply one column's values by fixed number.

The problem is that during my update (which may last several hours) there will definitely be short transactions that will update some rows and those rows will have correct value that should not be updated though they will still satisfy my update's condition.

So the question is - how do I skip (do not update) rows that were updated outside my long running update's transaction?

Answer 1

One way is to use FOR UPDATE SKIP LOCKED such that other sessions won't be able to pick the rows which are already picked for update.

For example,

Session 1:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno = 10
  4  FOR UPDATE NOWAIT;

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10

SQL>

Session 2:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno in (10, 20)
  4  FOR UPDATE NOWAIT;
  FROM emp  WHERE
       *
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Now let's skip the rows which are locked by session 1.

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno IN (10, 20)
  4  FOR UPDATE SKIP LOCKED;

     EMPNO     DEPTNO
---------- ----------
      7369         20
      7566         20
      7788         20
      7876         20
      7902         20

SQL>

So, department = 10 were locked by session 1 and then department = 20 are locked by session 2.