Transaction control: savepoints, DDL commits, and a blocked session
A nightly batch runs:
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 1001; SAVEPOINT s1; DELETE FROM order_lines WHERE order_id = 1001; UPDATE orders SET status = 'CLOSED' WHERE order_id = 1001; ROLLBACK TO s1; TRUNCATE TABLE order_lines; COMMIT;
The developer expected the delete to be undone but status to remain 'CLOSED', and expected to be able to ROLLBACK the truncate if the job failed. While the batch is mid-flight, a second session running SELECT status FROM orders WHERE order_id = 1001 still sees the original value; when that session tries to update the same row, it hangs. ORDERS is the parent of ORDER_LINES via an enabled foreign key.
1. After the script completes, what is the final value of ORDERS.STATUS and are the ORDER_LINES rows present? Justify each step.
Read the worked answer
ROLLBACK TO s1 undoes only what happened after the savepoint. Walking through:
status='SHIPPED'- before s1, survives.DELETE FROM order_lines- after s1, undone; the lines come back.status='CLOSED'- after s1, undone.TRUNCATE TABLE order_lines- DDL, so it performs an implicit COMMIT of the pending transaction (committing 'SHIPPED' permanently) and then deletes every line, irreversibly. It also destroys the savepoint.COMMIT- commits an already-committed empty transaction.
Final state: status = 'SHIPPED', ORDER_LINES empty.
Both expectations in the setup were wrong, and for the same reason: ROLLBACK TO is not a rollback of the whole transaction, and DDL is not part of transaction control at all.
2. What are the consequences of TRUNCATE here, and how would you rewrite the job so a failed run can be undone?
Read the worked answer
TRUNCATE is DDL, not DML: it commits implicitly (the 'SHIPPED' update is committed at that instant), it cannot be rolled back, it invalidates savepoints (a later ROLLBACK TO s1 gives ORA-01086), and it does not fire row-level triggers. It also cannot be used on a parent table while an enabled foreign key references it - truncating ORDERS would raise ORA-02266 unless the child is truncated first or the constraint is disabled; truncating the child here is allowed.
If reversibility matters, use DML inside an explicit transaction:
DELETE FROM order_lines WHERE order_id = 1001;
-- verify, then COMMIT or ROLLBACK
Use TRUNCATE deliberately, as a maintenance operation on a table you intend to empty and cannot partially fail, and never inside a multi-step batch whose earlier steps you may still need to undo. If the truncate is genuinely required, run it as its own transaction boundary: commit the DML first, then truncate.
3. The second session sees the old status and then hangs on its UPDATE. Explain both behaviours and what they imply for the batch design.
Read the worked answer
The batch's UPDATE ... 'SHIPPED' is uncommitted, so it holds a row lock on order 1001. Oracle never allows dirty reads: the second session's SELECT is answered from undo, returning the pre-update value, at statement-level read consistency. That is expected behaviour, not stale data.
When the second session issues UPDATE ... WHERE order_id = 1001, it blocks waiting for that row lock. It resumes only when the batch commits or rolls back; on commit, the second session re-reads the changed row and re-evaluates its WHERE clause, which can cause an updated-row-count of 0 and a lost update if the application does not check it.
Implications: keep transactions short around frequently-updated rows, commit before long-running steps (including before any DDL), and handle ORA-00060 deadlock possibilities by touching rows in a consistent order. A batch that holds a lock across a truncate and an external call will block the online application for its entire duration.