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)