ACID Properties in DBMS and MySQL

What Does ACID Mean?

ACID is a reliability model for database transactions: Atomicity, Consistency, Isolation and Durability. It explains how a database should behave when a statement fails, two users update data at the same time, the application disconnects or the server crashes.

ACID is not a magic label on SQL. Reliable behavior comes from a transactional engine such as InnoDB, correct schema constraints, a complete application transaction, suitable isolation and durable server configuration.

The Four ACID Properties at a Glance

A memorable sequence is: all-or-nothing, valid-to-valid, concurrent-but-controlled, committed-and-recoverable.

Verified Bank-Transfer Case Study

-- Opening balances
101 Asha  = 5000.00
102 Ravi  = 3000.00
103 Meera = 1200.00
Total     = 9200.00

START TRANSACTION;
SELECT account_id, balance
FROM bank_accounts
WHERE account_id IN (101, 102)
ORDER BY account_id
FOR UPDATE;

UPDATE bank_accounts
SET balance = balance - 1000.00
WHERE account_id = 101 AND balance >= 1000.00;

UPDATE bank_accounts
SET balance = balance + 1000.00
WHERE account_id = 102;

INSERT INTO transfer_ledger
VALUES (9001, 101, 102, 1000.00, 'COMMITTED', CURRENT_TIMESTAMP);

COMMIT;

The example becomes ACID only when the application checks every affected-row count and rolls back on any failure. Read the full safe transfer workflow for that decision logic.

A — Atomicity: All or Nothing

Atomicity treats the transaction as one indivisible logical unit. If the credit fails after the debit, ROLLBACK must remove the uncommitted debit as well. InnoDB uses transaction and undo information so it can reverse changes that are not committed.

START TRANSACTION;
UPDATE bank_accounts SET balance = balance - 500
WHERE account_id = 101;
-- Suppose the next required operation fails.
ROLLBACK;

-- Account 101 returns to its pre-transaction value.

• Atomicity is not: every business task written as one enormous SQL statement.
• Atomicity requires: correct boundaries and a full rollback path.
• Important limitation: nontransactional table changes cannot be fully rolled back.
• DDL caution: many schema statements cause an implicit commit and should stay outside business DML transactions.

C — Consistency: Valid State to Valid State

Consistency means a successful transaction respects the database's integrity rules and the application's business invariants. The DBMS can enforce primary keys, foreign keys, unique rules, NOT NULL and CHECK constraints. The application must still enforce rules that were never declared or that span a more complex process.

balance DECIMAL(12,2) NOT NULL
  CHECK (balance >= 0)

amount DECIMAL(12,2) NOT NULL
  CHECK (amount > 0)

FOREIGN KEY (from_account)
  REFERENCES bank_accounts(account_id)

Common misconception: ACID does not make incorrect business logic correct. A perfectly atomic transaction can consistently commit the wrong amount if the program supplied the wrong rule.

I — Isolation: Controlled Concurrency

Isolation controls what one transaction can observe while other transactions are active. InnoDB combines multi-version concurrency control, isolation levels and locks. MySQL InnoDB supports READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE; REPEATABLE READ is the default InnoDB level.

START TRANSACTION;
SELECT account_id, balance
FROM bank_accounts
WHERE account_id IN (101, 102)
ORDER BY account_id
FOR UPDATE;
-- Matching records stay locked for conflicting changes
-- until COMMIT or ROLLBACK.

Isolation does not mean “no concurrency.” It means concurrent effects follow defined rules. Select the isolation level for the workload and use locking reads when a value is read specifically to make a later update decision.

D — Durability: Committed Data Survives Recovery

After COMMIT succeeds, durability means the committed result is recoverable even if the server later fails. InnoDB's redo log, doublewrite mechanism and crash recovery contribute to this property. Binary-log and storage configuration also matter for replicated or point-in-time recovery needs.

• innodb_flush_log_at_trx_commit affects redo-log flush behavior.
• sync_binlog affects binary-log synchronization behavior.
• Storage write caches, operating-system guarantees and power protection influence failure behavior.
• Backups and tested restores protect against deletion, corruption, operational mistakes and disaster beyond one transaction.

Exam Answer, Misconceptions and Practice

Five-mark model answer: ACID properties make DBMS transactions reliable. Atomicity makes the transaction all-or-nothing. Consistency moves the database between states that satisfy integrity rules. Isolation controls interference among concurrent transactions according to an isolation level. Durability preserves committed results through crash recovery. In MySQL, InnoDB supplies transactional mechanisms such as locking, MVCC, undo, redo and recovery, while the schema and application must define correct constraints and transaction boundaries.

Practice: Map ACID to school fee payment, online order placement and seat booking. For each, identify one invariant, one concurrent race, one rollback point and one durability requirement.

Official References

• MySQL 8.4: InnoDB and the ACID Model
• MySQL 8.4: InnoDB Transaction Isolation Levels
• MySQL 8.4: The InnoDB Storage Engine

Definitions and MySQL implementation notes were checked against the official MySQL 8.4 manual.