Try It Yourself - SyntaxStudy Editor

HTML / CSS / JS

-- Explicit transaction
BEGIN;

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

-- Check for problems before committing
  DO $$
  BEGIN
    IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
      RAISE EXCEPTION 'Insufficient funds';
    END IF;
  END $$;

COMMIT;

-- ROLLBACK on error
BEGIN;
  DELETE FROM orders WHERE id = 99;
  -- Whoops — wrong query
ROLLBACK;

-- Savepoints — partial rollback
BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 100) RETURNING id;
  SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 99, 5);
  -- This item caused a problem
  ROLLBACK TO SAVEPOINT after_order;

-- Continue with a different item
  INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 12, 5);

COMMIT;

-- Serializable isolation for strict consistency
BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT balance FROM accounts WHERE id = 1;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;