SELECT FOR UPDATE seems not to work in a PL/SQL-driven Testcase with Oracle 12.1/19.3

Question

I intended to create a simple load test :

An ACCOUNT TABLE
A Procedure Updating the Account Balance
A Start-Script using Oracle dbms_scheduler to run the procedure in parallel.

Each procedure Call covers the Account_ID 1-16 and increments the Account Balance 500 times. So at the end the Account Balance is 500. For the test-case I run 4 concurrent background jobs per Account ID to increment the Account Balance, I would expect 2000 for each Account ID but the results are in randomly a range of 1850 till 1950; I use SELECT for UPDATE to lock the Account ID to be updated, but it does not work. Any ideas?

The table creation :

DROP TABLE ACCOUNTS;
CREATE TABLE ACCOUNTS ( ACC_ID INTEGER NOT NULL , ACC_BALANCE NUMBER (9,3) DEFAULT 0  NOT NULL , START_TIME TIMESTAMP, END_TIME TIMESTAMP);
ALTER TABLE ACCOUNTS ADD CONSTRAINT ACCOUNTS_PK PRIMARY KEY (ACC_ID);


INSERT INTO ACCOUNTS SELECT ROWNUM , 0 , NULL, NULL FROM DBA_USERS DT WHERE ROWNUM <= 16;
COMMIT WORK;

The Procedure :

CREATE OR REPLACE PROCEDURE Paccount_Test(p_Acc_Id     IN INTEGER
                                         ,p_Incr       IN NUMBER
                                         ,p_Num_Incr   IN INTEGER
                                         ,p_Work_Loops IN INTEGER) IS
    Xx            INTEGER := 0;
    l_Acc_Balance Accounts.Acc_Balance%TYPE;
BEGIN

    UPDATE Accounts SET Acc_Balance = 0.0, Start_Time = Systimestamp, End_Time = NULL WHERE Acc_Id = p_Acc_Id;
    COMMIT WORK;

    FOR Nloops IN 1 .. p_Num_Incr LOOP
        SELECT Acc_Balance INTO l_Acc_Balance FROM Accounts WHERE Acc_Id = p_Acc_Id FOR UPDATE;
    
        -- Amoount of work ...
        FOR Ii IN 1 .. p_Work_Loops LOOP
            Xx := Xx + 1;
        END LOOP;
    
        UPDATE Accounts SET Acc_Balance = Acc_Balance + p_Incr WHERE Acc_Id = p_Acc_Id;
        COMMIT WORK;
    END LOOP;

    UPDATE Accounts SET End_Time = Systimestamp WHERE Acc_Id = p_Acc_Id;
    COMMIT WORK;

END Paccount_Test;

The Starter Script for 64 Processes, ...

BEGIN
    FOR Acc_Id IN 1 .. 64 LOOP
        Dbms_Scheduler.Create_Job(Job_Name   => 'One_Time_Job' || Acc_Id
                                 ,Job_Type   => 'PLSQL_BLOCK'
                                 ,Job_Action => 'begin paccount_test (p_acc_id => ' || (mod(Acc_Id,16)+1) ||
                                                ', p_incr => 1, p_num_incr => 500, p_work_loops => 400000); end;'
                                 ,Start_Date => SYSDATE
                                 ,Enabled    => TRUE
                                 ,Auto_Drop  => TRUE
                                 ,Comments   => 'one-time job');
    END LOOP;
END;
/

Ich checked DBA_SCHEDULER_JOB_LOG, but did not see any error, also in V$session 64 Background processes were active.

Example Output of SELECT query :

ACC_ID ACC_BALANCE
1      1932,000
2      1900,000
3      1902,000
4      1883,000
5      1910,000
6      1939,000
7      1920,000
8      1910,000
9      1865,000
10     1920,000
11     1916,000
12     1888,000
13     1896,000
14     1909,000
15     1918,000
16     1935,000

user_13878023 · Answer

I have simulated the issue and the problem is the update statement at the beginning and before increment loop starts which also updates the acc_balance to 0 (it could happen for any record which has no lock at this very time and thus we see the value different than expected 2000) which is causing the issue here and for me too missed at first glance.

We also can make the above update with for update which I didn't try and could solve the problem but I don't think we need to update the acc_balance here and just remove it from the update clause to only update Start_Time and End_Time.

UPDATE Accounts 
   SET Start_Time = Systimestamp
     , End_Time = NULL 
 WHERE Acc_Id = p_Acc_Id;

And if at all it is a load test we always can update the value back to 0 before you actually start the load test otherwise need to find another way to update automatically.

I would wait for your feedback and see if it solves your issue and doesn't require any further modification

SELECT FOR UPDATE seems not to work in a PL/SQL-driven Testcase with Oracle 12.1/19.3

1 Answers