ACID Four Characteristics

Traditional transactions adhere to the ACID principles, which stand for Atomicity, Consistency, Isolation, and Durability.

• Atomicity: A transaction is a set of atomic operations; its atomicity ensures that all these operations are either fully completed or completely failed.

  # Example: An atomic operation in Python context (conceptual)
  def execute_atomic_transaction():
      try:
          step1 = perform_operation_a()
          step2 = perform_operation_b(step1)
          commit_step()  # All steps succeeded, transaction commits
      except Exception as e:
          rollback_step()  # Any failure triggers a full rollback
          raise e
  

• Consistency: The result of executing a transaction must transform the database from an inconsistent state to a consistent one. Ensuring database consistency means that upon completion of a transaction, all data within it must be in a logically and physically consistent state.

• Isolation: Since multiple transactions may need to process the same dataset simultaneously, each transaction should be isolated from others to prevent data corruption or interference.

  -- Example: Using TRANSACTION ISOLATION LEVEL SERIALIZABLE for strong isolation
  BEGIN TRAN;
      UPDATE accounts SET balance = balance - amount WHERE account_id = 'A';
      UPDATE logs INSERT INTO audit_log (action, timestamp) VALUES ('debit', NOW());
      COMMIT;
  

• Durability: Once a transaction is completed, its results must be able to withstand any system errors. This ensures that committed data remains intact even in the event of power loss or hardware failure.