Transactions, Constraints, and Data Integrity

What Is a Database Transaction?

A transaction is a sequence of one or more SQL statements that PostgreSQL treats as a single unit of work. Either every statement in the unit succeeds and its changes are permanently saved, or none of them are. This all-or-nothing behavior is what makes transactions essential for any application where related changes must stay consistent—bank transfers, order processing, seat reservations, and so on.

In PostgreSQL you mark the boundaries of a transaction with three core commands:

• `BEGIN` — opens the transaction block.
• `COMMIT` — permanently writes all changes made since `BEGIN`.
• `ROLLBACK` — discards all changes made since `BEGIN` and returns the database to its prior state.

If a fatal error occurs inside a transaction block before you issue `COMMIT`, PostgreSQL automatically rolls back the entire block when the session ends or when you issue `ROLLBACK`.

ACID Properties

The acronym ACID describes the four guarantees a reliable database engine must provide.

Atomicity — All statements in a transaction succeed together or fail together. No partial updates are visible. In the bank-transfer example, debiting one account and crediting another must both succeed; a failure in the middle leaves neither change applied.

Consistency — A transaction moves the database from one valid state to another valid state. CHECK constraints, NOT NULL constraints, foreign key constraints, and UNIQUE constraints enforce the rules that define "valid." PostgreSQL checks constraints at the end of each statement (or at the end of the transaction for deferred constraints).

Isolation — Concurrent transactions do not interfere with each other in unexpected ways. PostgreSQL provides several transaction isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) that control how much one transaction can see of another's uncommitted work. The default is READ COMMITTED, meaning a transaction sees only data committed before each individual statement runs.

Durability — Once PostgreSQL confirms a `COMMIT`, the changes survive crashes, power failures, and restarts. PostgreSQL achieves this through its write-ahead log (WAL).

Using SAVEPOINT

A savepoint creates a named checkpoint inside an open transaction. You can roll back to that checkpoint without discarding work done before it.

```sql

BEGIN;

INSERT INTO orders ...; -- step 1

SAVEPOINT after_order; -- checkpoint

INSERT INTO order_items ...; -- step 2, may fail

ROLLBACK TO SAVEPOINT after_order; -- undo only step 2

INSERT INTO order_items ...; -- retry step 2

COMMIT; -- save step 1 and corrected step 2

```

Savepoints are especially useful in longer scripts where you want to recover from a specific error without restarting the entire transaction.

Constraints and Data Integrity

Constraints are rules enforced by PostgreSQL at the table level. They are the first line of defense for data integrity, independent of application code.

• NOT NULL — a column must have a value.
• UNIQUE — no two rows may share the same value in the specified column(s).
• PRIMARY KEY — combines NOT NULL and UNIQUE; identifies each row.
• FOREIGN KEY — a value in one table must match an existing value in another, maintaining referential integrity.
• CHECK — a row must satisfy a Boolean expression (e.g., `balance >= 0`).

When a constraint violation occurs inside a transaction, PostgreSQL raises an error. In psql the entire transaction block is then in an aborted state; no further statements execute until you issue `ROLLBACK` (or `ROLLBACK TO SAVEPOINT` if you set one before the violation).

A Bank-Transfer Scenario

Consider transferring $200 from account 101 to account 202. Without a transaction:

```sql

UPDATE accounts SET balance = balance - 200 WHERE id = 101;

-- crash here leaves 101 debited and 202 uncredited

UPDATE accounts SET balance = balance + 200 WHERE id = 202;

```

With a transaction:

```sql

BEGIN;

UPDATE accounts SET balance = balance - 200 WHERE id = 101;

UPDATE accounts SET balance = balance + 200 WHERE id = 202;

COMMIT;

```

If anything fails between `BEGIN` and `COMMIT`, a `ROLLBACK` (explicit or automatic) restores both accounts to their original balances.

Isolation Levels and Concurrent Access

Isolation problems have standard names:

• Dirty read — reading uncommitted data from another transaction. PostgreSQL's default level (READ COMMITTED) prevents this.
• Non-repeatable read — re-reading a row within the same transaction returns different data because another transaction committed a change in between. REPEATABLE READ prevents this.
• Phantom read — a query returns a different set of rows on a second run because another transaction inserted or deleted rows. SERIALIZABLE prevents this.

Set the isolation level immediately after `BEGIN`:

```sql

BEGIN;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

...

COMMIT;

```

Safety and Best Practices

• Always test destructive transactions (DELETE, UPDATE) on a development database before running them in production.
• Never leave a transaction open and walk away from a psql session; uncommitted transactions hold locks that block other users.
• Keep transactions as short as possible to reduce lock contention.
• Use `ROLLBACK` immediately when you detect an error inside a transaction block; do not attempt further statements in an aborted block.
• For scripts that run automatically, log errors and always include error-handling logic that issues a `ROLLBACK` on failure.