Making DB Changes Predictable with Transactions

Source: Dev.to

Introduction

Hello, I’m Maneshwar. I’m working on FreeDevTools online currently building one place for all dev tools, cheat codes, and TLDRs — a free, open‑source hub where developers can quickly find and use tools without any hassle of searching all over the internet.

In the previous post, we saw how indexes define efficient access paths and how the DBMS sits between applications and storage, shielding applications from files, disks, and failures.

But there is a deeper problem that indexes and schemas alone cannot solve.

Databases are not static. They represent real‑world systems that evolve over time.

Orders are placed, payments are processed, students enroll, balances change. These changes must be reflected in the database accurately, even when:

• Multiple users act at the same time
• Applications fail
• Systems crash
• Power is lost mid‑operation

This is where the concept of a transaction becomes unavoidable.

Why We Need Transactions

Users see data at the conceptual level—relations, attributes, and constraints. Internally, the DBMS stores data in files and blocks, and maintains indexes and metadata.

Applications modify the database to mirror real‑world events. These modifications often involve multiple related data items.

Example

• Deduct money from one account
• Add it to another
• Record the transfer

These updates must be mutually consistent.

Two Major Dangers During Updates

1. Temporary inconsistency

   • While an application is midway through its logic, the database may be in an intermediate state.
   • If other applications see this state, they may behave incorrectly, even if they are perfectly written.

2. Failures during updates

   • An application may crash.
   • The system may reboot.
   • Power may fail.
   • The database may be left in a partially updated, inconsistent state.

A database that is not being accessed is assumed to be consistent.

• Read operations preserve consistency.
• Write operations temporarily break it.

The DBMS must ensure that these temporary inconsistencies:

• Are never visible to others
• Never become permanent due to failures

Transactions as Units of Consistency

To solve this, database operations from an application are grouped together. This group is called a transaction.

A transaction is a logical unit of work:

• Composed of multiple lower‑level database operations
• Intended to transform the database from one consistent state to another

When executed in isolation, a transaction preserves database consistency. While a transaction is active, the DBMS assumes:

“The database might be inconsistent right now.”

So the DBMS takes precautionary measures until the transaction finishes.

What Exactly Is a Transaction?

A transaction is a sequence of actions on data items. The DBMS’s responsibility is to make this sequence appear:

• Indivisible
• Instantaneous

to the outside world.

From the perspective of other transactions:

• Either the entire transaction has happened, or
• None of it has happened

There is no visible middle state. This idea is the heart of modern database systems. Almost every serious database abstraction—SQL, isolation levels, recovery logs—exists to support transactions.

Key points

• Initiated by applications
• Defined by application logic
• Assumed by the DBMS to be units of consistency
• Represent a real‑world event (which either happens or does not)

Commit, Abort, and Completion

Each DBMS provides a transaction interface that allows applications to:

1. Start a transaction

2. Execute database operations

3. Complete the transaction in one of two ways:

   • Commit
   • Abort

Commit

When an application commits a transaction, it tells the DBMS:

“All changes made by this transaction must become permanent.”

Once committed, the effects of the transaction must survive:

• Crashes
• Restarts
• Power failures

Abort

When a transaction aborts:

• All its effects must be removed
• The database must be restored to the state it had at transaction start

Aborts can happen:

• Explicitly, initiated by the application
• Implicitly, forced by the DBMS (due to errors or constraint violations)

In both cases, once a transaction commits or aborts, it is considered complete. Applications can only interact with the database through transactions.

The ACID Properties

Ensuring correctness in the presence of concurrency and failures is hard. To make this manageable, DBMSs guarantee a set of properties collectively known as ACID:

Atomicity

All operations of a transaction either:

• All happen, or
• None happen

There is no partial effect once the transaction is complete. Failed or aborted transactions leave no trace in the database.

Consistency

If a transaction starts with a consistent database state and runs in isolation, it must end in a consistent state.

Consistency concerns:

• Integrity constraints
• Correct application logic
• Valid state transitions

The DBMS enforces constraints, but the meaning of consistency comes from the application and data model.

Isolation

Even though transactions may execute concurrently, the final outcome must be as if they executed serially.

From each transaction’s point of view:

• It runs alone
• Its operations appear to happen instantaneously

Isolation protects transactions from seeing:

• Temporary inconsistencies
• Partial effects of other transactions

Durability

Once a transaction commits:

• Its effects must persist
• Even if the system crashes immediately afterward

Durability ties transactions back to everything we learned about disks, logs, and persistence.

What the DBMS Guarantees and What It Doesn’t

The DBMS guarantees:

• Atomicity, consistency, isolation, and durability of committed transactions
• Recovery from crashes using logs and checkpoints
• Concurrency control to prevent interference between concurrent transactions

The DBMS does not guarantee:

• Correct application logic (the application must enforce business rules)
• That developers write transactions correctly (e.g., avoiding deadlocks)
• Performance characteristics beyond the guarantees above (latency, throughput)

ACID Guarantees

• Atomicity
• Isolation
• Durability
• Enforcement of integrity constraints

The DBMS does not:

• Understand application semantics
• Decide transaction boundaries
• Reorder operations inside a transaction

Applications Define

• What a transaction does
• Which operations it contains
• When to commit or abort

The DBMS respects the order of operations as issued.

Where This Leaves Us

At this point, we have climbed the full abstraction ladder:

• Disks explain persistence and failure
• Relations define structure
• Indexes define access paths
• The DBMS coordinates storage and access
• Transactions define correctness over time

What remains is how the DBMS actually enforces ACID in practice.

That leads directly to:

• Transaction management
• Concurrency control
• Failure recovery
• Checkpointing

These mechanisms are where theory meets hard‑systems engineering, and that’s exactly where the next post begins.

👉 Check out: FreeDevTools

Any feedback or contributions are welcome!
It’s online, open‑source, and ready for anyone to use.

⭐ Star it on GitHub: freedevtools