What Are Transactions? (ACID Properties)

Imagine transferring $500 from your checking account to your savings account. The bank subtracts $500 from checking, and then adds $500 to savings. But what happens if the system crashes after subtracting but before adding? Without proper safeguards, your $500 simply vanishes. This is the exact problem that SQL transactions and their ACID properties exist to prevent.

A transaction groups one or more SQL operations into a single, indivisible unit of work. Either everything succeeds or everything is rolled back as if nothing happened. There is no in-between state where money disappears, inventory counts are wrong, or orders exist without customers.

This guide explains what transactions are, walks through each of the four ACID properties with real-world analogies and practical SQL examples, and shows you why these guarantees are the foundation of every reliable database application.

What Is a Transaction?

A transaction is a sequence of one or more SQL statements that are executed as a single logical unit. The database guarantees that either all statements in the transaction complete successfully, or none of them take effect.

Basic Transaction Syntax

BEGIN;  -- Start the transaction

UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Debit checking
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- Credit savings

COMMIT;  -- Make both changes permanent

If anything goes wrong between BEGIN and COMMIT, you can undo everything:

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

ROLLBACK;  -- Undo BOTH updates, as if nothing happened

Transaction Lifecycle

BEGIN
  │
  ├── Statement 1 executes
  ├── Statement 2 executes
  ├── Statement 3 executes
  │
  ├── Everything OK? ──→ COMMIT (changes are permanent)
  │
  └── Something wrong? ─→ ROLLBACK (all changes undone)

Database-Specific Syntax

Database	Start Transaction	Commit	Rollback
PostgreSQL	BEGIN; or START TRANSACTION;	COMMIT;	ROLLBACK;
MySQL	START TRANSACTION;	COMMIT;	ROLLBACK;
SQL Server	BEGIN TRANSACTION;	COMMIT TRANSACTION;	ROLLBACK TRANSACTION;
Oracle	Implicit (every statement starts one)	COMMIT;	ROLLBACK;
SQLite	BEGIN TRANSACTION;	COMMIT;	ROLLBACK;

Autocommit Mode

Most databases operate in autocommit mode by default, meaning every individual SQL statement is automatically wrapped in its own transaction and committed immediately. When you explicitly use BEGIN, you are telling the database: "Do not commit anything until I say COMMIT."

-- In autocommit mode, this is immediately permanent
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- If the next statement fails, the debit ALREADY happened. No way to undo it.
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- With an explicit transaction, nothing is permanent until COMMIT
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;  -- NOW both are permanent together

The Sample Data

We will use a simple banking scenario throughout this guide:

accounts table:

id	owner	account_type	balance
1	Alice	checking	5000.00
2	Alice	savings	12000.00
3	Bob	checking	3200.00
4	Bob	savings	8500.00
5	Charlie	checking	1500.00

audit_log table:

id	account_id	action	amount	timestamp

orders table (for e-commerce examples):

id	customer_id	total	status
1	101	250.00	pending

inventory table:

product_id	product_name	stock
501	Widget Pro	10
502	Gadget Max	3

The ACID Properties

ACID is an acronym for the four guarantees that every reliable transaction system provides:

• Atomicity
• Consistency
• Isolation
• Durability

These are not optional features or configuration settings. They are fundamental promises that the database makes about how your data is handled. Let's explore each one in depth.

Atomicity

Atomicity means a transaction is all or nothing. Either every statement in the transaction succeeds and all changes are applied, or the entire transaction fails and no changes are applied. There is no partial execution.

The Real-World Analogy

Think of a bank transfer as a single action on your banking app. You tap "Transfer $500 from Checking to Savings" and either:

• Success: Checking goes down by $500 AND savings goes up by $500
• Failure: Neither account changes

You never see a state where the money left checking but did not arrive in savings. The transfer is atomic: it cannot be divided into separate, independently visible pieces.

Without Atomicity (The Problem)

Imagine executing two separate statements without a transaction:

-- Statement 1: Debit checking (succeeds)
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- 💥 Database crashes, power failure, or network error here

-- Statement 2: Credit savings (NEVER EXECUTES)
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

Result:

id	owner	account_type	balance
1	Alice	checking	4500.00 ← $500 deducted
2	Alice	savings	12000.00 ← No change!

Alice lost $500. It was deducted from checking but never added to savings. This is a catastrophic data integrity failure.

With Atomicity (The Solution)

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- 💥 If ANYTHING fails here, the database rolls back the debit automatically

UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

If the crash happens after the first UPDATE but before COMMIT, the database automatically rolls back the debit when it recovers. Alice's checking account stays at $5000. The $500 is safe.

After recovery:

id	owner	account_type	balance
1	Alice	checking	5000.00 ← Unchanged (rolled back)
2	Alice	savings	12000.00 ← Unchanged

Multi-Statement Atomicity

Atomicity is not limited to two statements. A transaction can contain any number of operations:

BEGIN;

-- Step 1: Debit the source account
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Step 2: Credit the destination account
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Step 3: Log the debit
INSERT INTO audit_log (account_id, action, amount, timestamp)
VALUES (1, 'debit', 500, NOW());

-- Step 4: Log the credit
INSERT INTO audit_log (account_id, action, amount, timestamp)
VALUES (2, 'credit', 500, NOW());

-- Step 5: Update the transfer history
INSERT INTO transfers (from_account, to_account, amount, transfer_date)
VALUES (1, 2, 500, NOW());

COMMIT;

All five operations succeed together, or none of them take effect. You will never have an audit log entry without the corresponding balance change, or a transfer record without the actual money movement.

Explicit Rollback

You can also trigger a rollback manually based on application logic:

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Check: does the source account have enough funds?
-- (In application code, you'd check the result)
-- Let's say the balance went negative...

ROLLBACK;  -- Undo everything, the debit never happened

Consistency

Consistency guarantees that a transaction moves the database from one valid state to another valid state. It ensures that all data rules, constraints, and invariants are satisfied both before and after the transaction.

The Real-World Analogy

Think of a bank's rule: "No account can have a negative balance." Before the transfer, both accounts have positive balances (valid state). After the transfer, both accounts must still have positive balances (valid state). If the transfer would make checking go negative, the entire transaction is rejected, and the database stays in its original valid state.

What "Consistent" Means in Practice

Consistency encompasses several types of rules:

Rule Type	Example	Enforced By
Data type constraints	Balance must be a number, not text	Column data types
NOT NULL constraints	Every account must have an owner	NOT NULL
Unique constraints	No two customers can have the same email	UNIQUE
Foreign key constraints	Every order must reference a valid customer	FOREIGN KEY
Check constraints	Balance must be >= 0	CHECK
Business rules	Total debits must equal total credits	Application logic + triggers

Consistency with CHECK Constraints

-- Define the rule: balance can never go negative
ALTER TABLE accounts ADD CONSTRAINT chk_positive_balance
CHECK (balance >= 0);

Now let's try a transfer that would violate this constraint:

BEGIN;

-- Charlie has $1500. Try to transfer $2000.
UPDATE accounts SET balance = balance - 2000 WHERE id = 5;
-- This would make balance = -500, violating the CHECK constraint

UPDATE accounts SET balance = balance + 2000 WHERE id = 4;

COMMIT;

Error: new row for relation "accounts" violates check constraint "chk_positive_balance"

The entire transaction fails. Neither the debit nor the credit takes effect. Charlie's balance stays at $1500, and Bob's savings stays at $8500. The database moved from a valid state (all positive balances) and stayed in that valid state.

Consistency with Foreign Keys

BEGIN;

-- Try to create an order for a customer that doesn't exist
INSERT INTO orders (customer_id, total, status)
VALUES (999, 150.00, 'pending');
-- Customer 999 doesn't exist!

-- Try to deduct inventory
UPDATE inventory SET stock = stock - 1 WHERE product_id = 501;

COMMIT;

Error: insert or update on table "orders" violates foreign key constraint

The order insertion fails because customer 999 does not exist. Depending on the database, the inventory deduction is also rolled back (PostgreSQL rolls back the entire transaction on error by default). The database stays consistent: no orphaned orders, no inventory changes without valid orders.

Consistency as a Shared Responsibility

Unlike the other three ACID properties, consistency is a shared responsibility between the database and the application:

• The database enforces structural rules: data types, NOT NULL, UNIQUE, FOREIGN KEY, CHECK constraints.
• The application enforces business rules that are too complex for constraints: "A customer's total credit cannot exceed their credit limit across all orders" or "A transfer between accounts must have equal debits and credits."

-- Business rule enforced by application logic within a transaction
BEGIN;

-- Calculate total outstanding credit for the customer
SELECT SUM(total) INTO customer_credit
FROM orders
WHERE customer_id = 101 AND status = 'pending';

-- Check against credit limit (application logic)
IF customer_credit + 500 > 10000 THEN
    ROLLBACK;  -- Exceeds credit limit, reject the transaction
ELSE
    INSERT INTO orders (customer_id, total, status)
    VALUES (101, 500.00, 'pending');
    COMMIT;
END IF;

tip

Put as many rules as possible into database constraints rather than application code. Constraints are enforced universally (regardless of which application or script modifies the data), while application-level rules can be accidentally bypassed.

Isolation

Isolation determines how (and whether) concurrent transactions can see each other's uncommitted changes. When multiple users or processes access the database simultaneously, isolation prevents them from interfering with each other.

The Real-World Analogy

Imagine two bank tellers processing transactions for the same account at the same time:

• Teller A is transferring $500 out of Alice's checking
• Teller B is checking Alice's balance to approve a loan

Without isolation, Teller B might see Alice's balance after Teller A deducted $500 but before the matching credit to savings was applied. Teller B would see an artificially low total balance and might deny the loan incorrectly.

Isolation ensures that Teller B either sees Alice's account before the entire transfer or after the entire transfer, never during the in-between state.

Concurrency Problems Without Proper Isolation

When isolation is insufficient, several categories of problems can occur:

Dirty Read

A transaction reads data that another transaction has modified but not yet committed. If that other transaction rolls back, the first transaction used data that never actually existed.

Timeline:
────────────────────────────────────────────────────────

Transaction A:                    Transaction B:

BEGIN;
UPDATE accounts
SET balance = balance - 500
WHERE id = 1;
-- balance is now 4500
-- (not yet committed)
                                  BEGIN;
                                  SELECT balance FROM accounts
                                  WHERE id = 1;
                                  -- Reads 4500 (DIRTY READ!)
                                  -- Uses this value for decisions...
ROLLBACK;
-- balance goes back to 5000
                                  -- But Transaction B already acted
                                  -- on the value 4500, which
                                  -- never actually existed!
                                  COMMIT;

Non-Repeatable Read

A transaction reads the same row twice and gets different values because another transaction modified and committed the row between the two reads.

Timeline:
────────────────────────────────────────────────────────

Transaction A:                    Transaction B:

BEGIN;
SELECT balance FROM accounts
WHERE id = 1;
-- Returns 5000
                                  BEGIN;
                                  UPDATE accounts
                                  SET balance = balance - 500
                                  WHERE id = 1;
                                  COMMIT;  -- Committed!

SELECT balance FROM accounts
WHERE id = 1;
-- Returns 4500 (DIFFERENT!)
-- Same query, same transaction,
-- different result!
COMMIT;

Phantom Read

A transaction runs the same query twice and gets a different set of rows because another transaction inserted or deleted rows that match the query's condition.

Timeline:
────────────────────────────────────────────────────────

Transaction A:                    Transaction B:

BEGIN;
SELECT COUNT(*) FROM orders
WHERE customer_id = 101;
-- Returns 3
                                  BEGIN;
                                  INSERT INTO orders
                                  (customer_id, total, status)
                                  VALUES (101, 300, 'pending');
                                  COMMIT;

SELECT COUNT(*) FROM orders
WHERE customer_id = 101;
-- Returns 4 (PHANTOM ROW!)
COMMIT;

Isolation Levels

SQL defines four standard isolation levels that control which of these problems are prevented:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

Higher isolation levels provide stronger guarantees but typically reduce concurrency (transactions may need to wait for each other or may be aborted to prevent conflicts).

Setting Isolation Levels

-- PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... your queries ...
COMMIT;

-- MySQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- ... your queries ...
COMMIT;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- ... your queries ...
COMMIT TRANSACTION;

Default Isolation Levels by Database

Database	Default Level
PostgreSQL	READ COMMITTED
MySQL (InnoDB)	REPEATABLE READ
SQL Server	READ COMMITTED
Oracle	READ COMMITTED
SQLite	SERIALIZABLE

Practical Example: READ COMMITTED vs REPEATABLE READ

READ COMMITTED (the default in PostgreSQL and SQL Server):

-- Transaction A
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT balance FROM accounts WHERE id = 1;  -- Returns 5000

-- Transaction B commits a debit of $500 here

SELECT balance FROM accounts WHERE id = 1;  -- Returns 4500 (sees committed change)
COMMIT;

REPEATABLE READ:

-- Transaction A
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;  -- Returns 5000

-- Transaction B commits a debit of $500 here

SELECT balance FROM accounts WHERE id = 1;  -- Still returns 5000 (snapshot preserved)
COMMIT;

With REPEATABLE READ, Transaction A sees a consistent snapshot of the data as it existed when the transaction started. External changes are invisible until Transaction A commits and starts a new transaction.

Choosing an Isolation Level

• READ COMMITTED is sufficient for most applications. Each statement sees the latest committed data.
• REPEATABLE READ is important when a transaction must see consistent data across multiple queries (e.g., generating a report, computing totals that must agree).
• SERIALIZABLE is for critical operations where absolute correctness is required (e.g., financial calculations, seat reservations). It provides the strongest guarantees but has the highest performance cost.
• READ UNCOMMITTED is rarely used because dirty reads can lead to application bugs. Some data warehousing scenarios use it for approximate analytics where speed matters more than precision.

Durability

Durability guarantees that once a transaction is committed, its changes are permanent, even if the system crashes immediately after the commit. The data survives power failures, operating system crashes, hardware failures, and software bugs.

The Real-World Analogy

When the bank teller confirms your transfer and gives you a receipt, that transfer is permanent. Even if the bank's computer crashes five seconds later, your transfer is recorded. When the system recovers, the transfer will be there. The receipt is your proof; the transaction log is the database's proof.

How Durability Works: Write-Ahead Logging (WAL)

Databases achieve durability through a mechanism called Write-Ahead Logging (WAL), also known as transaction logging or redo logging:

1. Transaction modifies data in memory (fast)
2. Changes are written to the TRANSACTION LOG on disk (durable)
3. COMMIT returns success to the application
4. Eventually, changes are written from memory to the actual data files

If crash occurs after step 2:
→ On recovery, database replays the transaction log
→ All committed changes are restored
→ All uncommitted changes are discarded

The key insight is that COMMIT does not return until the changes are safely on disk in the transaction log. This is why COMMIT involves a disk write and is the "expensive" part of a transaction.

Durability in Practice

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;  -- At this exact moment, changes are written to the transaction log on disk
-- ← Even if the server loses power RIGHT HERE, the transfer is safe

When the database restarts after a crash, it reads the transaction log and:

1. Replays (redo) any committed transactions whose changes had not yet been written to the data files
2. Undoes (rollback) any uncommitted transactions that were in progress at the time of the crash

This process is called crash recovery and happens automatically every time the database starts.

Durability Across Different Failure Scenarios

Failure Type	Data Safe?	How
Application crash	Yes	Transaction log is on disk, database is still running
Database process crash	Yes	Transaction log survives; crash recovery on restart
Operating system crash	Yes	Transaction log is on disk; OS crash does not erase disk data
Power failure	Yes	Transaction log uses fsync to force data to physical disk
Single disk failure	Depends	Safe if using RAID, replication, or cloud storage with redundancy
Data center fire	Depends	Safe only if replicas exist in another location

info

Durability protects against software and normal hardware failures. Protection against catastrophic hardware failures (disk destruction, data center loss) requires additional measures like replication, backups, and multi-region deployment. These are infrastructure concerns, not transaction concerns.

Durability Configuration Trade-offs

Some databases allow you to relax durability for better performance. This is a trade-off: you gain speed but risk losing the most recent transactions if a crash occurs.

-- PostgreSQL: Control WAL sync behavior
SET synchronous_commit = off;
-- Faster commits, but up to ~10ms of recent transactions could be lost in a crash
-- The database remains consistent (no partial transactions), but some
-- committed transactions might be lost

SET synchronous_commit = on;  -- Default: full durability

-- MySQL (InnoDB)
SET innodb_flush_log_at_trx_commit = 1;  -- Default: full durability (fsync every commit)
SET innodb_flush_log_at_trx_commit = 2;  -- Flush to OS cache, not disk (faster, less durable)
SET innodb_flush_log_at_trx_commit = 0;  -- Flush every second (fastest, least durable)

caution

Relaxing durability is appropriate for non-critical data like session logs, analytics events, or caching layers. Never relax durability for financial transactions, user data, or any data where loss is unacceptable.

ACID in Action: A Complete E-Commerce Example

Let's bring all four properties together in a realistic e-commerce order scenario. A customer places an order that must:

1. Create the order record
2. Deduct inventory
3. Charge the customer's balance
4. Log the transaction

BEGIN;

-- Step 1: Create the order
INSERT INTO orders (customer_id, total, status)
VALUES (101, 159.98, 'confirmed')
RETURNING id INTO order_id;  -- PostgreSQL syntax

-- Step 2: Create order items and deduct inventory
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (order_id, 501, 2, 79.99);

UPDATE inventory
SET stock = stock - 2
WHERE product_id = 501;

-- Step 3: Charge the customer
UPDATE accounts
SET balance = balance - 159.98
WHERE id = 101;

-- Step 4: Log the transaction
INSERT INTO audit_log (account_id, action, amount, timestamp)
VALUES (101, 'purchase', 159.98, NOW());

COMMIT;

Here is how each ACID property protects this transaction:

Property	What It Guarantees
Atomicity	If the inventory update fails (e.g., not enough stock), the order, charge, and log are all rolled back. No orphaned orders, no incorrect charges.
Consistency	CHECK (stock >= 0) prevents negative inventory. FOREIGN KEY ensures the customer exists. CHECK (balance >= 0) prevents overdrafts. The database moves from one valid state to another.
Isolation	Another customer checking the same product's stock count will not see the partially deducted inventory. They see either the pre-order stock (10) or the post-order stock (8), never the in-between.
Durability	Once COMMIT succeeds, the order, inventory change, charge, and log entry are all permanent, even if the server crashes one millisecond later.

What Happens If Step 2 Fails?

Suppose the inventory CHECK constraint fails because there is not enough stock:

BEGIN;

INSERT INTO orders (customer_id, total, status)
VALUES (101, 159.98, 'confirmed');
-- ✅ Succeeds (order row created in memory)

UPDATE inventory SET stock = stock - 2 WHERE product_id = 502;
-- ❌ FAILS: stock is 3, subtracting 2 gives 1 (OK)... 
-- Actually, let's say product 502 has stock = 1, subtracting 2 gives -1
-- CHECK constraint violation!

-- ERROR: new row violates check constraint "chk_positive_stock"

ROLLBACK;  -- PostgreSQL auto-rolls back on error in many configurations
-- The order insertion from Step 1 is UNDONE
-- The database is exactly as it was before BEGIN

Final state: unchanged. No order, no inventory change, no charge, no log entry. Atomicity in action.

Common Mistakes to Avoid

Mistake 1: Long-Running Transactions

-- BAD: Transaction stays open for minutes or hours
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- User goes to lunch... transaction still open
-- Locks are held, other transactions are blocked
-- ...30 minutes later...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Long transactions hold locks, consume memory, and prevent other operations from proceeding. They also prevent the database from reclaiming space (in PostgreSQL, long transactions block VACUUM).

Fix: Keep transactions as short as possible. Do all your thinking and calculation outside the transaction; only open it when you are ready to read-and-write atomically.

-- GOOD: Short, focused transaction
-- Application logic happens OUTSIDE the transaction
-- Calculate amount, validate inputs, etc.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
INSERT INTO audit_log (...) VALUES (...);
COMMIT;
-- Transaction open for milliseconds, not minutes

Mistake 2: Forgetting to COMMIT or ROLLBACK

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Oops, developer forgot COMMIT
-- Transaction stays open indefinitely, holding locks

In interactive sessions (psql, MySQL CLI), uncommitted transactions can linger and block other operations. Some applications also leak database connections with open transactions.

Fix: Always ensure every BEGIN has a matching COMMIT or ROLLBACK. In application code, use try/catch patterns:

# Python pseudocode with proper transaction handling
try:
    connection.begin()
    connection.execute("UPDATE accounts SET balance = balance - 500 WHERE id = 1")
    connection.execute("UPDATE accounts SET balance = balance + 500 WHERE id = 2")
    connection.commit()
except Exception:
    connection.rollback()  # Always rollback on error
    raise

Mistake 3: Assuming Autocommit Provides Atomicity

-- In autocommit mode (default), each statement is its own transaction
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- ← This is COMMITTED immediately

-- If the next statement fails, the debit above CANNOT be undone
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

Each statement succeeds or fails independently. There is no atomicity across statements.

Fix: Always use explicit transactions when multiple statements must succeed or fail together:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Mistake 4: Catching Errors Without Rolling Back

-- BAD (pseudocode): Catching the error but not rolling back
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

try:
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- Fails
except:
    log("Transfer failed")
    # Transaction is still open! The debit is still pending!
    # If COMMIT is called later, only the debit goes through!

COMMIT;  -- DANGER: Commits the debit without the credit!

Fix: Always ROLLBACK when catching errors inside a transaction:

-- GOOD
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

try:
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
except:
    ROLLBACK;  -- Undo the debit
    raise;     -- Re-throw the error

COMMIT;

PostgreSQL Error Behavior

In PostgreSQL, if any statement within a transaction fails, the entire transaction enters an error state and all subsequent statements will be rejected with current transaction is aborted. You must issue a ROLLBACK before you can do anything else. This is actually a safety feature that prevents accidentally committing partial work.

BEGIN;
INSERT INTO accounts (id, owner) VALUES (1, 'Alice');  -- Fails (duplicate key)
-- ERROR: duplicate key value violates unique constraint

SELECT * FROM accounts;  -- This also fails!
-- ERROR: current transaction is aborted, commands ignored until end of transaction block

ROLLBACK;  -- Required to clear the error state

ACID Properties at a Glance

Property	Description
Atomicity	All or nothing. Every statement in the transaction succeeds together or fails together. No partial execution.
Consistency	Database moves from one valid state to another. All constraints, rules, and invariants are satisfied after the transaction completes.
Isolation	Concurrent transactions do not interfere with each other. Each transaction sees a consistent view of the data regardless of other activity.
Durability	Once committed, changes are permanent. They survive crashes, power failures, and restarts. Guaranteed by write-ahead logging (WAL).

Summary

SQL transactions and their ACID properties are the foundation of data reliability in every serious database application:

• Atomicity ensures that multi-statement operations are all or nothing. A bank transfer either fully completes (debit + credit) or fully rolls back. There is no in-between state where money vanishes.

• Consistency ensures that transactions respect all data rules and constraints. CHECK constraints, foreign keys, unique constraints, and application-level business rules are all enforced. The database never enters an invalid state.

• Isolation ensures that concurrent transactions do not interfere with each other. Four isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) offer different trade-offs between correctness and performance.

• Durability ensures that committed data is permanent. Write-ahead logging guarantees that even if the server crashes immediately after COMMIT, the data will be recovered intact.

Use BEGIN to start a transaction, COMMIT to make changes permanent, and ROLLBACK to undo everything. Keep transactions short, always handle errors with rollbacks, and choose the appropriate isolation level for your use case.

tip

Every time you write SQL that modifies more than one row or table as part of a single logical operation, ask yourself: "What happens if this fails halfway through?" If the answer is "bad things," wrap it in a transaction. ACID guarantees are what make databases trustworthy.