4.1Transactions and ACID properties

For an application developer designing a bank transfer or an e-commerce order-confirmation process, deciding "where a transaction should start and end"—the transaction boundary—is a critical judgment that determines the integrity of business data. A boundary drawn too narrowly can leave only part of an update applied, causing inconsistency; one drawn too broadly extends lock-holding time and makes other processing wait. This section builds the judgment needed to decide how to draw a transaction boundary, grounded in the ACID design principles.

4.1.1The four ACID properties

• Atomicity means the sequence of updates in a transaction is either applied in full or not applied at all. It is finalized with COMMIT, and if an abnormality occurs partway through, ROLLBACK undoes all the changes. Atomicity is what prevents a half-finished state such as "the withdrawal succeeded but the deposit failed" in a transfer.
• Consistency means the database continues to satisfy its defined integrity constraints (primary keys, foreign keys, check constraints, etc.) both before and after a transaction executes. Isolation means that multiple concurrently executing transactions cannot see each other's intermediate states, guaranteeing a result as if the transactions had run one at a time in some order (detailed in the next two sections). Durability means that once a transaction has completed COMMIT, its changes remain reliably on non-volatile storage such as disk even if a failure occurs immediately afterward.

4.1.2Transaction boundaries and COMMIT/ROLLBACK

• A transaction boundary is the span of processing from BEGIN (starting a transaction) through COMMIT or ROLLBACK. Because updates within the boundary are treated as a single unit under atomicity, the basic principle is to make the boundary match one business-meaningful unit of work—for a transfer, for instance, both the withdrawal and the deposit must be included in the same transaction, or an inconsistency in which only one side is applied cannot be prevented.
• Drawing the boundary wider than necessary holds locks for longer (detailed in the next section), extending how long other transactions must wait, so narrowing the boundary to the minimum scope needed to preserve integrity is the key to balancing consistency against performance. Conversely, splitting the boundary into too many separate transactions means consistency is no longer guaranteed across them, failing to meet the business requirement.

Suppose an e-commerce developer is designing an order-confirmation process consisting of three updates: creating the order record, decrementing inventory, and adding loyalty points. If each update were committed as a separate transaction, the order record could be created while the inventory decrement failed for some reason, leaving inventory out of sync with reality. So the developer bundles all three updates into a single transaction boundary, designing it so that if an error occurs partway through, ROLLBACK undoes all three. This guarantees atomicity: the order ends up either fully placed or not placed at all. A common mistake to avoid here is the reasoning that "since keeping a transaction open longer feels safer, slow external I/O—such as sending a confirmation email or calling an external payment service after the order is confirmed—should also be included in the same transaction." In fact, calls to external services have unpredictable failure rates and latency, and including them in the transaction boundary unnecessarily extends lock-holding time, making other order processing wait; the appropriate design decision is to keep external I/O outside the transaction and include in the boundary only the updates that require internal data integrity.

4.1.3Section summary

• ACID = atomicity (all-or-nothing), consistency (integrity constraints hold), isolation (intermediate states hidden), durability (survives after COMMIT)
• Draw the transaction boundary around the minimum business-meaningful unit and keep external I/O outside it
• A boundary that is too wide extends lock-holding time and makes other processing wait—balancing this against performance is the core design judgment