Transactions
BEGIN, COMMIT, ROLLBACK, and ACID guarantees
Skills taught: sql.transactions, sql.acid, sql.begin-commit-rollback, sql.savepoints
BEGIN, COMMIT, ROLLBACK
-- Transfer 'money' between accounts
-- (simulated with product stock and order creation)
BEGIN;
-- Step 1: Place an order
INSERT INTO orders (user_id, product_id, quantity, total, ordered_at)
VALUES (1, 2, 1, 799, '2024-07-01');
-- Step 2: Reduce stock
UPDATE products SET stock = stock - 1 WHERE id = 2;
-- If both steps succeed:
COMMIT;
-- If anything failed, we would run:
-- ROLLBACK;
-- Verify stock was reduced
SELECT id, name, stock FROM products WHERE id = 2;
ROLLBACK and SAVEPOINTs
-- Demonstrate ROLLBACK
BEGIN;
INSERT INTO users (name, email, city, joined_at)
VALUES ('Temp User', 'temp@example.com', 'Pune', '2024-07-01');
SELECT COUNT(*) AS user_count FROM users; -- should be 6
ROLLBACK; -- undo the insert
SELECT COUNT(*) AS user_count FROM users; -- back to 5
-- SAVEPOINTs: partial rollback
BEGIN;
SAVEPOINT before_user;
INSERT INTO users (name, email, city, joined_at)
VALUES ('User A', 'a@example.com', 'Delhi', '2024-07-01');
SAVEPOINT before_product;
INSERT INTO products (name, category, price, stock)
VALUES ('New Product', 'Books', 299, 100);
-- Undo only the product insert, keep the user
ROLLBACK TO before_product;
-- Now commit just the user insert
COMMIT;
Frequently Asked Questions
Does every SQL statement run in a transaction?
Yes — in SQLite and most databases, every statement that isn't explicitly wrapped in BEGIN...COMMIT is automatically wrapped in its own implicit transaction. This means a single UPDATE that updates 1000 rows is atomic by default. Explicit transactions let you group multiple statements into one atomic unit.