ACID and Transactions

Today, we're exploring a crucial concept in database management: ACID properties and transactions. These principles are fundamental to understanding how databases maintain consistency and reliability, especially when complex operations or multiple users interact with the database simultaneously.

What is ACID?

"ACID" is an acronym for Atomicity, Consistency, Isolation, and Durability. These four properties ensure that database transactions are processed reliably, maintaining data integrity despite errors, failures, or concurrent access.

Before we dive into each property, let's first understand what a transaction is:

A transaction is a sequence of one or more SQL operations that are treated as a single unit of work. Either all of these operations are completed successfully (in which case the transaction is committed), or none take effect (in which case the transaction is rolled back).

Think of a transaction like a package delivery. The package must go through several steps: pickup, transit, and delivery. If any step fails (e.g., the package is lost in transit), the entire process is considered unsuccessful, and the package is returned to the sender (rollback).

Only when all steps are completed successfully is the package considered delivered (commit).

Now, let's explore each ACID property and see how it applies in practice using SQL and PostgreSQL.

Setting the Stage: Our Bookstore Database

To illustrate these concepts, we'll use a simplified bookstore database:

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(200),
    Author VARCHAR(100),
    Price DECIMAL(10, 2),
    StockQuantity INT
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    BookID INT,
    Quantity INT,
    Price DECIMAL(10, 2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

-- Let's add some sample data
INSERT INTO Books (BookID, Title, Author, Price, StockQuantity) VALUES 
(1, 'The SQL Guide', 'Jane Doe', 29.99, 100),
(2, 'Database Design 101', 'John Smith', 39.99, 75),
(3, 'Transactions Explained', 'Alice Johnson', 34.99, 50);

INSERT INTO Customers (CustomerID, Name, Email) VALUES
(1, 'Bob Brown', 'bob@email.com'),
(2, 'Carol White', 'carol@email.com');

Now, let's explore each ACID property with practical examples.

Atomicity

Atomicity ensures that a transaction is treated as a single, indivisible unit. Either all operations in the transaction are completed successfully, or none of them are. There's no middle ground.

Let's create a transaction for a book purchase. We need to:

  1. Decrease the stock quantity of the book
  2. Create a new order
  3. Add order details
BEGIN;

-- Step 1: Decrease the stock quantity
UPDATE Books 
SET StockQuantity = StockQuantity - 1
WHERE BookID = 1;

-- Step 2: Create a new order
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES (1, 1, CURRENT_DATE, 29.99);

-- Step 3: Add order details
INSERT INTO OrderDetails (OrderDetailID, OrderID, BookID, Quantity, Price)
VALUES (1, 1, 1, 1, 29.99);

-- If we reach this point without errors, commit the transaction
COMMIT;

-- If an error occurs at any point, the transaction will be rolled back automatically
-- You can also manually roll back with:
-- ROLLBACK;

In this example, if any step fails (e.g., the book is out of stock, or there's an error inserting into the Orders table), the entire transaction is rolled back, and no changes are made to the database. This preserves atomicity.

Consistency

Consistency ensures that a transaction brings the database from one valid state to another. All rules, constraints, and triggers must be satisfied.

Let's add a constraint to our Books table to ensure the stock quantity is never negative, then create a transaction that respects this constraint:

-- First, add a constraint
ALTER TABLE Books ADD CONSTRAINT check_positive_stock CHECK (StockQuantity >= 0);

-- Now, let's create a transaction that respects this constraint
BEGIN;

-- Check if we have enough stock
SELECT StockQuantity INTO @current_stock FROM Books WHERE BookID = 1;

IF @current_stock > 0 THEN
    -- Decrease the stock quantity
    UPDATE Books 
    SET StockQuantity = StockQuantity - 1
    WHERE BookID = 1;

    -- Create a new order
    INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
    VALUES (2, 1, CURRENT_DATE, 29.99);

    -- Add order details
    INSERT INTO OrderDetails (OrderDetailID, OrderID, BookID, Quantity, Price)
    VALUES (2, 2, 1, 1, 29.99);

    COMMIT;
ELSE
    ROLLBACK;
    -- In a real application, you'd handle the "out of stock" situation here
END IF;

This transaction ensures that we never have a negative stock quantity, maintaining the consistency of our data.

Isolation

Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.

PostgreSQL provides different isolation levels. Let's look at how two concurrent transactions might behave under different isolation levels:

-- Transaction 1
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE Books SET StockQuantity = StockQuantity - 1 WHERE BookID = 1;
-- Pause here and run Transaction 2 before committing

-- Transaction 2
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT StockQuantity FROM Books WHERE BookID = 1;
COMMIT;

-- Back to Transaction 1
COMMIT;

In this example, under READ COMMITTED isolation (the default in PostgreSQL), Transaction 2 will see the old value of StockQuantity, not the updated value from the uncommitted Transaction 1. This prevents "dirty reads" but allows "non-repeatable reads".

You can experiment with different isolation levels (READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE) to see how they affect concurrent transactions.

Durability

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., a power outage or crash). The database system typically handles this.

While we can't directly demonstrate durability in SQL (as the database system handles it), we can show how to ensure our critical data is safely committed:

BEGIN;

-- Perform some critical operations
UPDATE Books SET StockQuantity = StockQuantity - 1 WHERE BookID = 1;
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES (3, 2, CURRENT_DATE, 29.99);

-- Ensure the transaction is committed
COMMIT;

-- After the COMMIT, these changes are guaranteed to be saved, even if the system crashes immediately after

In PostgreSQL, you can also use synchronous replication for added durability, ensuring that transactions are committed on multiple servers before considering them complete.

A Complete Transaction

Now that we've seen each ACID property in action, let's create a more complex transaction that encompasses all of these properties:

BEGIN;

-- Set a high isolation level for this critical transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Check stock and customer existence (Consistency)
IF EXISTS (SELECT 1 FROM Books WHERE BookID = 1 AND StockQuantity > 0) 
   AND EXISTS (SELECT 1 FROM Customers WHERE CustomerID = 1) THEN

    -- Decrease stock (Atomicity)
    UPDATE Books SET StockQuantity = StockQuantity - 1 WHERE BookID = 1;

    -- Create order (Atomicity)
    INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
    VALUES (4, 1, CURRENT_DATE, 
           (SELECT Price FROM Books WHERE BookID = 1));

    -- Add order details (Atomicity)
    INSERT INTO OrderDetails (OrderDetailID, OrderID, BookID, Quantity, Price)
    VALUES (4, 4, 1, 1, (SELECT Price FROM Books WHERE BookID = 1));

    -- If we reach this point without errors, commit the transaction (Durability)
    COMMIT;
ELSE
    -- If conditions aren't met, rollback (Atomicity)
    ROLLBACK;
END IF;

-- The high isolation level (Serializable) ensures Isolation

This transaction demonstrates all ACID properties:

  • Atomicity: All operations succeed or none do.
  • Consistency: We check constraints before making changes.
  • Isolation: We use a high isolation level to prevent interference from other transactions.
  • Durability: Once committed, these changes are permanent.

You'll hear about ACID in relation to different databases and now see it in action in your database transactions. It is crucial for maintaining data integrity and reliability in your applications.

As you continue to work with databases, you'll find these principles guiding many of your design decisions.

Our next article will explore more advanced PostgreSQL-specific types and concepts like full-text search, triggers, and stored procedures.

BeginnerDatabaseSqlPostgresql
Avatar for Niall Maher

Written by Niall Maher

Founder of Codú - The web developer community! I've worked in nearly every corner of technology businesses: Lead Developer, Software Architect, Product Manager, CTO, and now happily a Founder.

Loading

Fetching comments

Hey! 👋

Got something to say?

or to leave a comment.