What is ACID in the context of databases?

ACID is an acronym that defines the properties of transactions in relational databases, ensuring their reliability and data integrity even in case of failures.

• Atomicity: A transaction either completes entirely or not at all. No intermediate states. If any part of the operations fails, all changes are rolled back.

  // Example of atomicity: transferring money between accounts
  // If any update fails, the entire transaction is rolled back
  BEGIN TRANSACTION; // Start of transaction
  UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1;
  UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2;
  // If both updates succeed
  COMMIT; // Commit transaction
  // If any update fails
  ROLLBACK; // Rollback transaction
  

• Consistency: A transaction moves the database from one consistent state to another. It does not violate business rules and integrity constraints (e.g., unique keys, foreign keys).

  // Example of consistency: checking balance before withdrawal
  // The transaction will not allow the balance to become negative if such a constraint exists
  BEGIN TRANSACTION;
  SELECT balance FROM Accounts WHERE account_id = 1; // Check balance
  // If balance < 100, the transaction does not execute or is rolled back
  UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1;
  COMMIT;
  

• Isolation: Concurrent transactions do not affect each other. Each transaction "sees" the database in the state it was before other incomplete transactions began.

  // Example of isolation: two transactions read and update the same record
  // Without isolation, problems like dirty reads, non-repeatable reads, and phantom reads occur
  // Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable)
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; // The strictest level
  SELECT balance FROM Accounts WHERE account_id = 1;
  UPDATE Accounts SET balance = balance - 50 WHERE account_id = 1;
  COMMIT;
  

  Isolation levels and their issues:

  Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantoms
  Read Uncommitted	Yes	Yes	Yes
  Read Committed	No	Yes	Yes
  Repeatable Read	No	No	Yes
  Serializable	No	No	No

• Durability: After a transaction successfully completes (commits), its changes are saved and will not be lost even in case of system failure (e.g., power outage).

  // Example of durability: after COMMIT, data is written to disk or transaction log
  BEGIN TRANSACTION;
  INSERT INTO Orders (product_id, quantity) VALUES (101, 5);
  COMMIT; // After this, data is persistently saved
  

ACID properties are critically important for systems requiring high data integrity, such as banking and financial systems.