Atomicity vs Consistency in ACID

1. Atomicity

Atomicity means:

A transaction is treated as one indivisible unit: either all of it happens, or none of it happens.

The database must never leave a transaction half-applied.

Main idea

If a transaction contains 5 steps, and step 4 fails, then steps 1, 2, and 3 must also be undone.
The final outcome is only one of these:

• complete success
• complete rollback

There is no valid third state like “partially done”.

What Atomicity protects against

Atomicity protects against:

• partial updates
• interrupted execution
• crash in the middle of a transaction
• one statement succeeds, another fails, leaving incomplete work

Example

Suppose a money transfer:

1. Subtract 100 from account A
2. Add 100 to account B

If the system crashes after step 1 but before step 2:

• without atomicity, A loses 100 and B does not receive it
• with atomicity, the whole transaction rolls back, so neither side is applied

Important point

Atomicity says nothing about whether the transaction logic is correct business logic.
It only says the logic is applied fully or not at all.

So this transaction can be atomic and still be wrong:

If both statements complete and commit, atomicity is satisfied.
But business correctness may still be broken.

2. Consistency

Consistency means:

A transaction must take the database from one valid state to another valid state.

After the transaction finishes, all defined rules must still hold.

Main idea

Consistency is about preserving the truth of the database according to its rules.

Those rules may include:

• primary keys
• foreign keys
• unique constraints
• check constraints
• not null rules
• triggers
• generated logic
• application/business rules
• referential integrity
• domain rules such as “balance cannot go below zero”

What Consistency protects

Consistency protects the integrity of the data model.

If a transaction would violate a rule, the database must reject it or roll it back.

Example

Suppose the rule is:

• every employee must belong to an existing department

Then this is inconsistent:

If department 999 does not exist, consistency is violated.
A database with proper foreign key enforcement will reject it.

Another example

Suppose the rule is:

• account balance must never be negative

Then this transaction is inconsistent if it makes balance < 0:

If account 1 had only 300, and your rules say negative balances are forbidden, then allowing this would break consistency.

Important point

Consistency is about whether the resulting state is valid.
It is not about “all-or-nothing”; that is atomicity.

The core difference

Atomicity asks:

Was the transaction fully applied or not applied at all?

Consistency asks:

Is the resulting database state valid according to all rules?

That is the cleanest way to separate them.

Very simple comparison

Atomicity

• Focus: completion behavior
• Concern: partial execution
• Question: “Did everything happen, or did nothing happen?”
• Protects against: half-finished transactions

Consistency

• Focus: validity of data
• Concern: broken rules
• Question: “Is the database still correct after the transaction?”
• Protects against: invalid states

Why they are often confused

Because in practice, when a transaction fails, the database often rolls it back.
That rollback makes it look like atomicity and consistency are the same thing.

But they are not.

What really happens

• A transaction attempts some changes
• If those changes would violate integrity rules, the DB rejects them
• The failed transaction is rolled back

Here:

• Consistency is the reason the final invalid state is not allowed
• Atomicity is the reason partial changes are not left behind

So they cooperate, but they are different.

Full conceptual distinction

Atomicity is about the transaction as a unit

Think of the transaction as a package.

Atomicity says:

• the package is either delivered completely
• or not delivered at all

It does not care whether what is inside the package is logically sensible.

Consistency is about the database state

Think of the database as a system that must obey rules.

Consistency says:

• before transaction: valid state
• after transaction: valid state again

It does not mainly describe crash recovery or all-or-nothing behavior.
It describes preservation of integrity.

Example where Atomicity matters more

Suppose a transfer transaction has 3 steps:

1. Insert row in transfer log
2. Debit source account
3. Credit target account

If step 3 fails and steps 1–2 remain committed, the system is partially updated.

That is an atomicity failure.

Even if the tables still satisfy constraints, the transfer is incomplete.
So the main broken property here is atomicity.

Example where Consistency matters more

Suppose this transaction runs fully:

Assume your business rule says balances cannot be negative.

This transaction may be perfectly atomic:

• it ran fully
• it committed fully
• nothing partial happened

But it is inconsistent, because the final data violates the rule.

So atomicity can be satisfied while consistency is violated.

Can consistency be “programmer-defined”?

Mostly, yes.

That is an important insight.

The database engine does not invent your business truth by itself.
It enforces the rules that are defined through:

• schema constraints
• triggers
• transaction logic
• application validations
• procedures
• database design

So consistency depends on the set of rules you define.

But not only business logic

Some consistency rules are structural and built into the schema:

• PK uniqueness
• FK integrity
• CHECK conditions
• NOT NULL

So consistency is not “just philosophy”; it becomes concrete through enforceable rules.

Another key distinction

Atomicity is guaranteed mainly by transaction machinery

Usually through:

• undo/rollback segments
• transaction logs
• recovery mechanisms
• commit/rollback protocol

Consistency is guaranteed mainly by valid rules + correct enforcement

Usually through:

• constraints
• triggers
• business logic
• transaction design

So:

• Atomicity is more about the database transaction mechanism
• Consistency is more about the correctness of the data model and rules