YouTube video: https://youtu.be/pomxJOFVcQs?si=wIIntWXpLRQeCJg8

Introduction

ACID is an acronym representing the four essential properties of relational databases:

• Atomicity
• Consistency
• Isolation
• Durability

These properties ensure reliable processing of database transactions. Understanding ACID is fundamental for anyone working with relational database systems such as MySQL, SQL Server, Oracle, or PostgreSQL.

This guide explains each property in depth, the critical role they play in database management, and also explores why some modern systems (NoSQL databases) choose to relax certain properties, especially consistency, to improve scalability and performance.

What is a Transaction?

Before diving into ACID, it’s important to understand what a transaction is in the context of databases:

• A transaction is a collection of one or more queries that represent a single unit of work.
• This unit of work is executed atomically — either all queries succeed, or none do.
• Typical operations in a transaction might include:
  • Reading current values (SELECT)
  • Modifying values (UPDATE, INSERT, DELETE)
• Transactions have control semantics like:
  • BEGIN TRANSACTION: Mark the start
  • COMMIT: Persist changes if everything succeeds
  • ROLLBACK: Undo all changes if something fails

Example: Bank Transfer

Transferring money from Account A to Account B involves:

1. Check the balance of Account A.