DEV Community

Cover image for ACID Properties
Poorvika N
Poorvika N

Posted on

ACID Properties

Atomicity in Database Transactions

Atomicity is the “all or nothing” property of a database transaction.

  • It ensures that a transaction — which may include multiple SQL statements — is treated as a single indivisible unit of work.
  • That means either all operations of the transaction succeed, or none of them do.
  • If any part of the transaction fails (due to error, power loss, or system crash), the database rolls back to its previous stable state — ensuring that no partial updates occur.

1. Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT). Insert 3 sample rows.

2. Atomicity: Start a transaction that transfers money. Rollback midway → ensure no partial update remains.

Atomicity ensures all operations in a transaction succeed or none do.

Example: Transfer ₹1000 from Ravi → Priya and rollback midway

Result: No partial transfer — both balances remain same (proving Atomicity).

3. Consistency: Try inserting a record with negative balance → should be rejected.

Consistency ensures that data integrity rules (constraints) are not violated.

Example: Try inserting a record with negative balance

This will fail because of the CHECK (balance >= 0) constraint.
Result: Database remains in a consistent state.

4. Isolation: Run two sessions at once – one updating, the other reading → observe isolation.

Isolation ensures that concurrent transactions do not interfere with each other.

Depending on isolation level:

  • READ UNCOMMITTED → Session 2 might see uncommitted data (dirty read)
  • READ COMMITTED (default) → Session 2 sees only committed data
  • REPEATABLE READ / SERIALIZABLE → Session 2 waits or reads the old value until Session 1 commits Result: Each session’s visibility depends on the isolation level — proving isolation behavior.

5. Durability: Commit a transaction → restart DB → ensure data persists.

Durability ensures that once a transaction is committed, changes are permanent, even after a crash or restart.

Result: The updated balance remains — showing durability.

Top comments (0)