What is ACID in SQL?

ACID is an acronym describing a set of properties of transactions in database management systems, ensuring their reliability and data integrity.

• Atomicity: A transaction is either completed in full or not at all. If any part of the transaction fails, all changes made within this transaction are rolled back to the original state.

  BEGIN TRANSACTION; -- Start of transaction
  
  -- Operations that should be executed as a single unit
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
  INSERT INTO transactions (account_id, amount) VALUES (1, -100);
  
  COMMIT; -- Commit the transaction if all successful
  -- ROLLBACK; -- Rollback the transaction in case of error
  

• Consistency: A transaction transitions the database from one consistent state to another. All integrity constraints (primary keys, foreign keys, unique constraints) are maintained.

  -- Example of violating consistency: attempting to insert a record with a duplicate unique key
  INSERT INTO users (user_id, email) VALUES (1, 'test@example.com');
  -- If a user with user_id = 1 already exists and user_id is a unique key, this operation will cause an error
  INSERT INTO users (user_id, email) VALUES (1, 'another@example.com'); 
  

• Isolation: The execution of one transaction does not affect the execution of other parallel transactions. Each transaction sees the database as if it were the only one working with it. There are different levels of isolation (Read Uncommitted, Read Committed, Repeatable Read, Serializable) that affect the visibility of changes by other transactions.

  -- Example: Two transactions read and update the same data
  -- Transaction 1:
  BEGIN TRANSACTION;
  SELECT balance FROM accounts WHERE account_id = 1; -- Read 1
  -- Delay
  UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
  COMMIT;
  
  -- Transaction 2 (parallel):
  BEGIN TRANSACTION;
  SELECT balance FROM accounts WHERE account_id = 1; -- Read 2
  -- Delay
  UPDATE accounts SET balance = balance - 30 WHERE account_id = 1;
  COMMIT;
  
  -- The level of isolation affects which value of balance each transaction will see
  

• Durability: After a transaction successfully completes (commit), the changes are permanent and will not be lost even in case of system failures (e.g., power outages).

  -- After COMMIT; changes are written to durable storage (e.g., disk)
  COMMIT; 
  -- Even in case of subsequent failure, the changes made by this transaction will be preserved