Handling Concurrent User Edits and Lost Update Problems in APEX

The Problem

User A opens an order form. User B opens the same order. User A saves their changes. User B saves their changes, unknowingly overwriting everything User A did. This is the classic “lost update” problem, and it happens silently in any multi-user APEX application that does not implement concurrency control.

Solution 1: Automatic Row Version Detection (Forms)

For form pages built with the APEX Form region, enable “Lost Update Detection” in the form’s Processing section. APEX stores a checksum of the row’s values when the form loads. On submit, it compares the stored checksum with the current database values. If another user changed the row, APEX rejects the save with an error message:

In the Automatic Row Processing (DML) process, set “Lost Update Detection Type” to “Row Values.” This compares all column values. Alternatively, use “Row Version Column” if your table has a version number or last_modified timestamp column.

Solution 2: Row Version Column

ALTER TABLE orders ADD (
    row_version NUMBER DEFAULT 1 NOT NULL,
    last_modified_by VARCHAR2(255),
    last_modified_dt TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- In the update process, check and increment version
UPDATE orders
   SET status = :P20_STATUS,
       total = :P20_TOTAL,
       row_version = row_version + 1,
       last_modified_by = :APP_USER,
       last_modified_dt = SYSTIMESTAMP
 WHERE order_id = :P20_ORDER_ID
   AND row_version = :P20_ROW_VERSION;

IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
        'This record was modified by another user. Please refresh and try again.');
END IF;

Solution 3: Interactive Grid Lost Update Detection

Interactive Grids have built-in lost update detection. In the IG’s Attributes, enable “Lost Update Detection” and set the type to “Row Values” or “Row Version Column.” When another user modifies a row between the time User B loads it and tries to save, the IG shows a conflict indicator on that row.

Solution 4: Pessimistic Locking

For critical forms where lost updates are unacceptable, use SELECT ... FOR UPDATE NOWAIT in a Before Header process to lock the row when the form opens. If another user already has it locked, show a message that the record is being edited. Release the lock on save or cancel. This prevents concurrent edits entirely but reduces concurrency.

Which Approach to Use

Row Values comparison works for most applications with no schema changes. Row Version Column is more reliable for tables with LOB columns or columns excluded from the form. Pessimistic Locking is appropriate for high-stakes records where lost updates would cause significant problems. For Interactive Grids editing many rows, Row Values detection is the most practical approach.