Syed Jafer K

Today, i refreshed on a topic Atomicity (All Or Nothing ) using postgres for tommorow session. In this blog i jot down notes on Atomicity for better understanding.

Atomicity is one of the foundational properties of database transactions, as defined by the ACID principles (Atomicity, Consistency, Isolation, Durability). In PostgreSQL, atomicity ensures that a transaction is treated as a single, indivisible unit of work. Either all the operations within a transaction are successfully executed, or none of them are applied. This guarantees that the database remains in a consistent state, even in the event of errors, crashes, or other failures.

What is Atomicity?

Atomicity ensures that a transaction adheres to the principle of “all or nothing”. If any part of a transaction fails, the entire transaction is rolled back, leaving the database in its state prior to the transaction’s initiation. This property is crucial for maintaining the integrity and reliability of the database.

Atomicity in PostgreSQL

PostgreSQL implements atomicity by using:

1. Write-Ahead Logging (WAL): PostgreSQL maintains a transaction log to ensure changes can be undone or redone as needed.
2. Transactional Control Commands: Commands like BEGIN, COMMIT, and ROLLBACK explicitly define the boundaries of a transaction and control its outcome.

Example: Create Table Queries for Money Transaction Example

To implement a money transaction system, we need two tables: accounts and transactions.

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_holder_name VARCHAR(255) NOT NULL,
    balance NUMERIC(15, 2) NOT NULL CHECK (balance >= 0)
);

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    amount NUMERIC(15, 2) NOT NULL,
    account_id INT NOT NULL REFERENCES accounts(account_id),
    transaction_type VARCHAR(10) NOT NULL CHECK (transaction_type IN ('credit', 'debit')),
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1. A Successful Transaction

BEGIN;

INSERT INTO accounts (account_id, account_holder_name, balance) VALUES (1, 'John Doe', 1000);
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
INSERT INTO transactions (transaction_id, amount, account_id, transaction_type) VALUES (101, 500, 1, 'debit');

COMMIT;

• In this example, all three operations are part of a single transaction.
• If all statements execute successfully, the transaction is committed, and the changes are permanently applied.

2. A Failed Transaction with Rollback

BEGIN;

INSERT INTO accounts (account_id, account_holder_name, balance) VALUES (2, 'Jane Smith', 2000);
UPDATE accounts SET balance = balance - 300 WHERE account_id = 2;
-- Intentional error: duplicate key value violates unique constraint
INSERT INTO transactions (transaction_id, amount, account_id, transaction_type) VALUES (101, 300, 2, 'debit');

ROLLBACK;

• Here, an error occurs while inserting into the transactions table due to a duplicate key violation.
• PostgreSQL automatically halts the transaction, and the user explicitly issues a ROLLBACK command to undo all previous changes.

3. Ensuring Atomicity with Savepoints

Savepoints allow partial rollback within a transaction, providing finer control over error handling.

BEGIN;

INSERT INTO accounts (account_id, account_holder_name, balance) VALUES (3, 'Alice Brown', 3000);
SAVEPOINT sp1;

-- This statement causes an error
INSERT INTO transactions (transaction_id, amount, account_id, transaction_type) VALUES (102, 'invalid_amount', 3, 'debit');

-- Roll back to the savepoint instead of rolling back the entire transaction
ROLLBACK TO sp1;

-- Correcting the error and continuing
INSERT INTO transactions (transaction_id, amount, account_id, transaction_type) VALUES (102, 300, 3, 'debit');

COMMIT;

The ROLLBACK TO sp1; command undoes changes made after the savepoint sp1 while keeping the earlier changes intact.

Key Benefits of Atomicity

1. Error Recovery: By rolling back unsuccessful transactions, atomicity ensures that the database is not left in a partial or inconsistent state.
2. Data Integrity: The “all or nothing” approach protects data from corruption caused by system crashes, errors, or interruptions.
3. Simplified Development: Developers can group multiple operations into a single transaction without worrying about partial failures.