Oracle Database / Basic: relational and transaction foundations / Lesson 6

Transactions, savepoints and DDL boundaries

Design a shipment change as an explicit unit of work and understand Oracle implicit commits.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Explain visibility, COMMIT, ROLLBACK and savepoints.
Keep schema changes outside an assumed DML rollback boundary.

A transaction protects a business transition

Booking a shipment can require a parent row, reserved capacity and an audit event. If these facts must agree, they belong in one explicit unit of work. A transaction is not automatically one HTTP request or one SQL script: connector autocommit, pooled connection reuse and helper functions can change its boundary. Document who begins and ends the transaction and how exceptional paths terminate it. An application should not leave the final decision to a connection close, because tool and driver behavior differ and abnormal termination is not a reliable business protocol.

Your own changes differ from others’ visibility

A session normally sees its own uncommitted changes. Other sessions do not read those uncommitted versions. Under the default READ COMMITTED isolation, each ordinary query sees a consistent committed view for that statement, so two queries in the same transaction can legitimately observe different committed work from other sessions. This does not authorize a read-modify-write race. If a capacity invariant matters, use a conditional update or a reviewed locking protocol. A screenshot from the writing session is therefore insufficient evidence that a separate application session can already see the new booking.

Undo a branch without losing earlier work

A savepoint marks a position inside the current transaction. ROLLBACK TO undoes work after that marker while preserving earlier changes; it does not commit those earlier changes. The teaching trace starts with capacity ten, reserves two units and then reverses a later one-unit adjustment. The reservation remains pending until the final COMMIT. Actual affected-row checks must be part of application logic, especially if the selected identifier is missing. Savepoint names belong to one transaction and are erased by a full commit or rollback.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

UPDATE capacity SET free_units = free_units - 2 WHERE slot_id = 7;
SAVEPOINT after_reservation;
UPDATE capacity SET free_units = free_units - 1 WHERE slot_id = 7;
ROLLBACK TO after_reservation;
COMMIT;

Assuming slot 7 begins at 10 and both updates match one row, the committed free_units is 8. This trace is reasoned, not executed.

DDL can end preceding DML unexpectedly

Oracle issues an implicit commit before syntactically valid DDL, even when that DDL later fails, and after successful DDL. Consequently, CREATE TABLE placed after an uncommitted booking can commit the booking before the creation attempt. A later ROLLBACK cannot undo that committed work. Do not port a PostgreSQL migration strategy by assuming all schema and data changes share one reversible transaction. Plan schema evolution as reviewed phases with explicit checkpoints and compensating actions. The safe course example is an explanatory sequence only and must not be executed against a configured database.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

INSERT INTO booking_log (booking_id, note) VALUES (501, 'lab booking');
CREATE TABLE booking_probe (probe_id NUMBER);
ROLLBACK;

The INSERT can already be committed by the DDL boundary; the final ROLLBACK is not a schema-and-data reset.

A lost response creates uncertainty

A network failure after sending COMMIT does not prove rollback. The server may have made the transaction durable before the acknowledgment was lost. Retrying an unkeyed insert can duplicate the business operation. Design a unique request identifier and a reconciliation query so the application can discover whether the intended booking already exists. Keep external effects such as email behind a reliable outbox or equivalent coordinated protocol; a database rollback cannot retract a message already delivered. Transaction correctness includes the client’s knowledge of the outcome, not only the engine’s internal state.

Common errors and fixes

ORA-01086

Why it happens: The named savepoint is no longer valid after a commit, rollback or different transaction context.
How to fix it: Review the actual transaction boundary, including helper-issued commits and DDL.
Prevent it next time: Keep transaction ownership explicit in the service layer.

ROLLBACK did not undo an earlier INSERT

Why it happens: An intervening DDL statement implicitly committed preceding DML.
How to fix it: Reconcile committed data and use a reviewed compensating change if needed.
Prevent it next time: Separate migration phases from application transaction examples.

A retry creates a second booking

Why it happens: The client assumed a lost commit response meant failure.
How to fix it: Resolve the request identifier before replaying the business operation.
Prevent it next time: Enforce idempotency with a unique persisted business request key.

Check your understanding

1. A savepoint follows reservation but precedes an optional note. What remains after ROLLBACK TO that savepoint?

Show worked solution

The reservation remains uncommitted in the transaction and the later note is undone. The application must still choose COMMIT or full ROLLBACK for the overall unit. Verify affected-row counts and avoid sending external confirmations until the durable business outcome is known.

2. An INSERT is followed by syntactically valid DDL that fails because an object name exists. Can a following rollback reliably erase the insert?