Transactions and Consistency: Protecting Multi-Step Data Changes

Many meaningful data changes require more than one statement.

A bank transfer subtracts money from one account and adds it to another. A purchase creates an order, records line items, and reserves inventory. If execution stops halfway, the database may contain a state that never made business sense.

A transaction groups operations into a unit with defined success, failure, and visibility guarantees.

Transactions make it possible to protect rules across multiple changes.

A concrete example: transferring money

A simplified transfer might execute:

BEGIN;
 
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 10;
 
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 20;
 
COMMIT;

If the second update fails, the application can ROLLBACK. The first uncommitted change is discarded.

The transaction boundary says that the transfer is one meaningful operation, not two unrelated balance edits.

Atomicity means all or nothing

Atomicity says the transaction's changes commit together or do not commit.

It does not mean the operations happen instantaneously or cannot be observed internally by the transaction itself. It means other work does not receive a permanently half-finished result.

Atomicity depends on the systems inside the boundary. A database transaction cannot automatically undo an email already sent or a payment already accepted by an external service.

Coordinating those external effects requires patterns such as outboxes, idempotency, or compensating actions.

Consistency protects defined rules

In ACID, consistency means a transaction takes the database from one valid state to another, according to enforced rules.

Rules may include:

• Primary-key uniqueness
• Valid foreign-key relationships
• Required values
• Check constraints
• Application invariants

The database can enforce balance >= 0 if that rule is declared as a constraint. It cannot infer that a premium customer should receive a particular discount unless software or schema logic defines it.

Transactions provide a mechanism. Developers still need to state the correct rules.

Isolation controls concurrent visibility

Transactions often run simultaneously. Isolation defines how their intermediate work can affect one another.

Without sufficient isolation, anomalies can occur:

• Dirty read: Reading another transaction's uncommitted change
• Non-repeatable read: Reading a row twice and seeing different committed values
• Phantom: Repeating a range query and seeing added or removed matching rows
• Lost update: One write unintentionally overwrites another

Database isolation levels trade stronger protection for concurrency and performance.

The names and exact guarantees vary by database, so application teams must read the behavior of their actual system.

Durability means committed changes survive

After a transaction commits, durability means its result should survive expected failures such as a process restart.

Databases commonly write changes to a durable transaction log before confirming success. They can replay that log during recovery.

Durability has operational boundaries. Hardware failure, storage corruption, region loss, or accidental deletion still require replication and backups.

A commit acknowledgment is a promise defined by configuration and architecture, not magic immunity from every disaster.

ACID is a set of guarantees

The familiar acronym is:

• Atomicity: The transaction commits as a unit.
• Consistency: Defined rules remain true.
• Isolation: Concurrent operations interact according to a chosen model.
• Durability: Committed data survives expected failures.

These properties are related, but they answer different questions.

Saying a database is "ACID compliant" is not enough to understand an application. Ask which operations use transactions, what isolation is configured, what constraints exist, and what failure boundary durability covers.

Read-modify-write can create races

Suppose application code:

1. Reads stock quantity 1.
2. Checks that it is positive.
3. Writes quantity 0.

Two purchases can both read 1 before either writes. Both pass the check and both succeed, overselling the item.

One solution performs the condition and change atomically:

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 7
  AND quantity > 0;

The application then checks whether one row was updated.

Transactions alone do not fix every race. The statements and isolation strategy must protect the invariant.

Locks and multiple versions

Databases coordinate concurrent work using techniques such as:

• Row or table locks
• Multiple versions of records
• Optimistic conflict detection
• Serializable execution checks

Locks can block conflicting operations. Multi-version concurrency control allows readers to see a consistent snapshot while writers create newer versions.

Each technique has costs. Long transactions can hold locks, retain old versions, increase contention, and delay cleanup.

Keep transactions focused and avoid waiting for human input or slow network calls inside them.

Deadlocks can occur

A deadlock can happen when:

• Transaction A holds row 1 and waits for row 2.
• Transaction B holds row 2 and waits for row 1.

Neither can continue.

Databases detect this cycle and abort one transaction. Applications must be prepared to retry the aborted operation.

Acquiring resources in a consistent order reduces deadlock probability, but cannot always eliminate it in complex systems.

Retries should be bounded and should repeat an operation that is safe to run again.

Savepoints allow partial rollback

A transaction can create a savepoint:

SAVEPOINT before_optional_step;

If an optional operation fails, the transaction may roll back to that point rather than discard everything.

Savepoints are useful for controlled sub-operations, but too many nested recovery paths can make logic difficult to reason about. The overall transaction should still represent a coherent business action.

Transactions should match business boundaries

Do not make every request one enormous transaction by default.

A good boundary contains changes that must remain consistent together. For example:

• Creating an invoice and its line items
• Reserving one inventory unit
• Recording an account ledger entry and updating its balance projection

A workflow spanning hours, people, or external services cannot remain one database transaction. It needs durable state transitions and recovery logic.

Testing transactional behavior

Normal unit tests may miss concurrency bugs.

Test:

• Failure after each important step
• Two operations racing on the same records
• Deadlock or retry behavior
• Constraint violations
• Process restart after commit acknowledgment
• Idempotent handling of repeated requests

Observability should include transaction failures, lock waits, deadlocks, and retry counts so operational contention is visible.

Knowledge check

1. What does atomicity protect in a transfer?
2. Why does database consistency still depend on declared rules?
3. Give one example of a concurrency anomaly.
4. Why should transactions remain short?
5. Why can a database transaction not automatically undo a sent email?

The one idea to remember

A transaction protects one meaningful state change with defined all-or-nothing, consistency, isolation, and durability guarantees. Those guarantees work only when the boundary and business invariants are designed explicitly.