Question about Oracle locking and summarisation

Question

First, here are some scripts to setup the tables and background.

CREATE TABLE TEST_P
(
  ID    NUMBER(3) NOT NULL PRIMARY KEY,
  SRC   VARCHAR2(2) NOT NULL,
  DEST  VARCHAR2(2) NOT NULL,
  AMT   NUMBER(4) NOT NULL,
  B_ID_SRC  NUMBER(3),
  B_ID_DEST NUMBER(3)
);

A row in this table indicates that AMT is being moved from SRC to DEST. The ID column is a surrogate key. The first row indicates 10 thingies are being moved from B1 to S1. The values in SRC and DEST are different - the same value can not appear in both.

INSERT INTO TEST_P VALUES (1, 'B1', 'S1', 10, NULL, NULL);
INSERT INTO TEST_P VALUES (2, 'B2', 'S1', 20, NULL, NULL);
INSERT INTO TEST_P VALUES (3, 'B3', 'S2', 40, NULL, NULL);
INSERT INTO TEST_P VALUES (4, 'B1', 'S2', 80, NULL, NULL);
INSERT INTO TEST_P VALUES (5, 'B4', 'S2', 160,NULL, NULL);

There is another table like this. It has a different view of the same information. Each row here indicates something added or removed from "Who". Values in WHO are B1, B2.. and S1, S2...

CREATE TABLE TEST_B
(
  ID       NUMBER(3)   NOT NULL  PRIMARY KEY,
  BATCH    NUMBER(3)   NOT NULL,
  WHO      VARCHAR2(2) NOT NULL,
  AMT      NUMBER(4)   NOT NULL
);

CREATE SEQUENCE TEST_B_SEQ START WITH 100;

In need to write a process that will periodically take values from TEST_P and populate TEST_B. It must also update B_ID_SRC and B_ID_DEST which are foreign keys into TEST_B.

Here is my solution so far.

Step 1:

INSERT INTO TEST_B
(ID, BATCH, WHO, AMT)
SELECT TEST_B_SEQ.NEXTVAL, 42, WHO, AMT FROM
(
  SELECT SRC AS WHO, SUM(AMT) AMT FROM TEST_P
  WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
  GROUP BY SRC
  UNION ALL
  SELECT DEST, -SUM(AMT) FROM TEST_P
  WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
  GROUP BY DEST)
;

Step 2:

UPDATE TEST_P
  SET B_ID_SRC = (SELECT ID FROM TEST_B WHERE BATCH = 42 AND TEST_P.SRC = WHO),
      B_ID_DEST = (SELECT ID FROM TEST_B WHERE BATCH = 42 AND TEST_P.DEST = WHO);

There are two problems with this:

1) The rows in the SELECT should be locked. How can I do this select with a FOR UPDATE?

2) If a row is inserted by another session and commited at Step 1.5 then the UPDATE will catch more rows than the INSERT. How should I resolve this without reverting to row by row processing?

Further Details The real TEST_P table has a status column on it. It's only when things are in the correct status that they are included into TEST_B.

For various reasons TEST_B is actually required. I can't just make it a view or something. There is subsequent processing, etc.

Answer 1

Hi WW,

in your exemple you're going to update all rows of TEST_P. Two simple solutions would allow you to be sure that the information on both tables is consistent. You could either:

1. LOCK TABLE test_p IN EXCLUSIVE MODE for the duration of your transaction (the other inserting sessions would have to wait) or
2. ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE this would prevent the first session to see the changes made by other sessions after the beginning of the transaction.

Method 1 is straightforward, I will demonstrate method 2:

session 1> ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;

Session altered

session 1> INSERT INTO TEST_B
        2  (ID, BATCH, WHO, AMT)
        3  SELECT TEST_B_SEQ.NEXTVAL, 42, WHO, AMT FROM
        4  (
        5    SELECT SRC AS WHO, SUM(AMT) AMT FROM TEST_P
        6    WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
        7    GROUP BY SRC
        8    UNION ALL
        9    SELECT DEST, -SUM(AMT) FROM TEST_P
       10    WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
       11    GROUP BY DEST)
       12  ;

6 rows inserted

Here I insert a row with another session and commit:

session 2> INSERT INTO TEST_P VALUES (6, 'B4', 'S2', 2000,NULL, NULL);

1 row inserted

session 2> commit;

Commit complete

Session 1 does not see the row inserted with session 2 yet:

session 1> select * from TEST_P;

  ID SRC DEST   AMT B_ID_SRC B_ID_DEST
---- --- ---- ----- -------- ---------
   1 B1  S1      10          
   2 B2  S1      20          
   3 B3  S2      40          
   4 B1  S2      80          
   5 B4  S2      16

session 1> UPDATE TEST_P
        2    SET B_ID_SRC = (SELECT ID FROM TEST_B WHERE BATCH = 42 AND TEST_P.SRC = WHO),
        3        B_ID_DEST = (SELECT ID FROM TEST_B WHERE BATCH = 42 AND TEST_P.DEST = WHO);

5 rows updated

session 1> commit;

Commit complete

The result is consistent, after the commit session 1 will see the row inserted by session 2:

session 1> select * from TEST_P;

  ID SRC DEST   AMT B_ID_SRC B_ID_DEST
---- --- ---- ----- -------- ---------
   6 B4  S2    2000          
   1 B1  S1      10      100       104
   2 B2  S1      20      101       104
   3 B3  S2      40      102       105
   4 B1  S2      80      100       105
   5 B4  S2     160      103       105

6 rows selected

Answer 2

A single MERGE statement can handle the requirement here.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606

Your statement would be something along the lines of:

MERGE INTO TEST_B
USING 
(
  SELECT SRC AS WHO, SUM(AMT) AMT FROM TEST_P
  WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
  GROUP BY SRC
  UNION ALL
  SELECT DEST, -SUM(AMT) FROM TEST_P
  WHERE B_ID_SRC IS NULL AND B_ID_DEST IS NULL
  GROUP BY DEST)
ON (
WHEN MATCHED THEN UPDATE SET ...;

It might be more effiecient to identify the rows that need to be updated though a join to the target table in the USING clause, to avoid updating rows that do not need to be modified.