SQL-ACID

The ACID properties in SQL Server are fundamental principles that ensure database transactions are processed reliably. Each property represents a key aspect of transaction management.

1. Atomicity

Atomicity ensures that a transaction is treated as a single, indivisible unit of work. Either all operations within the transaction succeed, or none of them are applied.

Example

BEGIN TRANSACTION;

BEGIN TRY
    INSERT INTO Accounts (AccountID, Balance) VALUES (1, 1000);
    UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
    INSERT INTO Accounts (AccountID, Balance) VALUES (2, 500);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;

If any operation (e.g., the update or second insert) fails, the entire transaction is rolled back.

2. Consistency

Consistency ensures that the database transitions from one valid state to another. A transaction should always leave the database in a consistent state, adhering to all constraints, cascades, triggers, and rules.

Example

-- Consider a `Bank` table with a constraint: Total balance must not exceed $10,000.
CREATE TABLE Bank (
    AccountID INT PRIMARY KEY,
    Balance INT CHECK (Balance <= 10000)
);

-- Invalid Transaction
BEGIN TRANSACTION;

UPDATE Bank SET Balance = Balance + 11000 WHERE AccountID = 1;

-- This violates the CHECK constraint and will fail.
COMMIT TRANSACTION;

The database ensures that constraints are enforced, maintaining consistency.

3. Isolation

Isolation ensures that concurrent transactions do not interfere with each other. Depending on the isolation level, a transaction can see intermediate states of other transactions or only their final states.

Example

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 101;
-- Hold the transaction open without committing.

-- Transaction 2
BEGIN TRANSACTION;
SELECT Stock FROM Products WHERE ProductID = 101;
-- The stock shown depends on the isolation level.
COMMIT TRANSACTION;

The isolation level (e.g., READ UNCOMMITTED, SERIALIZABLE) determines how much a transaction is "isolated" from others.

4. Durability

Durability ensures that once a transaction is committed, its changes are permanent, even in the event of a system failure.

Example

BEGIN TRANSACTION;

INSERT INTO Orders (OrderID, ProductID, Quantity) VALUES (1, 101, 5);
COMMIT TRANSACTION;

-- After committing, the data remains safe even if the server crashes.

SQL Server uses transaction logs to ensure changes are written to disk and can be recovered.

Real-World Example of ACID in Action

Scenario: Money Transfer

Atomicity

• Meaning - A transaction is an all-or-nothing operation. Either every step of the transaction is completed, or none of it happens. If there’s an error during the transaction, all changes made so far are rolled back, leaving the database in its original state.
• Example
  
  When transferring $100 from Account A to Account B:
  • Debit $100 from Account A.
  • Credit $100 to Account B.
  • If the debit succeeds but the credit fails (e.g., due to a network error), the debit operation is rolled back. The database remains as if the transaction never started.

Consistency

• Meaning - The database must always remain in a valid state, maintaining all defined rules, constraints, and relationships. A transaction must transition the database from one consistent state to another.
• Example
  
  After transferring $100 from Account A to Account B:
  • The total amount of money in both accounts combined must remain the same as before the transfer.
  • If Account A had $500 and Account B had $300 before the transfer, their combined balance of $800 must remain unchanged.

Isolation

• Meaning -Transactions running concurrently should not affect each other. The intermediate states of one transaction must not be visible to other transactions, depending on the isolation level.
• Example
  
  While transferring $100 from Account A to Account B:
  • Another transaction querying Account A or Account B will not see the intermediate state where $100 has been debited from Account A but not yet credited to Account B.
  • Depending on the isolation level (e.g., READ COMMITTED or SERIALIZABLE), other transactions may only see the final state once the transfer is completed.

Durability

• Meaning - Once a transaction is successfully committed, its changes are permanent and will survive system crashes or failures.
• Example
  
  After successfully transferring $100 from Account A to Account B
  • Even if the server crashes immediately after the transaction is committed, the updated balances in both accounts will be preserved when the system restarts.
  • This is ensured by SQL Server’s transaction log, which records the changes and applies them during recovery.

BEGIN TRANSACTION;

BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1; -- Debit
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2; -- Credit
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;

These properties ensure data integrity, consistency, and reliability, making them vital for robust database systems.