A customer maintenance form in Uniface 10, part 4 - the platform already had optimistic locking, I just made the error message worse

The customer form from [parts 1-3] does CRUD, validates, searches, warns about duplicates and has a tested service behind it. The next item on the list was the one you cannot retrofit cheaply later:

Two people open the same customer. Both edit. Both save. The second one wins and nobody ever finds out.

I went in planning to build a version-column check. I came out having deleted most of it, because Uniface had been doing optimistic locking the whole time - it was just reporting the conflict in a way that ended the application. This post is that detour, in the order it happened, because the wrong turns are the useful part.

The plan I started with

Classic and portable:

1. Add CHANGED_AT, CHANGED_BY for the audit trail.
2. Add a version token, compare it before saving, refuse on mismatch.

ALTER TABLE CUSTOMER ADD COLUMN CHANGED_AT DATETIME;
ALTER TABLE CUSTOMER ADD COLUMN CHANGED_BY VARCHAR(30);
ALTER TABLE CUSTOMER ADD COLUMN VERSION_NO INTEGER;

Two practical notes before the interesting part:

• The IDE's EDIT SQL dialog runs one statement per execution. My three-statement script ran silently into nothing - no error, no output, no columns. One statement, then COMMIT, then the next.
• To add a field to a modeled entity, drag a template from the palette onto the entity row, then click the new field's name to rename it. Actions -> Duplicate duplicates the whole entity, which is how I briefly ended up with a CUSTOMER_1.CUSTOMER_MDL in the model.

The first version token was going to be CHANGED_AT. I dropped that before writing it: in ProcScript the timestamp is a formatted value, in the SQLite column it is the DBMS format, and comparing the two in a WHERE clause is a bet on formats. A numeric counter compares cleanly - which is also the idea behind Uniface's own U_VERSION shortcut field.

So: VERSION_NO, and a service operation to check it.

public operation CHECK_VERSION
params
    numeric pId : IN
    numeric pVersion : IN
    boolean pConflict : OUT
    string pError : OUT
endparams
variables
    numeric vVersion
    string vSql
endvariables
    pConflict = 0
    pError = ""
    vVersion = 0
    if (pVersion != "")
        vVersion = pVersion
    endif
    vSql = "SELECT COUNT(*) FROM CUSTOMER WHERE CUSTOMER_ID = %%(pId) AND COALESCE(VERSION_NO, 0) = %%(vVersion)"
    sql vSql, "CUSTOMERS"
    if ($status < 0)
        pError = $concat("The version check failed (error ", $procerror, ").")
        return -1
    endif
    if ($result < 1)
        pConflict = 1
    endif
    return 0
end

COALESCE(VERSION_NO, 0) so rows that predate the column count as version 0 - no backfill needed. DO_SAVE called this before store, and on conflict showed a dialog instead of overwriting.

Compiled, 0 errors. Now it needed a real second user.

How to simulate a second user in the IDE

This turned out to be easier than expected, and it is worth knowing: while a form runs from "Compile & Test", the IDE window itself stays usable. They are two windows of the same process. So:

1. Run the form, load a customer.
2. Alt-tab to the IDE, open EDIT SQL on the $CUSTOMERS path, and change that row.
3. Alt-tab back and press Save.

UPDATE CUSTOMER SET PHONE = 'EXTERN 8', VERSION_NO = 8 WHERE CUSTOMER_ID = 9
COMMIT

(Writing to the SQLite file from outside Uniface while the form has it open is not an option - in my setup that fails with disk I/O error. Going through the IDE's own connection avoids the whole question.)

Result 1: the check said "no conflict"

The form still displayed the stale phone number, so the occurrence was clearly not current. And the save went through with a cheerful "The customer was saved."

The putmess line I had added for exactly this moment told me why:

DO_SAVE version check: mem 8, conflict F

mem 8 - the version in memory was already the new one. The comparison was comparing the database against itself.

Two things caused that, and both are worth writing on a sticky note:

1. A field that is not painted is not in the occurrence buffer. VERSION_NO existed in the model but was not on the form. Reading VERSION_NO.CUSTOMER then does not give you "the value as retrieved" - it gives you a current value. Only painted fields are part of what the form holds.

2. The generated lock trigger reloads the occurrence behind your back. This is in every modeled entity, straight from the template:

trigger lock
throws
  try
    lock
  catch <UIOSERR_UPDATE_NOT_ALLOWED>
    return -5
  catch <UIOSERR_WRITE_FAILURE>
    return -6
  catch <UIOSERR_DUPLICATE_KEY>
    return -7
  catch <UIOSERR_LOCK_DATA_MISMATCH>
    ; Occurrence has been modified or removed since it was retrieved
    ; Reload the occurrence and continue processing
    reload
  catch <UIOSERR_LOCKED>
    return -11
  endtry
end

The lock trigger fires at field start modification in an interactive form - the moment the user types the first character. If the row changed in the meantime, the default behaviour is reload: silently refresh the occurrence and carry on. By the time DO_SAVE runs, any token you kept in the occurrence is the new one.

So a hand-rolled pre-check inside an interactive form is structurally defeated. Not "buggy" - defeated. That is a design property of the platform, and it took a log line to see it.

Result 2: the platform was already catching it

Then I made the conflict bigger (changed a field the reload could not paper over) and got this:

An uncaught exception is causing Uniface to exit

ERROR= -10
WHERE= <UIOSERR_LOCK_DATA_MISMATCH>
DESCRIPTION=Lock: data mismatch
COMPONENT=CUSTOMER_FRM
TRIGGER=WRITE
MODULE=occTrigger WRITE 1 of CUSTOMER.CUSTOMER_MDL
MODULE=entry DO_SAVE 73

and in the status line:

2012 - Occurrence in form does not match database occurrence

There it was. The entity property Locking = Y (Cautious) had been set since the day the entity was created, and the kernel compares the row's field values on update. It had detected the concurrent change. Correctly. Every time.

The problem was never detection. The problem was that the detection arrived as an uncaught exception that terminates the application, because the generated write trigger starts with throws:

trigger write
throws
  write
end

Readers of [part 1] will recognise this exactly: the same single word in the generated read trigger crashed the form on an empty result set. Same template convention, same consequence, different trigger.

The fix, which is smaller than the plan

1. Remove throws from the entity's write trigger. Now store/e returns a negative status instead of unwinding out of the application.

2. Map error -10 to a real conversation in DO_SAVE:

    store/e "CUSTOMER"
    vStatus  = $status
    vErrCode = $procerror
    if (vStatus < 0)
        rollback
        if (vIsNew)
            CREATED_AT.CUSTOMER = ""
        endif
        if (vErrCode = -10)
            call CONFLICT_PROMPT
            return -1
        endif
        putmess $concat("DO_SAVE store failed: status ", vStatus, ", procerror ", vErrCode)
        message/error $concat("The customer could not be saved (status ", vStatus, ", error ", vErrCode, ").")
        return -1
    endif
    commit

Note again that vStatus and vErrCode are captured before rollback - rollback is a statement and resets $status/$procerror.

3. One entry for the conversation itself:

entry CONFLICT_PROMPT
variables
    string vId
endvariables
    vId = CUSTOMER_ID.CUSTOMER
    askmess/question "This customer was changed by someone else in the meantime. Your changes have not been saved. Do you want to load the current data and discard your changes?~Changed by someone else", "Reload,Keep editing"
    if ($status = 1)
        call LOAD_LIST
        call SELECT_BY_ID(vId)
    endif
    return 0
end

Reload re-reads the list and re-selects the same customer by ID, so the user sees what is actually stored. Keep editing leaves their input on screen so they can copy something out of it before trying again. Nothing is overwritten either way.

4. Maintain the audit fields on every save:

    VERSION_NO.CUSTOMER = vVersion + 1
    CHANGED_AT.CUSTOMER = $datim
    CHANGED_BY.CUSTOMER = $user

CHANGED_AT and CHANGED_BY are painted read-only (NED) in the detail area, so "who last touched this record, and when" is visible rather than forensic. VERSION_NO is kept as an explicit counter: it makes the kernel's comparison fail even in the pathological case where a concurrent change produced field-identical values, and it is cheap.

And CHECK_VERSION? It stays in the service, tested, unused by the form. It is the right tool for a non-interactive caller - a batch job or an API that reads, computes and writes back without a lock trigger in between. It is the wrong tool inside a form, and the code says so.

Testing it

The service suite grew by three cases (now 33 tests, 0 failures), all of which run against the real database and roll back at the end:

entry TEST_VERSION
params
    numeric pTests : INOUT
    numeric pFailures : INOUT
endparams
variables
    boolean vConflict
    string vError, vDetail
    numeric vStatus
endvariables
    sql "DELETE FROM CUSTOMER WHERE CUSTOMER_ID = 999998", "CUSTOMERS"
    sql "INSERT INTO CUSTOMER (CUSTOMER_ID, LAST_NAME, FIRST_NAME, CREATED_AT, VERSION_NO) VALUES (999998, 'Version', 'Test', '2026-01-01 00:00:00', 5)", "CUSTOMERS"
    activate "CUSTOMER_SVC".CHECK_VERSION(999998, 5, vConflict, vError)
    vStatus = $status
    vDetail = "status %%(vStatus), conflict %%(vConflict), %%(vError)"
    call CHECK("VERSION current version is no conflict", (vStatus = 0 & vConflict = 0), vDetail, pTests, pFailures)
    activate "CUSTOMER_SVC".CHECK_VERSION(999998, 4, vConflict, vError)
    ...
    call CHECK("VERSION older version is a conflict", (vStatus = 0 & vConflict = 1), vDetail, pTests, pFailures)
    activate "CUSTOMER_SVC".CHECK_VERSION(999997, 0, vConflict, vError)
    ...
    call CHECK("VERSION missing customer is a conflict", (vStatus = 0 & vConflict = 1), vDetail, pTests, pFailures)
    return 0
end

PASS: VERSION current version is no conflict
PASS: VERSION older version is a conflict
PASS: VERSION missing customer is a conflict
Customer service: 33 tests, 0 failures

The part that matters most, though, cannot be unit-tested: the full loop of load -> someone else changes the row -> save -> dialog -> reload -> save again. That one is the two-window procedure from above, written down in the project's handover file as a checklist so it gets repeated after every change to DO_SAVE.

What I would tell my past self

Check what the platform already does before building it. I spent the first half of this on a mechanism that existed, was already switched on, and was already correct. The tell was visible the whole time in the entity's property sheet: Locking = Y (Cautious).

"It doesn't work" and "it works and reports badly" look identical to a user. An uncaught exception that closes the application is what an undetected conflict feels like, and it is the reason I assumed nothing was there. The entire real fix was deleting one word (throws) and writing one dialog.

Generated triggers are code you own. Twice now, the same template convention - throws on the generated trigger - turned a handled situation into a terminated application. The templates are a starting point for a component you are responsible for, not a runtime you leave alone.

A log line beats an hypothesis. DO_SAVE version check: mem 8, conflict F ended twenty minutes of theorising in one second. putmess costs one line and writes into log\ide_<pid>.log; in a UI-driven 4GL where you cannot step through a running form easily, it is the debugger.

Know which layer owns a rule. Part 3 ended with the same lesson from the opposite direction: a MAN field syntax in the model competing with validation in the service, and the platform winning the race. Here the platform was right and my extra layer was the one that had to go. Either way, two layers enforcing one rule is one layer too many - decide which one owns it, and delete the other.

Next up for this application: a status flag instead of hard deletes, so that customers with history can be deactivated rather than removed. That one is mostly SQL and a checkbox - and, for once, no platform behaviour to discover first.