Skip to editor content
learningsql.orglesson 10 of 25

Transactions

Practice exercise →

Imagine transferring money between two bank accounts. You debit one account and credit another. What if the system crashes between those two operations? One account lost money, but the other never received it. Transactions solve this problem by grouping operations into an all-or-nothing unit.

What is a Transaction?

A transaction is a sequence of SQL operations that are treated as a single logical unit of work. Either all operations succeed, or none of them do.

BEGIN, COMMIT, and ROLLBACK

-- Create an accounts table
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL,
    balance REAL NOT NULL
);

INSERT INTO accounts (id, owner, balance)
VALUES
    (1, 'Alice', 1000.00),
    (2, 'Bob', 500.00),
    (3, 'Carol', 750.00);

-- Show initial balances
SELECT * FROM accounts;

-- Begin a transaction: transfer $200 from Alice to Bob
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 200 WHERE owner = 'Alice';
UPDATE accounts SET balance = balance + 200 WHERE owner = 'Bob';

-- Commit the transaction (make changes permanent)
COMMIT;

-- Verify the transfer
SELECT * FROM accounts;
  • BEGIN TRANSACTION (or just BEGIN) starts a new transaction
  • COMMIT saves all changes made during the transaction
  • ROLLBACK undoes all changes made during the transaction

Rolling Back Changes

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER NOT NULL
);

INSERT INTO products (id, name, price, stock)
VALUES
    (1, 'Laptop', 999.99, 50),
    (2, 'Mouse', 24.99, 200),
    (3, 'Keyboard', 79.99, 150);

-- Show initial state
SELECT * FROM products;

-- Start a transaction
BEGIN TRANSACTION;

-- Make some changes
UPDATE products SET price = 0 WHERE name = 'Laptop';
UPDATE products SET stock = -999 WHERE name = 'Mouse';

-- Oops! Those changes are wrong. Roll back.
ROLLBACK;

-- Verify nothing changed
SELECT * FROM products;

ROLLBACK is your undo button. Any changes made after BEGIN are discarded when you ROLLBACK. The database returns to exactly the state it was in before the transaction started.

ACID Properties

Transactions guarantee four fundamental properties, known by the acronym ACID:

Atomicity — All operations in a transaction succeed or fail together. There's no partial completion.

Consistency — A transaction brings the database from one valid state to another. Constraints are enforced.

Isolation — Concurrent transactions don't interfere with each other. Each transaction sees a consistent snapshot.

Durability — Once committed, changes survive system failures.

-- Demonstrate atomicity with constraints
CREATE TABLE bank_accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL,
    balance REAL NOT NULL CHECK(balance >= 0)
);

INSERT INTO bank_accounts (id, owner, balance)
VALUES
    (1, 'Alice', 500.00),
    (2, 'Bob', 300.00);

-- Show initial state
SELECT * FROM bank_accounts;

-- This transaction would violate the CHECK constraint
-- because Alice only has $500 but we're trying to send $600
BEGIN TRANSACTION;
UPDATE bank_accounts SET balance = balance - 600 WHERE owner = 'Alice';
-- The CHECK constraint prevents Alice's balance from going negative
-- In SQLite, the UPDATE above will fail, and we should rollback
ROLLBACK;

-- Balances remain unchanged
SELECT * FROM bank_accounts;

-- A valid transfer that respects the constraint
BEGIN TRANSACTION;
UPDATE bank_accounts SET balance = balance - 200 WHERE owner = 'Alice';
UPDATE bank_accounts SET balance = balance + 200 WHERE owner = 'Bob';
COMMIT;

SELECT * FROM bank_accounts;

The CHECK constraint works together with the transaction to ensure data consistency. If any operation violates a constraint, you can roll back the entire transaction.

SAVEPOINT

SAVEPOINTs let you create checkpoints within a transaction. You can roll back to a specific savepoint without discarding the entire transaction.

-- Create an orders system
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    product TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    status TEXT DEFAULT 'pending'
);

CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    stock INTEGER NOT NULL
);

INSERT INTO inventory (id, product, stock)
VALUES
    (1, 'Widget', 100),
    (2, 'Gadget', 50),
    (3, 'Gizmo', 25);

-- Start a transaction for processing multiple orders
BEGIN TRANSACTION;

-- Process first order successfully
INSERT INTO orders (id, customer, product, quantity, status)
VALUES (1, 'Alice', 'Widget', 10, 'confirmed');
UPDATE inventory SET stock = stock - 10 WHERE product = 'Widget';

-- Create a savepoint before the risky second order
SAVEPOINT before_second_order;

-- Process second order
INSERT INTO orders (id, customer, product, quantity, status)
VALUES (2, 'Bob', 'Gizmo', 30, 'confirmed');
UPDATE inventory SET stock = stock - 30 WHERE product = 'Gizmo';

-- Oops! Bob ordered 30 Gizmos but only 25 are in stock.
-- Roll back only the second order.
ROLLBACK TO before_second_order;

-- Process a corrected second order
INSERT INTO orders (id, customer, product, quantity, status)
VALUES (2, 'Bob', 'Gizmo', 20, 'confirmed');
UPDATE inventory SET stock = stock - 20 WHERE product = 'Gizmo';

-- Commit everything
COMMIT;

-- Verify results
SELECT * FROM orders;
SELECT * FROM inventory;

SAVEPOINTs are especially useful in long transactions where you want to undo part of the work without losing everything. You can create multiple savepoints and roll back to any of them.

Transaction Patterns for Real Applications

The Try-and-Verify Pattern

A common pattern is to perform operations and verify the results before committing.

-- Create employee and payroll tables
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    salary INTEGER NOT NULL
);

CREATE TABLE payroll_log (
    id INTEGER PRIMARY KEY,
    employee_id INTEGER,
    amount INTEGER,
    pay_date TEXT,
    type TEXT
);

INSERT INTO employees (id, name, salary)
VALUES
    (1, 'Alice', 80000),
    (2, 'Bob', 75000),
    (3, 'Carol', 90000);

-- Give everyone a 10% raise and log it
BEGIN TRANSACTION;

-- Apply the raises
UPDATE employees SET salary = ROUND(salary * 1.10);

-- Log each raise
INSERT INTO payroll_log (id, employee_id, amount, pay_date, type)
SELECT
    id,
    id,
    ROUND(salary * 0.10 / 1.10),
    '2025-02-01',
    'raise'
FROM employees;

-- Verify: check the new total salary budget
SELECT
    SUM(salary) AS new_total_budget,
    COUNT(*) AS employees_affected
FROM employees;

-- If everything looks correct, commit
COMMIT;

-- Final state
SELECT e.name, e.salary, p.amount AS raise_amount
FROM employees e
LEFT JOIN payroll_log p ON e.id = p.employee_id;

Batch Operations

Wrapping batch inserts in a transaction dramatically improves performance in SQLite because it avoids committing after every single row.

-- Create a logs table
CREATE TABLE event_log (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    message TEXT,
    created_at TEXT
);

-- Without a transaction, each INSERT would auto-commit (slow)
-- With a transaction, all inserts commit once (fast)
BEGIN TRANSACTION;

INSERT INTO event_log (id, event_type, message, created_at)
VALUES (1, 'LOGIN', 'User alice logged in', '2025-01-15 08:00:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (2, 'PAGE_VIEW', 'alice viewed dashboard', '2025-01-15 08:01:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (3, 'ACTION', 'alice created report', '2025-01-15 08:05:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (4, 'LOGIN', 'User bob logged in', '2025-01-15 08:10:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (5, 'PAGE_VIEW', 'bob viewed settings', '2025-01-15 08:11:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (6, 'LOGOUT', 'User alice logged out', '2025-01-15 08:30:00');

COMMIT;

-- Verify all inserts succeeded
SELECT event_type, COUNT(*) AS count
FROM event_log
GROUP BY event_type
ORDER BY count DESC;

In SQLite, wrapping many inserts in a single transaction can be 10x to 50x faster than individual auto-committed inserts.

Isolation Levels

SQL databases support different isolation levels that control how transactions interact with each other. While SQLite's model is simpler than most (it uses serialized write access), understanding isolation concepts is important for working with any database.

The standard isolation levels from least to most strict are:

  1. READ UNCOMMITTED — Can see uncommitted changes from other transactions (dirty reads)
  2. READ COMMITTED — Only sees committed changes
  3. REPEATABLE READ — Same data reads produce the same results within a transaction
  4. SERIALIZABLE — Full isolation; transactions behave as if they run one at a time

SQLite defaults to SERIALIZABLE for write transactions, which is the safest level. In multi-user databases like PostgreSQL, you may need to choose the appropriate level based on your needs.

Practice Exercises

Try these on your own:

  1. Write a transaction that transfers funds between two accounts, checking for sufficient balance
  2. Use SAVEPOINT to process three orders, rolling back only the ones that exceed inventory
  3. Wrap a batch of 10 INSERT statements in a transaction
  4. Write a transaction that updates prices and verifies the total before committing
  5. Create a transaction with multiple savepoints and selectively roll back to different points

Key Takeaways

  • Transactions group operations into atomic units — all succeed or all fail
  • BEGIN starts a transaction; COMMIT saves changes; ROLLBACK undoes them
  • ACID properties (Atomicity, Consistency, Isolation, Durability) guarantee data reliability
  • SAVEPOINT creates checkpoints within a transaction for partial rollback
  • Wrapping batch operations in transactions improves performance significantly
  • Always use transactions when multiple related operations must succeed together

Pro Tip: In production applications, always wrap related write operations in transactions. A common mistake is updating multiple tables without a transaction, which can leave your data in an inconsistent state if any operation fails.

Next Steps

Transactions keep your data consistent when you control the operations. But what about enforcing rules automatically every time data changes — without relying on application code? The next lesson covers triggers, which let the database itself respond to INSERT, UPDATE, and DELETE events. Triggers are the tool behind audit trails, automatic timestamps, and validation logic that runs no matter how the data is modified.

If you landed here from a search, the rest of the SQL curriculum builds on these foundations. Explore Joins and Relationships for multi-table queries, Window Functions for advanced analytics, or continue below.

Next lesson

Triggers and Automation

Learn SQL triggers to automate database actions. Create audit trails, enforce rules, and run logic on INSERT, UPDATE, and DELETE events.

22 min