Mastering Database Fundamentals: ACID with practical example

In this guide, we’ll explore how to practically demonstrate these ACID principles which we saw in the previous article using PostgreSQL and Docker. By the end of this article, you’ll not only grasp the theoretical aspects of ACID but also see how it applies to real-world database transactions through hands-on examples. So, let’s dive into the core of database transactions and see how these principles keep our data safe, consistent, and reliable.

---

1. Set Up PostgreSQL with Docker

First, we’ll run a PostgreSQL instance in a Docker container. If you haven’t installed Docker yet, make sure to install Docker first.

Run the following command to pull the official PostgreSQL image and run it in a container:

docker run --name postgres-acid -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres

This will:

• Pull the postgres Docker image.
• Run a container named postgres-acid with the default postgres database user.
• Map PostgreSQL’s port 5432 on the container to your machine.
• Set the password for the postgres user as secret.

---

2. Connect to PostgreSQL

Once the container is running, you can connect to PostgreSQL using the psql client or any other PostgreSQL client.

To connect using Docker, execute the following command:

docker exec -it postgres-acid psql -U postgres

You are now inside the PostgreSQL shell.

---

3. Create a Sample Table

We will create a simple table to work with. Run this SQL command to create a table accounts with an id, balance, and name:

CREATE TABLE bank_account (
    id SERIAL PRIMARY KEY,
    account_name VARCHAR(50),
    balance NUMERIC
);

-- Insert initial data
INSERT INTO bank_account (account_name, balance) VALUES ('Account A', 1000), ('Account B', 500);

This creates two accounts: Account A with $1000 and Account B with $500.

---

4. Demonstrate the ACID Properties

We can now explore the ACID properties by running transactions that simulate a money transfer between these accounts.

Atomicity Example

We will now simulate a transfer of funds between two accounts, but introduce an error to see how PostgreSQL handles atomicity.

Start a transaction:

BEGIN;

Transfer funds from Account A to Account B:

UPDATE bank_account SET balance = balance - 200 WHERE account_name = 'Account A';
UPDATE bank_account SET balance = balance + 200 WHERE account_name = 'Account B';

Now, deliberately cause an error by attempting to select a non-existent table:

SELECT invalid_column FROM non_existent_table;

At this point, PostgreSQL will display an error like:

ERROR:  relation "non_existent_table" does not exist

Since this operation failed, PostgreSQL automatically places the transaction in an aborted state. Any further commands issued in this transaction will trigger the following message:

current transaction is aborted, commands ignored until end of transaction block

How to Recover:

To resolve this and roll back the transaction, simply run:

ROLLBACK;

This ensures that none of the changes (including the valid ones) are applied to the database, demonstrating atomicity—the “all or nothing” rule.

Testing Success:

To verify that no changes were made, run the following:

SELECT * FROM bank_account;

You should see the original balances unchanged. Now, if you’d like to proceed with a successful transaction, you can begin a new transaction:

BEGIN;

Then, perform the transfer correctly:

UPDATE bank_account SET balance = balance - 200 WHERE account_name = 'Account A';
UPDATE bank_account SET balance = balance + 200 WHERE account_name = 'Account B';

And finally:

COMMIT;

Check the balances again to confirm the transaction succeeded:

SELECT * FROM bank_account;

This demonstrates atomicity: either the entire transaction is completed, or no changes are made at all, preserving database integrity.

---

Consistency Example

Consistency ensures that a database remains in a valid state before and after a transaction, meaning any transaction will take the database from one valid state to another. It guarantees that all predefined rules, such as constraints, are respected.

Let’s demonstrate Consistency by enforcing a rule that the balance of any account cannot be negative.

Setup:

To enforce consistency, we will add a CHECK constraint to ensure that the balance in any account can never be less than zero.

Modify the bank_account table to include this constraint:

ALTER TABLE bank_account
ADD CONSTRAINT positive_balance CHECK (balance >= 0);

This rule guarantees that no transaction can result in a negative balance for any account, ensuring the database’s consistency.

Testing Consistency:

Let’s try to transfer more money from Account A than its current balance to see how the consistency is maintained.

Start a transaction:

BEGIN;

Attempt to transfer $1200 from Account A (which only has $1000):

UPDATE bank_account SET balance = balance - 1200 WHERE account_name = 'Account A';
UPDATE bank_account SET balance = balance + 1200 WHERE account_name = 'Account B';

Since Account A only has $1000, the CHECK constraint will prevent this transaction from violating the rule:

ERROR:  new row for relation "bank_account" violates check constraint "positive_balance"
DETAIL:  Failing row contains (1, Account A, -200).

This error occurs because the operation would have resulted in a negative balance, which violates the consistency rule we set.

Rollback the Transaction:

Since the transaction failed, we need to roll back to restore the database to its previous valid state:

ROLLBACK;

Successful Transaction:

Now, let’s try a valid transfer that maintains consistency:

BEGIN;
UPDATE bank_account SET balance = balance - 200 WHERE account_name = 'Account A';
UPDATE bank_account SET balance = balance + 200 WHERE account_name = 'Account B';
COMMIT;

This transaction follows the constraint that no account can have a negative balance, so it completes successfully. The balances are updated correctly without violating the consistency of the database.

---

Isolation Example

Let’s run two simultaneous transactions that attempt to update the same row. Without proper isolation, these could interfere with each other.

Open two separate PostgreSQL sessions (using the same docker exec command). Run the following commands in each:

Session 1:

BEGIN;

-- Select balance of Account A
SELECT * FROM accounts WHERE name = 'Account A';

-- Deduct $100 from Account A
UPDATE accounts SET balance = balance - 100 WHERE name = 'Account A';

-- Do not commit yet; leave the transaction open

Session 2:

BEGIN;

-- Try to read the balance of Account A
SELECT * FROM accounts WHERE name = 'Account A';

-- Attempt to deduct $200 from Account A
UPDATE accounts SET balance = balance - 200 WHERE name = 'Account A';

COMMIT;

You’ll see that Session 2 is blocked until Session 1 completes, demonstrating the isolation property. PostgreSQL uses isolation to prevent inconsistent results due to concurrent transactions.

---

Durability Example

To test durability, commit a transaction and then stop the Docker container.

BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE name = 'Account B';
COMMIT;

Now stop the container:

docker stop postgres-acid
docker start postgres-acid

Once the container restarts, reconnect and check the balance:

docker exec -it postgres-acid psql -U postgres -c "SELECT * FROM accounts;"

The transaction’s result (the updated balance of Account B) should still be there, demonstrating durability.

---

5. Clean Up

Once you’re done, you can stop and remove the PostgreSQL Docker container:

docker stop postgres-acid
docker rm postgres-acid

With this example using PostgreSQL, we have practically demonstrated how Atomicity, Consistency, Isolation, and Durability (ACID) work, ensuring a deeper understanding of these critical database properties.

Series Navigation

<< Mastering Database Fundamentals: ACID Property