Transactions: One Change, All or Nothing

21.1.1 PLAIN — in simple words#

1. Suppose accepting a reservation must both reduce available stock and record which request received it. Saving only one of those changes leaves an incomplete account of the operation.
2. A transaction gives a selected collection of database work one all-or-nothing boundary. The application decides which related operations belong together and whether their results satisfy its rules.
3. Putting statements inside BEGIN and COMMIT does not make a wrong rule right. A transaction can reliably commit the wrong amount if the application supplied the wrong calculation.

21.1.2 PLAIN — a picture in your head#

1. Mira prepares a reservation slip and the corresponding stock adjustment inside one envelope. The envelope is either accepted as a complete unit or rejected before becoming the official record.
2. The envelope boundary makes it harder to accept the slip while forgetting its stock change.
3. Where the comparison breaks: a transaction is not literally a hidden envelope containing every effect. Network calls, printed receipts and handed-over goods are outside its ordinary database boundary. Visibility also depends on the engine’s isolation rules.

21.1.3 PLAIN — a worked example#

1. In a new teaching inventory, product TX-PEN has five available units. Request TX-R1 asks for three. A successful operation should leave two units and one accepted request record for three units.
2. The incomplete outcomes are easy to name: stock becomes two with no request record, or the request is accepted while stock remains five. Either could mislead later reconciliation.
3. Group the guarded stock change and request insertion into one owned transaction. If either step fails, roll back both transactional changes. If both succeed and all required checks pass, commit.
4. This models reservation records, not physical delivery, payment, authentication or a production checkout.

21.1.4 PLAIN — what is really happening inside#

1. The database tracks the transaction’s changes and coordinates their visibility and persistence under its documented mechanisms.
2. The application must inspect outcomes inside the boundary. An UPDATE affecting zero rows is often a successful SQL statement but an unsuccessful business precondition.
3. Keep the transaction no larger than necessary for the invariant. Waiting for a human to answer a form while holding a transaction open can retain locks, snapshots and resources unnecessarily.

21.1.5 TECHNICAL — the engineer’s version#

1. Define a transaction’s read set, write set, invariants and completion condition. The transaction boundary should include the reads and decisions on which its protected writes depend, subject to a concurrency-control strategy. [S117]
2. State who owns BEGIN, COMMIT and ROLLBACK. A helper that unexpectedly commits its caller’s transaction can split a larger intended atomic operation.
3. In SQLite, explicit transaction forms and driver transaction settings interact. The educational code uses a deliberate connection configuration and owns one transaction rather than relying on an unstated autocommit assumption. [S25] [S86]

21.1.6 WORDS — remember these#

1. Transaction boundary: the work accepted or rejected together — the scope of a database transaction’s reads, writes and completion decision. Business precondition: what must be true before an operation is accepted — an application rule that may require checking affected rows or queried state. Transaction owner: the code responsible for completion — the layer that controls commit and rollback for a defined unit of work.

21.2.1 PLAIN — in simple words#

1. Commit asks the database to accept the transaction’s changes. Rollback abandons changes still inside an uncommitted transaction.
2. A statement succeeding does not mean the whole transaction committed. A later constraint check, communication failure or other error can change the outcome.
3. After a commit acknowledgement is lost, the client may not know whether the database committed. “I did not receive success” is not the same as “nothing happened.”

21.2.2 PLAIN — a picture in your head#

1. A clerk sends an envelope for registration. Before registration, it can be withdrawn. After registration, tearing up the clerk’s local copy does not erase the official record.
2. If the return receipt is lost, the clerk needs to check the registration identity rather than register the same operation again under a new name.
3. Where the comparison breaks: database completion involves protocol and persistence guarantees, not a human registrar. The important analogy is the difference between an uncommitted change and an unknown acknowledgement outcome.

21.2.3 PLAIN — a worked example#

db.execute("BEGIN IMMEDIATE")
try:
    changed = db.execute(
        "UPDATE tx_stock SET available = available - ? "
        "WHERE product_id = ? AND available >= ?",
        (3, "TX-PEN", 3),
    ).rowcount
    if changed != 1:
        raise ValueError("reservation precondition not satisfied")
    db.execute(
        "INSERT INTO tx_requests(request_id, product_id, quantity) "
        "VALUES (?, ?, ?)",
        ("TX-R1", "TX-PEN", 3),
    )
    db.execute("COMMIT")
except BaseException:
    if db.in_transaction:
        db.execute("ROLLBACK")
    raise

1. This is a scoped SQLite teaching pattern for an owned connection and separately created exercise tables. It does not implement idempotent replay; a duplicate request key can reject the insertion and roll back the stock change.
2. Inject an ordinary Python exception after the UPDATE but before the INSERT or COMMIT. After rollback, verify both stock and requests, not just the raised error.
3. This injection tests application-level rollback. It does not simulate device power loss or a lost network acknowledgement.

21.2.4 PLAIN — what is really happening inside#

1. Transaction state can differ after different failures. Some errors abort one statement; others invalidate more work or leave a transaction needing explicit recovery. Read the engine and driver contract.
2. Deferred constraints can reject COMMIT after earlier statements appeared successful. The application must handle that failure rather than return success before commit completes.
3. Once a change is committed, a correction is normally another transaction. Rollback is not a time machine for previously committed business actions.

21.2.5 TECHNICAL — the engineer’s version#

1. SQLite documents that COMMIT can fail, including due to busy conditions, and that transaction state after errors needs deliberate handling. PostgreSQL has its own failed-transaction and savepoint behaviour. Do not generalise one engine’s exception handling to all engines. [S25] [S117]
2. A communication exception during commit can be an indeterminate outcome from the client’s perspective. Use stable request identity and a reconciliation path; Chapter 26 develops stored idempotency outcomes.
3. Cleanup should not disguise the original failure. Production libraries need careful exception handling if rollback itself fails, and must not return a possibly damaged or still-active connection to a pool without a defined recovery policy.

21.2.6 WORDS — remember these#

1. Commit: accept a transaction’s database changes — the completion operation whose acknowledgement has engine- and configuration-specific guarantees. Rollback: abandon uncommitted transactional work — restoration of the transaction’s database effects under its supported semantics. Indeterminate outcome: the caller cannot yet tell whether it completed — uncertainty commonly caused by losing communication around a commit or external effect.

21.3.1 PLAIN — in simple words#

1. Atomicity concerns whether the selected changes take effect together. Consistency concerns the rules that valid states must satisfy.
2. The database enforces the constraints you actually declared and the mechanisms its transaction model provides. It does not infer every intended business rule from a table’s name.
3. A transaction that subtracts four units for a three-unit request can be perfectly atomic and still wrong. Both changes agree only if the application checks the correct relationship.

21.3.2 PLAIN — a picture in your head#

1. An envelope can contain a complete but incorrect invoice. Keeping every page together prevents missing pages; it does not correct the arithmetic.
2. A reviewer still needs rules about quantities, prices and the meaning of the document.
3. Where the comparison breaks: some consistency rules are mechanically enforceable as constraints, while others need application logic or coordination. A human reviewer is not an automatic feature of every transaction.

21.3.3 PLAIN — a worked example#

1. Return to the separate capacity example from Chapter 11: capacity is ten, and two allocations are seven and six. Each allocation is positive, so a positive-value CHECK can accept both while the sum reaches thirteen.
2. Wrapping both INSERTs in one transaction makes their acceptance all-or-nothing. It does not add the missing sum-within-capacity rule.
3. A correct design can represent a guarded available quantity, coordinate through a shared record, or use another explicitly justified invariant strategy. The chosen strategy must also work when requests overlap.
4. The earlier counterexample remains evidence of absent protection in that lab; it is not retroactively repaired by this explanation.

21.3.4 PLAIN — what is really happening inside#

1. A valid-state rule can concern one value, one row, several rows or an external fact. Different enforcement mechanisms cover different scopes.
2. Transactional atomicity helps keep a multi-row rule intact once the application has a correct algorithm and suitable isolation or locking. It does not supply that algorithm on its own.
3. Verification should include both valid and invalid transitions. Tests that only confirm successful insertion never establish that forbidden states are rejected.

21.3.5 TECHNICAL — the engineer’s version#

1. In ACID terminology, atomicity, consistency, isolation and durability describe distinct aspects of transactional behaviour. Consistency depends on declared constraints and correctly implemented invariants; it is not a general truth detector.
2. A transaction can preserve structural constraints while violating a business policy omitted from them. Chapter 11’s cross-row and historical-price counterexamples demonstrate this distinction with concrete accepted states.
3. Prove the isolated transition preserves the invariant, then analyse overlapping transitions under the chosen concurrency model. A proof that assumes serial execution is incomplete when the deployment permits non-serialisable interactions.

21.3.6 WORDS — remember these#

1. Atomicity: the selected changes take effect together — all-or-nothing transactional acceptance of the covered database work. Invariant: a rule every accepted state must preserve — a condition maintained by constraints and operation protocols across permitted transitions. Consistency: accepted states obey the specified rules — validity relative to actual constraints and invariants, not an assurance that every stored claim is true.

21.4.1 PLAIN — in simple words#

1. Isolation controls what overlapping transactions can observe and how their actions interact. Durability concerns what an acknowledged change survives.
2. These properties answer different questions. A change may be protected from partial visibility yet configured with weaker persistence acknowledgements. A durable wrong answer remains wrong.
3. State the failure being discussed: a process crash, an operating-system crash, power loss, a destroyed device or a lost region are not equivalent events.

21.4.2 PLAIN — a picture in your head#

1. Isolation is about several clerks working without seeing an unacceptable mixture of one another’s unfinished edits. Durability is about which official records remain after a failure.
2. A fireproof cabinet does not decide which edits clerks may combine, and a careful editing protocol does not make paper fireproof.
3. Where the comparison breaks: actual guarantees depend on software, configuration, storage and replication protocols. Neither “transactional” nor “saved” specifies all failure domains by itself.

21.4.3 PLAIN — a worked example#

1. A reader totals two fields while another transaction changes them together. The isolation question is whether the reader can observe an unacceptable mixture of before and after values under its snapshot rules.
2. A server acknowledges that the change committed, then its process exits. The durability question is whether a restarted engine recovers the acknowledged state under its configured persistence path.
3. A memory-only teaching database can demonstrate transactional visibility and rollback while losing all data when the process ends. It is not a durable storage deployment merely because COMMIT works.
4. Chapters 23 and 30 examine the two questions separately rather than hiding them behind one ACID label.

21.4.4 PLAIN — what is really happening inside#

1. Isolation can use locks, versions, conflict detection and other coordination. A snapshot is a visibility rule, not necessarily a physical copy of the entire database.
2. Durability usually involves recovery records, ordered persistence requests and assumptions about the operating system and device. Replication can extend the failure boundary only under its actual acknowledgement rules.
3. Testing a Python exception proves less than testing an abrupt process crash, which proves less than a controlled power-loss experiment. Name the experiment actually performed.

21.4.5 TECHNICAL — the engineer’s version#

1. PostgreSQL’s isolation levels and SQLite’s writer/reader behaviour are product-specific contracts. Do not infer a universal anomaly table from a generic level name. [S66] [S54]
2. SQLite’s atomic-commit documentation explicitly discusses filesystem and device assumptions. The educational in-memory examples do not exercise those assumptions. [S53]
3. A durability claim should identify acknowledgement point, configured persistence mode, recovery procedure and covered failure domain. Later replication acknowledgements must be analysed with the same precision.

21.4.6 WORDS — remember these#

1. Isolation: rules for overlapping work — guarantees governing visibility and interaction among concurrent transactions. Durability: acknowledged work survives specified failures — persistence under a named configuration, recovery model and failure boundary. Failure domain: what one failure can affect together — a process, host, device, availability zone or other explicitly bounded component group.

21.5.1 PLAIN — in simple words#

1. A savepoint marks a position inside a transaction. Rolling back to it can discard later transactional changes while retaining earlier uncommitted work.
2. It is not necessarily an independent committed transaction. Releasing an inner savepoint does not make its changes immune to a later rollback of the outer transaction.
3. Savepoints help recover from optional or tentative steps, but they should not be used to silently accept an incomplete business operation whose steps were all required.

21.5.2 PLAIN — a picture in your head#

1. While drafting an order, Mira bookmarks a version before trying an optional annotation. She can return to that bookmark without discarding the whole draft.
2. The draft still is not an accepted order merely because the annotation’s bookmark was removed.
3. Where the comparison breaks: savepoints have precise engine-specific stack and error-recovery semantics. They do not restore external files, user-interface state or every nontransactional counter.

21.5.3 PLAIN — a worked example#

1. In a disposable notes table, begin a transaction and insert a required note A. Create savepoint optional_note, then insert tentative note B.

BEGIN;
INSERT INTO tx_notes(note_id, body) VALUES ('A', 'required');
SAVEPOINT optional_note;
INSERT INTO tx_notes(note_id, body) VALUES ('B', 'tentative');
ROLLBACK TO optional_note;
RELEASE optional_note;
COMMIT;

2. The committed result contains A but not B. The exercise explicitly defines B as optional; this is not permission to drop a failed stock adjustment from a required reservation.
3. Repeat with a full ROLLBACK instead of COMMIT at the end. Then neither A nor B remains from the transaction, even after RELEASE.

21.5.4 PLAIN — what is really happening inside#

1. Savepoints form nested recovery boundaries within the transaction’s supported semantics. Rolling back to one abandons subsequent work and affects later savepoints according to the engine’s rules.
2. A released inner boundary no longer provides that local recovery point, but its changes remain part of the outer transaction until outer completion.
3. SQLite permits an outermost SAVEPOINT to start a transaction, so releasing that outermost boundary can commit. Distinguish this case from releasing an inner savepoint inside an existing transaction. [S127]

21.5.5 TECHNICAL — the engineer’s version#

1. PostgreSQL SAVEPOINT and ROLLBACK TO support partial recovery inside a transaction, including recovery from eligible failed suboperations. The outer transaction remains the final acceptance boundary. [S121]
2. SQLite RELEASE has stack semantics; an inner release is not a durable commit, while release of the outermost transaction savepoint has a different effect. Code must know which boundary it owns. [S127]
3. A savepoint strategy should document which failures are optional, which invalidate the whole business action and which leave the connection unusable. Catching every exception and continuing is not a valid general recovery policy.

21.5.6 WORDS — remember these#

1. Savepoint: a local return point inside transaction work — a named boundary to which later transactional changes can be rolled back. Partial rollback: discard only a selected later portion — rollback to a savepoint rather than abandonment of the entire outer transaction. Outer transaction: the enclosing acceptance boundary — the transaction whose final commit or rollback governs changes retained from inner savepoint work.

21.6.1 PLAIN — in simple words#

1. Sending an email, charging an external payment service, printing a document or handing over goods is not normally undone by rolling back a local database transaction.
2. Performing the external effect before commit can leave an effect with no accepted database record. Performing it after commit can leave accepted work with an effect that was never completed.
3. The solution is an explicit cross-boundary protocol, not a claim that one local transaction magically covers everything. Chapter 26 introduces durable delivery intent; Chapter 38 covers compensation.

21.6.2 PLAIN — a picture in your head#

1. Dev sends a customer a reservation message, then discovers that the database transaction failed. The message is already in the customer’s inbox.
2. Erasing the draft on Dev’s desk does not recall that message.
3. Where the comparison breaks: some external providers support idempotency keys or transactional integration, but those are additional contracts. Their existence must be verified for the exact operation, not assumed from the word API.

21.6.3 PLAIN — a worked example#

1. Schedule A: update stock; send confirmation; request insertion fails; roll back. The customer saw a confirmation for a reservation that did not commit.
2. Schedule B: update stock and request; commit; process crashes before sending. The reservation exists but no confirmation was sent.
3. An outbox stores the intention to send alongside the business changes in one database transaction. A later worker retries delivery from that committed intention.
4. This closes the local “committed work with no recorded intent” gap. It does not by itself prevent duplicate external delivery when a worker sends successfully and crashes before recording acknowledgement. [S125]

21.6.4 PLAIN — what is really happening inside#

1. Each independent system has its own acceptance point. A local database cannot roll back a remote side effect merely because the application uses a try/except block.
2. Stable operation identity helps reconcile uncertain outcomes. A correction or compensation is a new action with its own evidence, not a claim that the first action never happened.
3. Even within PostgreSQL, sequence allocation has special non-rollback behaviour. Gaps in generated values therefore do not prove that a business record was deleted or lost. [S122]

21.6.5 TECHNICAL — the engineer’s version#

1. Distinguish local transactional atomicity from distributed atomic commit and from a saga of compensating actions. They provide different failure semantics.
2. An outbox atomically binds business state to durable delivery intent when both writes share the same effective database transaction. Delivery, ordering and consumer deduplication remain separate obligations. [S125]
3. The educational tests inject failures at named points and inspect database state. They do not send live messages, perform payments or prove end-to-end exactly-once effects.

21.6.6 WORDS — remember these#

1. External effect: something changes beyond the local transaction — an action in another system or the physical world not automatically reversed by database rollback. Outbox: committed work carries a delivery instruction — a transactional record of an event or message to be delivered by a separate process. Compensation: a new action addresses an earlier effect — an explicit corrective operation, not erasure of the original history.