BEGIN, COMMIT & ROLLBACK

A transaction is a group of SQL statements that are treated as a single unit of work. Either all the statements succeed and their changes become permanent, or none of them do. Transactions are one of the most important concepts in database systems -- they keep your data consistent even when errors occur, networks fail, or the server crashes mid-operation.

Why Transactions Matter

Consider a simple scenario: transferring inventory from one product to another. This requires two updates:

1. Decrease the quantity of Product A
2. Increase the quantity of Product B

What if the system crashes after step 1 but before step 2? Without transactions, Product A has lost inventory but Product B never gained it. The inventory has vanished into thin air. Transactions prevent this by ensuring both steps either complete together or are both undone.

The ACID Properties

Transactions provide four guarantees, known as ACID:

• Atomicity -- All statements in a transaction succeed or fail as one unit. There is no partial completion.
• Consistency -- A transaction brings the database from one valid state to another. All constraints and rules are satisfied.
• Isolation -- Concurrent transactions do not interfere with each other. Each transaction sees a consistent snapshot of the data.
• Durability -- Once a transaction is committed, the changes survive system failures (crashes, power outages, etc.).

Auto-Commit Mode

By default, PostgreSQL operates in auto-commit mode. Each individual SQL statement is automatically wrapped in its own transaction and committed immediately:

-- These two statements are two separate transactions
UPDATE products SET quantity = quantity - 5 WHERE name = 'Widget';
-- ^ This is automatically committed

UPDATE products SET price = price * 1.1 WHERE name = 'Gadget';
-- ^ This is independently committed

If the second statement fails, the first is already committed and cannot be undone. This is fine for simple operations, but dangerous when multiple statements need to succeed or fail together.

BEGIN: Starting a Transaction

The BEGIN statement starts an explicit transaction. All subsequent statements become part of this transaction until you end it with COMMIT or ROLLBACK.

BEGIN;

-- These statements are now part of a single transaction
UPDATE products SET quantity = quantity - 10 WHERE name = 'Widget';
UPDATE products SET quantity = quantity + 10 WHERE name = 'Gadget';

At this point, the changes are tentative. Other connections to the database cannot see them yet. You must decide what to do with the transaction.

START TRANSACTION is a synonym for BEGIN and works identically:

START TRANSACTION;
-- ... same as BEGIN

COMMIT: Making Changes Permanent

COMMIT ends the transaction and makes all changes permanent. Once committed, the changes are durable and visible to other connections.

BEGIN;

UPDATE products SET quantity = quantity - 10 WHERE name = 'Widget';
UPDATE products SET quantity = quantity + 10 WHERE name = 'Gadget';

COMMIT;
-- Both updates are now permanent and visible to everyone

The complete inventory transfer is atomic. Either both products are updated, or neither is.

ROLLBACK: Undoing Changes

ROLLBACK ends the transaction and undoes all changes made since BEGIN. The database returns to exactly the state it was in before the transaction started.

BEGIN;

UPDATE products SET price = 0 WHERE category = 'Electronics';
-- Oops! We set all electronics prices to zero

ROLLBACK;
-- All changes are undone. Prices are back to their original values.

This is like an "undo" button for your database operations. The changes never happened.

A Complete Example

Let us walk through a realistic scenario using the products table:

-- First, check the current state
SELECT name, price, quantity FROM products WHERE name IN ('Widget', 'Gadget');

-- Start a transaction to update prices
BEGIN;

-- Increase Widget price by 10%
UPDATE products SET price = price * 1.10 WHERE name = 'Widget';

-- Check the intermediate state (only visible within this transaction)
SELECT name, price FROM products WHERE name = 'Widget';

-- Decrease Gadget price by 5%
UPDATE products SET price = price * 0.95 WHERE name = 'Gadget';

-- Everything looks good, make it permanent
COMMIT;

Now let us see what happens with an error:

BEGIN;

UPDATE products SET quantity = quantity - 100 WHERE name = 'Widget';
-- This might make quantity negative, which we don't want

-- Check the result
SELECT name, quantity FROM products WHERE name = 'Widget';
-- If quantity is negative, we should rollback

ROLLBACK;
-- Quantity is restored to its original value

Error Handling in PostgreSQL Transactions

PostgreSQL has strict error handling within transactions. If any statement in a transaction causes an error, the entire transaction is aborted. You cannot execute any more statements -- you can only issue ROLLBACK.

BEGIN;

UPDATE products SET price = 99.99 WHERE name = 'Widget';
-- Succeeds

UPDATE products SET price = 'not a number' WHERE name = 'Gadget';
-- ERROR: invalid input syntax for type numeric

SELECT * FROM products;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block

ROLLBACK;
-- Now we're back to a clean state

This behavior is different from some other databases (like MySQL) where you can continue after an error. In PostgreSQL, an error poisons the entire transaction.

Practical Patterns

The Check-Then-Act Pattern

BEGIN;

-- Check current state
SELECT quantity FROM products WHERE name = 'Widget';
-- Returns: 50

-- Only proceed if we have enough stock
UPDATE products SET quantity = quantity - 30 WHERE name = 'Widget' AND quantity >= 30;

-- Verify the update affected a row
-- (In application code, you'd check the row count)

COMMIT;

Bulk Updates with Safety Net

BEGIN;

-- Apply a price increase to all products in a category
UPDATE products
SET price = price * 1.15
WHERE category = 'Electronics';

-- Review the changes before committing
SELECT name, price FROM products WHERE category = 'Electronics';

-- If the new prices look correct:
COMMIT;
-- Otherwise:
-- ROLLBACK;

Multi-Table Operations

BEGIN;

-- Mark an order as shipped
UPDATE orders SET status = 'shipped' WHERE id = 42;

-- Decrease the product inventory
UPDATE products SET quantity = quantity - 1 WHERE id = 7;

-- Both changes succeed or both are rolled back
COMMIT;

Transaction Boundaries and DDL

In PostgreSQL, DDL statements (CREATE TABLE, ALTER TABLE, DROP TABLE, etc.) are also transactional. This is unusual -- many other databases auto-commit DDL statements. In PostgreSQL, you can roll back a table creation:

BEGIN;

CREATE TABLE temp_report (
  id SERIAL PRIMARY KEY,
  data TEXT
);

INSERT INTO temp_report (data) VALUES ('test');

-- Changed our mind
ROLLBACK;
-- The table and the inserted row no longer exist

Common Mistakes

Forgetting to COMMIT: If you start a transaction and forget to commit, your changes are not visible to other connections and will be lost when the connection closes.

Long-running transactions: Holding a transaction open for a long time can block other users and consume resources. Keep transactions as short as possible.

Nested BEGIN: PostgreSQL does not support true nested transactions. Issuing BEGIN inside an existing transaction generates a warning but does not create a nested transaction. Use SAVEPOINT instead (covered in the next lesson).

Try It Yourself

1. Start a transaction, update the price of any product in the products table, then check the new price. If it looks good, commit. If not, rollback.

2. Start a transaction that decreases the quantity of one product and increases the quantity of another (simulating a stock transfer). Commit the transaction and verify the results.

3. Start a transaction, attempt an invalid update (e.g., set price to a non-numeric value), then observe the aborted transaction behavior. Issue a ROLLBACK to recover.

4. In a transaction, update all products to set in_stock = false where quantity = 0. Before committing, query the table to verify only the correct rows were affected.