ACID properties in MySQL

Introduction

ACID properties help keep data safe and correct when many people use a database at the same time.

When you want to make sure money transfers between bank accounts are done correctly.

When multiple users update the same data and you want to avoid mistakes.

When you need to keep data accurate even if the system crashes.

When you want to make sure all parts of a task finish completely or not at all.

When you want to avoid seeing half-finished changes in your data.

Syntax

MySQL

ACID stands for:
- Atomicity
- Consistency
- Isolation
- Durability

Atomicity means all parts of a task happen or none do.

Consistency means data rules are always followed.

Examples

This starts a transaction to group commands so they all succeed or fail together.

MySQL

START TRANSACTION;
-- perform multiple SQL commands
COMMIT;

This cancels all changes made in the current transaction.

MySQL

ROLLBACK;

Sample Program

This moves 100 units from account 1 to account 2 safely. If any step fails, no money moves.

MySQL

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Important Notes

Isolation prevents other users from seeing partial changes during a transaction.

Durability means once changes are saved, they stay even if power goes out.

Summary

ACID ensures database tasks are safe and reliable.

Use transactions to group related changes.

ACID helps avoid errors when many users work together.