ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a vital compliace requirement for DBMS, enforcing four essential characteristics.
The main objective of ACID is to ensure the integrity and reliability of transactions within a database, even in the face of errors, power failures, or other unexpected incidents.
Notably, both MYSQL (specifically InnoDB’s engine) and Postgres (since 2001) are recognized as ACID-cimpliant. In this article, I will delve into the inner workings of ACID within the context of Postgres.
To practice ACID, it is necessary to set up the project using PostgreSQL. Personally, I prefer using psql for executing SQL commands via the terminal.
acid_example database and connect to the database:postgres=# CREATE DATABASE acid_example;
CREATE DATABASE
postgres=# \c acid_example
You are now connected to database "acid_example" as user "xxx".
acid_example=#
accounts table:
CREATE TABLE accounts (
id INT GENERATED BY DEFAULT AS IDENTITY,
name TEXT NOT NULL,
balance BIGINT NOT NULL,
PRIMARY KEY(id)
);
SELECT * FROM accounts;
# >> Output
# id | name | balance
# ----+------+---------
# (0 rows)
The first characteristic of ACID is the atomicity. The atomicity treats every statement within a transaction (create, read, update, or delete) as an indivisible unit.
If the transaction is successful, all the statements should be executed. If not, none of the statements should take the effect.
This property ensures that data remains intact and prevents any loss or damage during the transaction process.
In Postgres, the atomicity is achieved using database transactions. By default, Postgres executes transactions in an “unchained mode”, also referred to as “auto-commit” in othe database systems.
However, if want to properly implement the atomicity, it’s need to manually use database transactions, where each transaction can consist of one or more statements.
For example, let’s store two pieces of data in the database. When commit the transaction, both pieces of data will be successfully stored in the database.
Initiate a transaction, use BEGIN TRANSACTION, BEGIN WORK, or just BEGIN.
Make the changes visible to other sessions, it’s need to commit the transaction using COMMIT TRANSACTION, COMMIT WORK, or COMMIT.
Session 1:
BEGIN TRANSACTION;
INSERT INTO accounts(name, balance)
VALUES('Adi', 1000000);
Verify that teh data has not been stored yet, use Session 2 (another terminal) to connect to the database.
Session 2:
postgres=# \c acid_example;
You are now connected to database "acid_example" as user "xxx".
acid_example=# SELECT * FROM accounts;
# >> Output
# id | name | balance
# ----+------+---------
# (0 rows)
The data is currently empty. This indicates that even if storing the data, Postgres doesn’t consider it a success or failure because that’s not yet committed the data.
Session 1:
INSERT INTO accounts(name, balance)
VALUES('Angli', 2000000);
COMMIT;
Session 2:
SELECT * FROM accounts;
# >> Output
# id | name | balance
# ----+-------+---------
# 1 | Adi | 1000000
# 2 | Angli | 2000000
# (2 rows)
Use the rollback feature to undo the transactions. Rollback cancels all the statements within the current transaction.
Session 1:
BEGIN TRANSACTION;
DELETE FROM accounts WHERE id = 1;
DELETE FROM accounts WHERE id = 2;
SELECT * FROM accounts;
# >> Output
# id | name | balance
# ----+------+---------
# (0 rows)
Remember, this is just temporary data because that have not yet committed the transaction. Observe that the data from Session 2 is still present.
Session 2:
SELECT * FROM accounts;
# >> Output
# id | name | balance
# ----+-------+---------
# 1 | Adi | 1000000
# 2 | Angli | 2000000
# (2 rows)
Return to Session 1 and perform a rollback on the transactions, then observe that the data is not deleted; it remains intact.
Session 1:
ROLLBACK;
SELECT * FROM accounts;
# >> Output
# id | name | balance
# ----+-------+---------
# 1 | Adi | 1000000
# 2 | Angli | 2000000
# (2 rows)