CODEWITHSUNDEEP
oracle-databaseplsqltransactions
Dan Karbayev
Dan Karbayev

Reputation: 2930

PL/SQL - do not update changed rows

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?

Upvotes: 3

Views: 437

Answers (3)

Dan Karbayev
Dan Karbayev

Reputation: 2930

Oh, I absolutely forgot about this question. So, I ended up making a snapshot of current rows by saving their copies to another table (I had to update only those rows that satisfied given condition). After that I updated rows that haven't changed their values (using merge).

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

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.

Upvotes: 4

Suttipong Pourpawawead
Suttipong Pourpawawead

Reputation: 141

I have done something like your problem but my table isn't too huge like your.

I re-designed my table, added 2 columns.

created_date: A Trigger put sysdate when insert data.

modified_date: A Trigger put sysdate when update data.

Then I can use created_date or modified_date in my where clause.

Example:

UPDATE TABLE table_name
SET column_name = 'values'
WHERE created_date < SYSDATE;

I hope this will help you.

Upvotes: 1

Related Questions