144 ) What are ACID Properties?

 

What are ACID Properties?

ACID is a set of four key properties that guarantee reliable and predictable transaction processing in relational database management systems (RDBMS). A transaction is a single unit of work comprising one or more SQL operations (inserts, updates, deletes).

• Atomicity: The "all-or-nothing" rule. Either every operation within the transaction succeeds, or the entire transaction is rolled back. No partial updates are permitted.

• Consistency: Ensures that a transaction brings the database from one valid state to another, strictly adhering to all defined rules, constraints, cascades, and foreign keys.

• Isolation: Ensures that concurrent transactions execute independently without interfering with one another, preventing dirty reads, non-repeatable reads, and phantom reads.

• Durability: Once a transaction is committed, its changes are permanent and survive system crashes, power failures, or server reboots.

Examples of Each ACID Property

1. Atomicity Example (Bank Transfer):

   • Scenario: Transferring $100 from Account A to Account B requires two operations: subtract $100 from A and add $100 to B.

   • Failure: If the system crashes after step 1, atomicity ensures the $100 is not lost; the database rolls back the deduction from Account A.

2. Consistency Example (Inventory Balance):

   • Scenario: A database constraint dictates that inventory stock cannot drop below zero (stock >= 0).

   • Failure: If a transaction attempts to purchase 10 items when only 5 are in stock, consistency prevents the transaction from committing and violating the rule.

3. Isolation Example (Concurrent Balance Checks):

   • Scenario: User A reads their bank balance while a background process (User B) is depositing funds.

   • Control: Isolation ensures User A doesn't see a "half-written" or dirty state during the simultaneous execution.

4. Durability Example (Order Confirmation):

   • Scenario: A user completes an online checkout, and the application returns a "Success" message.

   • Guarantee: Even if the database server instantly loses power right after the commit, the order record is safely written to non-volatile disk storage (Write-Ahead Logging).

How to Confirm and Test via SQL Commands

You can verify and test ACID behavior using standard transactional control statements (BEGIN / START TRANSACTION, COMMIT, ROLLBACK, and isolation-level configuration commands).

1. Testing Atomicity (COMMIT vs. ROLLBACK)

This command confirms that failed operations can be entirely undone, leaving zero trace in the database.

SQL

-- Start a transaction block
BEGIN TRANSACTION;

-- Step 1: Deduct funds
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Step 2: Simulate an error or bad condition (e.g., target account doesn't exist)
UPDATE accounts SET balance = balance + 500 WHERE account_id = 99999; 

-- If something goes wrong, you explicitly roll back
ROLLBACK;

-- Confirmation: Account 1's balance remains completely unchanged
SELECT balance FROM accounts WHERE account_id = 1;

2. Testing Consistency (Constraint Enforcement)

This confirms that invalid data states are automatically rejected by the database engine.

SQL

-- Attempt to insert a record that violates a CHECK constraint (e.g., age must be >= 18)
BEGIN TRANSACTION;

INSERT INTO users (username, age) VALUES ('john_doe', 15);
-- Error: Check constraint violated!

COMMIT; 
-- Confirmation: The database automatically aborted or will reject this, 
-- and the row does not exist in the table.
SELECT * FROM users WHERE username = 'john_doe';

3. Testing Isolation (Session Isolation Levels)

You can set and verify the isolation level to control how transactions interact concurrently.

SQL

-- Check the current isolation level (PostgreSQL example)
SHOW TRANSACTION ISOLATION LEVEL;

-- Set transaction isolation level to Serializable (the highest level of isolation)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;
-- While this transaction is open, updates from other concurrent sessions are blocked or deferred.
COMMIT;

4. Confirming Durability

While durability is handled automatically by the storage engine via Write-Ahead Logging (WAL), you confirm it by issuing a successful COMMIT:

SQL

BEGIN TRANSACTION;
INSERT INTO audit_logs (event_desc, logged_at) VALUES ('System backup verified', CURRENT_TIMESTAMP);
COMMIT; 
-- Once the COMMIT returns success, durability is guaranteed by the RDBMS storage layer,  

-- ensuring the data persists even if the database service is immediately restarted.