ACID Properties

A transaction in MySQL must follow the ACID principles to ensure data correctness and reliability:

1. Atomicity → All or nothing
2. Consistency → Preserve valid state of data
3. Isolation → Transactions do not interfere with each other
4. Durability → Committed changes are permanent

Atomicity

A transaction is atomic, meaning all the SQL operations inside it succeed as one unit, or none do.

If any step fails → the entire transaction is rolled back.

START TRANSACTION;

-- Deduct from Alice
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;

-- Add to Bob
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;

-- If both succeed, commit
COMMIT;

• If both updates succeed → changes are saved.
• If one fails (e.g., system crash after deduction) → transaction rolls back, leaving balances unchanged.

Consistency

A transaction must preserve the rules and constraints of the database. After it finishes, the database should always be in a valid state.

Suppose Alice has 5000 BDT. If she tries to transfer 6000 BDT, that would break the rule that balance >= 0.

START TRANSACTION;

UPDATE accounts SET balance = balance - 6000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 6000 WHERE account_id = 2;

-- Check constraint
SELECT balance FROM accounts WHERE account_id = 1;

-- If balance < 0 → rollback
ROLLBACK;

Database remains consistent → Alice’s balance cannot go negative.

Isolation

Multiple transactions running at the same time should not interfere with each other.

Each transaction behaves as if it is executed alone, even though MySQL may run them concurrently.

Example:

Two users try to withdraw from Alice’s account at the same time:

• Transaction A → Withdraw 2000
• Transaction B → Withdraw 4000

If not isolated properly, both might read the same starting balance (5000) and withdraw simultaneously → result could be -1000 ❌.

But with isolation, MySQL ensures correct sequence:

• If Transaction A commits first → balance = 3000
• Then Transaction B runs → withdrawal fails because 3000 < 4000

Final balance remains valid.

Durability

Once a transaction is committed, the changes are permanent, even if the system crashes immediately after.

MySQL (with InnoDB) guarantees this by writing committed changes to disk using redo logs.

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
COMMIT;

Even if the server crashes right after COMMIT, Alice’s new balance will be safely stored when MySQL restarts.

Summary of ACID with Example

Property	Meaning	Banking Example
Atomicity	All operations succeed or none	Deduct from Alice & Add to Bob must happen together
Consistency	Database must remain valid	Balance cannot go negative
Isolation	Transactions don’t interfere	Two withdrawals won’t corrupt balance
Durability	Once committed → permanent	After COMMIT, crash won’t erase transfer