What is a transaction in SQL?

Instruction: Explain commit and rollback, and show why related database changes belong in one transaction.

Context: Understand SQL transactions with a simple transfer example and practical failure-handling advice.

Updated

Example Answer

A transaction groups database operations into one unit of work. I commit when the whole operation succeeds and roll back when it fails. For example, a transfer should update both account balances together. Otherwise, a failure between the two updates could leave the balances inconsistent.

Small example

This synthetic example uses known account IDs. BEGIN is supported by PostgreSQL and SQLite; transaction syntax varies by database.

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 100), (2, 50);
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 20 WHERE account_id = 2;
COMMIT;

The final balances are 80 and 70. In an application, check that the intended rows were updated, handle insufficient funds, and roll back on errors. Transactions do not replace those checks. Concurrent transfers also need a suitable locking or isolation strategy.

Reference

PostgreSQL: transactions

Related Questions