Topic 46 of 52
COMMIT
Overview
COMMIT permanently saves all changes made in the current transaction to the database. Once committed, changes are durable and visible to other transactions. It is the final step in any successful database transaction.
Syntax
sql
-- Explicit transaction with COMMIT
BEGIN; -- or START TRANSACTION in MySQL
INSERT INTO orders (user_id, total) VALUES (42, 1500);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10;
COMMIT; -- Make changes permanent
-- Auto-commit (default behavior without BEGIN)
-- Each statement is automatically committed:
INSERT INTO logs (message) VALUES ('Hello'); -- immediately committed
-- COMMIT in stored procedure
CREATE OR REPLACE PROCEDURE transfer_funds(from_id INT, to_id INT, amt DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
UPDATE accounts SET balance = balance - amt WHERE id = from_id;
UPDATE accounts SET balance = balance + amt WHERE id = to_id;
COMMIT; -- commit within procedure
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
$$;
-- Check if in transaction (PostgreSQL)
SELECT pg_current_xact_id_if_assigned() IS NOT NULL;Common Pitfalls
- In autocommit mode (the default), every statement is automatically committed — you must explicitly BEGIN to start a transaction.
- Long uncommitted transactions hold locks and block other queries — always keep transactions as short as possible.
- Interview tip: COMMIT is irreversible (no undo after COMMIT) — always verify data with a SELECT before COMMIT for critical operations.
Real-World Example
Order placement with explicit commit and error handling:
example
sql
-- Order processing transaction
-- (This pattern is implemented in application code using SQL)
BEGIN;
-- Step 1: Create the order
INSERT INTO orders (user_id, status, total_amount)
VALUES (42, 'processing', 2999.00)
RETURNING id INTO order_id;
-- Step 2: Add order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
(order_id, 101, 2, 999.00),
(order_id, 205, 1, 1001.00);
-- Step 3: Deduct from inventory
UPDATE products
SET stock = stock - 2 WHERE id = 101;
UPDATE products
SET stock = stock - 1 WHERE id = 205;
-- Step 4: Record payment
INSERT INTO payments (order_id, amount, method, status)
VALUES (order_id, 2999.00, 'UPI', 'completed');
-- Step 5: Update order status
UPDATE orders SET status = 'confirmed' WHERE id = order_id;
COMMIT; -- All 5 steps committed atomically
-- If any step failed, the application would call ROLLBACK