What are ACID properties in database transactions and how do they ensure reliability?
ACID is a set of four guarantees that database engines implement to ensure transactions execute reliably:
• Atomicity: 'All or nothing'. If any statement in a transaction fails, all previously executed modifications are rolled back. • Consistency: A transaction transforms the database from one valid state to another, strictly honoring all constraints, foreign keys, and triggers. • Isolation: Concurrent transactions execute without interfering with one another or seeing intermediate, uncommitted states. • Durability: Once a transaction commits, its changes survive system crashes and power outages, typically via Write-Ahead Logging (WAL).
BEGIN TRANSACTION;
-- Debit from account A
UPDATE Accounts
SET balance = balance - 500.00
WHERE account_id = 101 AND balance >= 500.00;
-- Credit to account B
UPDATE Accounts
SET balance = balance + 500.00
WHERE account_id = 202;
-- Check balance integrity before persisting
IF @@ROWCOUNT = 1
COMMIT TRANSACTION;
ELSE
ROLLBACK TRANSACTION;If money is deducted from Account 101 but the credit to Account 202 fails, the entire transaction is rolled back, guaranteeing that money is never lost or created out of thin air.
Ready for Views?
Take a quick test to evaluate yourself, or move straight to the next module.