sqlportgremysqlaciddatabasetransaction

Understand ACID properties

This is about database ACID properties

May 20, 20262 min read24 views

ACID Properties

ACID
means 4 rules that make database transactions safe and reliable.
A transaction means a group of database operations that should happen together.
Example:
Money transfer:
  • Remove money from Account A
  • Add money to Account B
Both must happen, or none should happen.

A — Atomicity

Atomicity means all operations in a transaction happen fully, or nothing happens.

Example

sql
BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE id = 2;

COMMIT;
If the second update fails, the first update should also be cancelled.

Simple Meaning

All or nothing.

C — Consistency

Consistency means the database must remain valid before and after the transaction.
Rules like:
  • foreign keys
  • unique constraints
  • check constraints
  • not null constraints
must not be broken.

Example

If balance cannot be negative, this should not be allowed:
sql
UPDATE accounts
SET balance = -100
WHERE id = 1;

Simple Meaning

Data rules must stay correct.

I — Isolation

Isolation means multiple transactions should not badly affect each other.
If two users update data at the same time, the database should handle it safely.

Example

Two users try to buy the last product at the same time.
The database should not allow both to buy if only one item is available.

Simple Meaning

Transactions should not interfere with each other incorrectly.

D — Durability

Durability means once a transaction is committed, the data is saved permanently.
Even if the server crashes after commit, the data should remain.

Example

sql
COMMIT;
After 
COMMIT
, the change should not disappear.

Simple Meaning

Committed data is safely saved.

Easy Memory Trick

LetterMeaningSimple Idea
AAtomicityAll or nothing
CConsistencyRules stay valid
IIsolationTransactions do not break each other
DDurabilitySaved after commit

Full Example

Bank transfer:
sql
BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE id = 2;

COMMIT;
ACID means:
  • Both updates happen together
  • Balance rules stay valid
  • Other transactions do not create wrong results
  • After commit, the transfer is saved

Best Practice

Use transactions when multiple queries depend on each other.
Good examples:
  • money transfer
  • order creation
  • payment update
  • inventory update
  • user signup with profile creation

Found this useful? Have thoughts or questions?

Reach out →
All posts