SQL transactions: group changes safely

Who this is for: learners who can read basic SQL statements and understand primary and foreign keys. Review SQL constraints if those ideas are new.

What you will learn: how a transaction groups changes, how COMMIT saves them, how ROLLBACK discards uncommitted changes, and why related writes should succeed or fail together.

The transaction examples below were executed with SQLite. Transaction-control details and defaults can vary by database and client; verify the syntax and behavior for the database and driver you use.

Prepare the shared sample data

Run this setup once in a test database. SQLite foreign-key checks must be enabled for the connection before the schema is used. Use one database session/connection when running each transaction block so its statements share the same transaction.

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount DECIMAL(8, 2) NOT NULL CHECK (amount >= 0),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

INSERT INTO customers (customer_id, customer_name) VALUES
    (1, 'Asha'),
    (2, 'Ben'),
    (3, 'Chloe');

INSERT INTO orders (order_id, customer_id, amount) VALUES
    (101, 1, 120.00),
    (102, 1, 80.00),
    (103, 3, 200.00);

Commit related changes together

Suppose a new customer places an order. Creating the customer and its order are related changes: if the customer insert succeeds but the order does not, the data may not represent the intended operation. A transaction lets both inserts be committed together:

BEGIN TRANSACTION;

INSERT INTO customers (customer_id, customer_name)
VALUES (4, 'Dara');

INSERT INTO orders (order_id, customer_id, amount)
VALUES (104, 4, 150.00);

COMMIT;

After the commit, this query includes Dara and the new order:

SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE c.customer_id = 4;

Roll back changes before committing

While a transaction is still open, you can discard its changes with ROLLBACK. This example temporarily changes order 101, checks the uncommitted value in the same connection, then rolls it back:

BEGIN TRANSACTION;

UPDATE orders
SET amount = 999.00
WHERE order_id = 101;

SELECT order_id, amount
FROM orders
WHERE order_id = 101;

ROLLBACK;

The SELECT inside the transaction sees the temporary value. After the rollback, the order amount is restored:

SELECT order_id, amount
FROM orders
WHERE order_id = 101;

A practical transaction workflow

1. Start a transaction before the related writes.
2. Perform and check the changes using the same connection.
3. Commit only if the operation is complete and valid.
4. If the operation should be abandoned, roll back before committing.

A rollback does not undo a transaction that has already been committed. In application code, use the database library's transaction API so exceptions and failures can trigger an appropriate rollback; exact APIs and error behavior depend on the language, framework, and database.

Try it yourself

Using the sample tables, write a transaction that adds customer Eli with ID 5 and order 105 for 75.00. Then check that both rows are present only after committing.

BEGIN TRANSACTION;

INSERT INTO customers (customer_id, customer_name)
VALUES (5, 'Eli');

INSERT INTO orders (order_id, customer_id, amount)
VALUES (105, 5, 75.00);

COMMIT;

Key idea

Use a transaction when a task involves related database changes that should be treated as one unit. COMMIT finishes and saves the work; ROLLBACK discards work that is still uncommitted. Test transaction behavior with your actual database and connection library.