Transaction Boundaries, Retries, Idempotency, Distributed Transaction Awareness, and Failure Semantics

Learning outcomes

A ServiceHub API sends COMMIT, then loses the socket before receiving the acknowledgement. The database may have committed even though the client sees an exception. Blind retry can duplicate a payment or work order. Resilience therefore distinguishes transaction ownership, retry classification, commit-outcome uncertainty, and idempotency.

1. Application code owns the transaction boundary

Python-oracledb and node-oracledb default to autocommit off. JDBC connections default to JDBC autocommit behavior unless disabled. In .NET, make the OracleTransaction scope explicit for multi-statement work. A transaction should match a business invariant, not a repository method.

try (Connection c = ds.getConnection()) {  c.setAutoCommit(false);  try {    // SQL statement 1    // SQL statement 2    c.commit();  } catch (Exception e) {    c.rollback();    throw e;  }}

with pool.acquire() as conn:    try:        with conn.cursor() as cur:            cur.execute("update ...", binds)            cur.execute("insert ...", binds2)        conn.commit()    except:        conn.rollback()        raise

Oracle DDL has implicit commit boundaries independent of ordinary DML autocommit settings, so schema migrations require their own deployment design.

2. Retry taxonomy

A blanket “retry every ORA error” policy is unsafe. The driver error, database transaction state, and business operation all matter.

3. Idempotency makes one business request recognizable

BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh27_request_outcome PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh27_payment PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE sh27_request_outcome (  request_id VARCHAR2(64) PRIMARY KEY,  tenant_code VARCHAR2(20) NOT NULL,  operation_name VARCHAR2(40) NOT NULL,  outcome_code VARCHAR2(20) NOT NULL,  result_id NUMBER,  created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL);CREATE TABLE sh27_payment (  payment_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  request_id VARCHAR2(64) NOT NULL UNIQUE,  tenant_code VARCHAR2(20) NOT NULL,  amount NUMBER(12,2) NOT NULL,  status_code VARCHAR2(12) NOT NULL);

4. Commit effect and outcome together

DECLARE  l_payment_id NUMBER;BEGIN  INSERT INTO sh27_payment(    request_id,tenant_code,amount,status_code  ) VALUES(    :request_id,:tenant_code,:amount,'ACCEPTED'  )  RETURNING payment_id INTO l_payment_id;  INSERT INTO sh27_request_outcome(    request_id,tenant_code,operation_name,    outcome_code,result_id  ) VALUES(    :request_id,:tenant_code,    'CREATE_PAYMENT','ACCEPTED',l_payment_id  );  COMMIT;END;/

If the client loses the response, reconnect and query the same durable request ID. Do not invent a new ID for the retry.

5. Deliberate duplicate retry

INSERT INTO sh27_payment(  request_id,tenant_code,amount,status_code) VALUES(  'REQ-1001','TENANT_A',125.00,'ACCEPTED');-- If REQ-1001 already committed:-- ORA-00001: unique constraint (...) violated

Translate the uniqueness conflict to “request already exists” only after validating that the existing record belongs to the same tenant/operation and then return its stored outcome.

6. Reproduce a transient lock conflict

CREATE TABLE sh27_lock_target (  item_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL);INSERT INTO sh27_lock_target VALUES(1,'OPEN');COMMIT;

UPDATE sh27_lock_targetSET status_code='BUSY'WHERE item_id=1;-- Keep transaction open temporarily.

SELECT item_id,status_codeFROM sh27_lock_targetWHERE item_id=1FOR UPDATE NOWAIT;-- Expected while A owns the row:-- ORA-00054: resource busy and acquire with NOWAIT specified

Bounded retry may be appropriate for a short transient lock. A tight infinite loop amplifies contention; observe blockers and repair long transaction scope if this is structural.

7. Safe repair/verification

ROLLBACK;

SELECT item_id,status_codeFROM sh27_lock_targetWHERE item_id=1FOR UPDATE NOWAIT;-- Succeeds after A releases the row.ROLLBACK;

8. Transaction Guard resolves commit-outcome ambiguity

Transaction Guard preserves a logical transaction identity/outcome so applications can determine whether the last submission committed after recoverable errors. 26ai also introduces Database Native Transaction Guard improvements. Current licensing marks Transaction Guard unavailable in Free.

SELECT  name, commit_outcome, retention_timeout, failover_typeFROM all_servicesORDER BY name;

Do not enable commit-outcome service attributes solely because the dictionary exposes them when the deployment is not entitled.

9. AC/TAC replays protected requests

Application Continuity can replay eligible in-flight requests after recoverable outages and uses Transaction Guard to avoid duplicate committed work. It is not a generic catch-and-retry loop and is not licensed in Free. It requires an appropriate AC/TAC service and matching HA entitlement/topology.

10. XA/two-phase commit is for real atomic multi-resource requirements

XA lets a transaction manager coordinate multiple resource managers using two-phase commit. This can preserve atomicity across Oracle and another XA resource, but introduces prepare/in-doubt recovery, longer resource holding, and transaction-manager configuration. Use it when the business invariant truly requires one atomic multi-resource outcome.

Microsoft Distributed Transaction Coordinator support is a Windows-specific development-platform feature in the current licensing matrix; Java XA/ODP.NET integration has separate runtime requirements.

11. Transactional outbox keeps the database transaction local

CREATE TABLE sh27_outbox (  event_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  request_id VARCHAR2(64) NOT NULL,  event_type VARCHAR2(40) NOT NULL,  payload_json JSON NOT NULL,  published_at TIMESTAMP,  created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  CONSTRAINT sh27_outbox_request_uq    UNIQUE(request_id,event_type));

INSERT INTO sh27_outbox(  request_id,event_type,payload_json) VALUES(  :request_id,  'PAYMENT_ACCEPTED',  JSON_OBJECT(    'requestId' VALUE :request_id,    'amount' VALUE :amount    RETURNING JSON  ));COMMIT;

A publisher later delivers committed outbox events with its own retry/deduplication. This removes XA across Oracle and a message broker but makes external delivery eventual rather than part of one distributed commit.

12. Saga is compensation, not rollback

A saga commits local steps and, on later failure, runs compensating business actions. A refund is a new durable business event; it is not the same as rolling back the original payment. Use saga/outbox where long-running workflows fit explicit eventual consistency better than cross-service 2PC.

13. Cleanup and production judgment

DROP TABLE sh27_outbox PURGE;DROP TABLE sh27_lock_target PURGE;DROP TABLE sh27_payment PURGE;DROP TABLE sh27_request_outcome PURGE;

Keep transactions short and explicit, never return pooled connections with open work, classify retries by outcome and business semantics, and persist idempotency where duplicate effects matter. Use Transaction Guard/AC where licensed; use XA only for genuinely atomic multi-resource work; otherwise outbox/saga often exposes failures more clearly.

The mandatory lab is Free-compatible; Transaction Guard and AC are not. Lesson 5 now examines hidden ORM SQL and schema-deployment failures.

Authoritative references

• node-oracledb Transaction Management — autocommit/transaction behavior
• python-oracledb SQL Execution — commit/rollback behavior
• Ensuring Application Continuity — Transaction Guard/AC mechanisms
• ALL_SERVICES — service commit/failover properties
• Licensing Information — Transaction Guard/AC/DTC boundaries