Transactions

Code

-- Begin transaction
BEGIN;

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

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

-- Commit or rollback
COMMIT;
-- ROLLBACK;

-- Set isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM products WHERE id = 1;
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- Savepoint
BEGIN;
INSERT INTO orders (user_id) VALUES (1);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
-- If error occurs
ROLLBACK TO sp1;
COMMIT;

Explanation

Transactions guarantee ACID properties; SAVEPOINT allows partial rollback; isolation levels control concurrency visibility.

More SQL Snippets