Photo by Thiago Matos : https://www.pexels.com/photo/orange-led-cfl-bulb-2338672/
In production systems that manage critical data- whether financial transactions, user state, or infrastructure configuration- the integrity of state transitions is non-negotiable. That’s where the ACID properties of relational databases come into play.
This article takes a focused look at Atomicity , the first principle of ACID. We’ll analyze how PostgreSQL enforces it, how failures are handled, and what engineers must understand to avoid subtle consistency bugs in real-world systems.
What Atomicity Really Means
Atomicity guarantees that a transaction is all-or-nothing : every operation in the transaction either completes successfully, or none of them do. There is no concept of “partial success.”
Atomicity is enforced at the transaction level, not the statement level. PostgreSQL ensures that even if a failure occurs midway through a transaction, all previous operations in that transaction are automatically rolled back.
For reference, see the official PostgreSQL Transaction Tutorial.
A Simple Transfer Operation
Consider a simplified banking example. We’ll create an accounts table and simulate transferring funds between two accounts:
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (name, balance)
VALUES ('Alice', 1000), ('Bob', 1000);
Now, we implement a basic transfer operation using an explicit transaction:
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob';
COMMIT;
If both statements succeed, the transaction is committed and both updates become permanent.
Simulating a Failure
Now let’s simulate an error that occurs in the second statement:
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
-- Intentional typo: column name is misspelled
UPDATE accounts SET balnce = balance + 200 WHERE name = 'Bob';
COMMIT;
PostgreSQL will raise an error:
ERROR: column "balnce" does not exist
At this point, the transaction is marked as failed. No further operations are allowed until a rollback is issued:
ROLLBACK;
Although the first UPDATE ran without error, its effect is discarded because the transaction was never successfully committed. This is atomicity in practice.
What If You Don’t Use Transactions?
When operations are issued individually without an explicit BEGIN block, PostgreSQL executes each as its own atomic unit:
UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
-- This succeeds
UPDATE accounts SET balnce = balance + 200 WHERE name = 'Bob';
-- This fails
Here, Alice’s balance is reduced, but Bob never receives the funds. This violates our consistency requirements and results in data loss.
Lesson: without explicit transactions, the database cannot enforce atomicity across multiple operations.
Statement-Level Atomicity
PostgreSQL does provide implicit transaction handling for single statements. If a single INSERT, UPDATE, or DELETE statement fails, it is discarded entirely and does not affect the database. However, for multiple operations that must succeed together, atomicity is not guaranteed unless you explicitly wrap them in a transaction.
Relevant documentation: PostgreSQL BEGIN
Constraint Failures and Runtime Errors
PostgreSQL also enforces atomicity in the presence of constraint violations or other runtime errors. For example, suppose we enforce that balances cannot be negative:
ALTER TABLE accounts ADD CONSTRAINT non_negative_balance CHECK (balance >= 0);
Then attempt:
BEGIN;
UPDATE accounts SET balance = balance - 1100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 1100 WHERE name = 'Bob';
COMMIT;
Alice only has 1000, so her balance would go negative. The first statement violates the constraint, and the entire transaction is aborted. No changes are applied.
Handling Transactions in Application Code
Here’s how atomicity works in a real-world PostgreSQL client using Python with psycopg3:
import psycopg
conn = psycopg.connect("postgresql://user:pass@localhost/db")
try:
with conn.transaction():
with conn.cursor() as cur:
cur.execute("UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice'")
cur.execute("UPDATE accounts SET balnce = balance + 200 WHERE name = 'Bob'") # Bug
except Exception as e:
print(f"Transaction failed: {e}")
In this case, conn.transaction() ensures that the entire block is treated as atomic. When the second query fails, psycopg3 automatically rolls back the transaction. No data is persisted.
Summary of Cases
- Single UPDATE : Executed without an explicit transaction block, PostgreSQL treats it as an implicit transaction. It either succeeds or fails as a single atomic operation.
- Multiple UPDATEs without BEGIN : When multiple operations are issued individually without wrapping them in a transaction, atomicity is not guaranteed. If one statement succeeds and another fails, partial state changes can persist.
- BEGIN / COMMIT with syntax or runtime error : When using an explicit transaction, any error- be it a syntax issue or constraint violation- causes the entire transaction to be rolled back. This ensures atomicity across all enclosed operations.
- Using psycopg3 with with conn.transaction(): In application code, using this context manager ensures atomicity. If any exception is raised during the block, psycopg3 automatically rolls back the transaction.
Final Thoughts
PostgreSQL provides strong atomicity guarantees, but they’re only effective when transactions are used intentionally and correctly. Multi-statement operations should always be wrapped in an explicit transaction, and developers must anticipate rollback paths as part of the application’s normal flow.
Failing to do so can result in partial writes, corrupted state, and long-tail data integrity bugs that are difficult to trace.
For more details, refer to the official documentation:
Top comments (0)