SQL Transactions

A transaction groups several SQL statements so they succeed or fail as one unit. Placing an order touches several tables: create the order, add its items, reduce stock. If the system fails halfway, you don't want an order without items or stock that dropped for nothing. Transactions guarantee all or nothing.

Syntax

BEGIN;                 -- start the transaction
  statement 1;
  statement 2;
COMMIT;                -- save everything
-- or
ROLLBACK;              -- undo everything since BEGIN

ACID

Examples

1. COMMIT: place an order

BEGIN;

INSERT INTO orders (order_id, customer_id, order_date, status, payment_method)
VALUES (1025, 2, '2026-02-15', 'Pending', 'UPI');

INSERT INTO order_items (order_item_id, order_id, product_id, quantity, unit_price)
VALUES (39, 1025, 103, 1, 31990.00);

UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 103;

COMMIT;

SELECT o.order_id, oi.product_id, p.stock_qty
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_id = 1025;

Result:

(1 row)

All three changes were saved together. Stock fell from 12 to 11.

2. ROLLBACK: undo a mistake

Someone runs a price update without a WHERE clause, inside a transaction:

BEGIN;

UPDATE products SET price = 0;   -- oops: every product

SELECT COUNT(*) AS free_products FROM products WHERE price = 0;

Result:

(1 row)

Every product is now free, but only inside this transaction. Roll it back:

ROLLBACK;

SELECT COUNT(*) AS free_products FROM products WHERE price = 0;

Result:

(1 row)

Nothing changed. This is why experienced analysts run manual UPDATE and DELETE statements inside BEGIN ... ROLLBACK/COMMIT.

3. Errors cancel the whole transaction

Add a rule that stock can't go negative, then try to sell more than we have:

ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock_qty >= 0);

Result:

Query OK, 0 row(s) affected.

BEGIN;
INSERT INTO orders (order_id, customer_id, order_date, status, payment_method)
VALUES (1026, 5, '2026-02-16', 'Pending', 'Card');
UPDATE products SET stock_qty = stock_qty - 50 WHERE product_id = 115;  -- only 20 in stock
COMMIT;

Error: new row for relation "products" violates check constraint "chk_stock"

The UPDATE failed, so PostgreSQL marks the transaction as failed and discards everything when it ends. End it with ROLLBACK, then check that order 1026 was never created:

ROLLBACK;
SELECT COUNT(*) AS order_1026_rows FROM orders WHERE order_id = 1026;

Result:

(1 row)

4. SAVEPOINT: partial rollback

A savepoint marks a point you can roll back to without losing earlier work:

BEGIN;
UPDATE products SET stock_qty = stock_qty + 100 WHERE product_id = 108;   -- restock bottles
SAVEPOINT after_restock;
UPDATE products SET stock_qty = 0 WHERE product_id = 110;                 -- mistake
ROLLBACK TO SAVEPOINT after_restock;                                      -- undo only the mistake
COMMIT;

SELECT product_id, stock_qty FROM products WHERE product_id IN (108, 110);

Result:

(2 rows)

The restock was kept (300 → 400) and the mistake was undone (face wash still at 500).

Common mistakes

• Forgetting COMMIT. Changes stay invisible to others, and locks are held until the session ends.
• Long-running transactions. Holding a transaction open for minutes blocks other users and grows logs. Keep them short.
• Assuming DDL can always be rolled back. PostgreSQL can roll back CREATE/ALTER/DROP; MySQL and Oracle commit DDL immediately.

Practice question

In one transaction, move 10 units of stock from the Prestige cooker (107) to a new "Display Unit" count by reducing its stock by 10, then decide it was a mistake and undo it. Confirm the stock is unchanged.

BEGIN;
UPDATE products SET stock_qty = stock_qty - 10 WHERE product_id = 107;
ROLLBACK;

SELECT product_id, stock_qty FROM products WHERE product_id = 107;

Key takeaways

• A transaction is all or nothing: BEGIN, then COMMIT or ROLLBACK.
• ACID = atomicity, consistency, isolation, durability.
• An error inside a transaction means none of its changes are saved.
• Run risky manual changes inside a transaction so you can roll them back.