Handle concurrent updates and transaction boundaries

Development and modifications

26R1 reference · Documentation-based walkthrough · Sources reviewed 2026-09-28 · Not executed in IFS or Oracle. Match the database, build home, and tools to your installed release. Example names are fictional.

Prevent a decision based on stale data from overwriting another user’s change. First observe the behavior in a disposable Oracle table, then map the requirement to the actual IFS API’s version/locking contract.

Before you start

Use a separate disposable schema with two SQL sessions connected to it. Confirm the name c_demo_handover_lock is unused. This exercise creates and drops a table; DDL commits implicitly. It is not an IFS business-table customization or a production deployment script.

In IFS, use supported business APIs and their real version tokens. Do not manufacture an objversion from a guessed rowversion format.

1. Create the isolated fixture

Run once before either session holds an exercise transaction:

CREATE TABLE c_demo_handover_lock (
   id NUMBER PRIMARY KEY,
   quantity NUMBER NOT NULL,
   version_no NUMBER NOT NULL
);
INSERT INTO c_demo_handover_lock VALUES (1, 10, 1);
COMMIT;

Expected starting row: ID 1, quantity 10, version 1. Record this baseline.

2. Observe a pessimistic lock

1. In session A, run the following query and leave the transaction open:

SELECT quantity, version_no
FROM c_demo_handover_lock
WHERE id = 1
FOR UPDATE NOWAIT;

2. In session B, run the same query. Expect a resource-busy error while A holds the lock.
3. In A, issue ROLLBACK to end the exercise transaction and release the lock.
4. Retry the query in B. It should now succeed. Issue ROLLBACK in B too.

A normal non-locking SELECT may still read a consistent committed version. Closing a cursor does not itself commit the transaction or release its row locks. NOWAIT is useful here to avoid an indefinite training wait; a real application needs an agreed wait/timeout response.

3. Demonstrate an optimistic stale-write check

1. In B, read version_no without locking and record 1.
2. In A, execute and commit:

UPDATE c_demo_handover_lock
SET quantity = 11, version_no = version_no + 1
WHERE id = 1 AND version_no = 1;
COMMIT;

3. In B, attempt a change using its stale value:

UPDATE c_demo_handover_lock
SET quantity = 12, version_no = version_no + 1
WHERE id = 1 AND version_no = 1;

4. Expect zero rows updated. Do not report success. Read the current row, explain the conflict, and decide whether the business operation can be safely retried.
5. Roll back B and check the final row: quantity 11, version 2.

The numeric version is an exercise mechanism. In IFS, preserve the actual supported API’s optimistic token/ETag and error response instead of reimplementing this table pattern against application data.

4. Choose locking for the complete business decision

If a server operation reads a value and uses it to decide a subsequent write, inspect whether the supported operation already performs the check atomically. Merely trusting a later lock does not guarantee that the earlier decision was current.

Use a supported atomic API, an appropriate version check, or deliberate locking within one transaction. Additional FOR UPDATE is not mandatory before every Modify__ or Remove__: unnecessary locks increase contention and can interact badly with the API’s lock order. Test both sessions and the real call chain.

Keep lock ordering consistent across multi-row operations. Do not hold database locks while waiting for a user, a remote HTTP response, or a long computation. Use SKIP LOCKED only for a designed queue-processing contract; skipping a locked business row silently may lose required work.

5. Set transaction ownership and retry rules

1. Identify the framework/caller that owns commit and rollback.
2. Keep a logical business operation atomic. A reusable nested method should not independently commit its caller’s unrelated work.
3. Inject a failure after the first of two dependent changes and verify the intended rollback behavior.
4. Treat outbound email/HTTP effects separately: a database rollback cannot retract an external side effect already sent.
5. For asynchronous delivery, define durable work tracking, an idempotency key, duplicate handling, and a reconciliation path.
6. Retry only documented transient failures with bounded attempts and backoff. Re-read/revalidate after a conflict; do not replay a non-idempotent action blindly.

See integration reliability and workflow asynchronous operations for the delivery side of this design.

Troubleshooting

Symptom	Investigation
Unexpected wait	Transaction still open, lock order, or remote work performed while holding locks
Lost update	Missing/stale token validation or unconditional write
Duplicate external action	Retry without durable deduplication or ambiguous-success handling
Partial business update remains	Hidden commit, swallowed exception, or inconsistent transaction ownership

Exercise and solution

Exercise: session B updates zero rows after its version check. Should it fetch the new version and automatically repeat an action that sends a notification?

Show the solution

Not without revalidating the business decision and checking whether the notification already occurred. A stale-write conflict is not proof that replay is harmless. Use the action’s idempotency and reconciliation contract.

Cleanup and delivery evidence

End both exercise transactions. In the disposable schema, verify that only the training table is being removed, then run DROP TABLE c_demo_handover_lock. Record both-session observations and the corresponding IFS/API tests separately. This page does not claim those procedures have been executed.

Sources

• Oracle locking and consistent reads
• Oracle SELECT locking clauses
• Oracle commits and implicit DDL commits