Transactions

A transaction is a sequence of one or more SQL operations executed as a single logical unit of work: either all of them take effect (COMMIT) or none of them do (ROLLBACK).

Concept

A transaction is a sequence of one or more SQL operations executed as a single logical unit of work: either all of them take effect (COMMIT) or none of them do (ROLLBACK).

Classic example: transferring money requires debiting one account and crediting another. If the system crashes between the two updates, money would disappear — unless both run in one transaction.

Why It Matters

Transactions protect data correctness in the face of failures (crashes, errors, power loss) and concurrency (many users changing data at once). Every serious application relies on them: payments, bookings, inventory, order processing.

ACID Properties

How databases implement it All or nothing. A failure rolls back every change in the transaction.

Undo logs / rollback segments / versioned rows A transaction moves the database from one valid state to another; all constraints (PK, FK, CHECK, UNIQUE), triggers, and rules hold at commit.

Constraint checking, plus correct application logic Concurrent transactions do not see each other's intermediate states (to the degree specified by the isolation level).

Locks and/or MVCC (multi-version concurrency control) Once committed, changes survive crashes and power failures.

Write-ahead log (WAL / redo log / transaction log) flushed to disk at commit; recovery replays the log Write-ahead logging in one picture:

UPDATE accounts ...    →  change recorded in log buffer
COMMIT                 →  log flushed (fsync) to disk  ← durability point
(later)                →  modified data pages written to data files (checkpoint)
crash + restart        →  redo committed changes from log, undo uncommitted ones

Syntax

BEGIN; / START TRANSACTION; START TRANSACTION; / BEGIN; BEGIN TRANSACTION; / BEGIN TRAN; Implicit (first DML starts one) BEGIN; / BEGIN TRANSACTION; COMMIT; / END; SAVEPOINT s1; SAVE TRANSACTION s1; Roll back to savepoint ROLLBACK TO SAVEPOINT s1; ROLLBACK TRANSACTION s1; ROLLBACK TO s1; RELEASE SAVEPOINT s1;

Autocommit: most clients (PostgreSQL, MySQL, SQL Server, SQLite) run each statement in its own transaction unless you explicitly start one. Oracle and SQL*Plus do not autocommit DML by default — you must COMMIT.

Example

21.1 Banking transfer Transfer 20,000 from ACC-100 to ACC-200:

Now try to transfer 40,000 (more than the balance):

Balances remain 30000 / 30000. Atomicity and consistency preserved.

Note on error behavior: in PostgreSQL, any error aborts the whole transaction. In MySQL and SQL Server (by default), a failing statement is rolled back but the transaction stays open, so earlier statements could still be committed — your code must detect the error and ROLLBACK explicitly. In SQL Server, SET XACT_ABORT ON makes most errors roll back the entire transaction.

21.2 Robust transfer with error handling (SQL Server T-SQL) PostgreSQL equivalent logic lives in a function/procedure (Module 25) or in application code:

21.3 SAVEPOINT and ROLLBACK TO SAVEPOINT Savepoints allow partial rollback inside a transaction.

After COMMIT: order 2001 exists with a single Laptop line. The Standing Desk line never became visible to anyone.

Rolling back to a savepoint does not end the transaction; the savepoint remains usable. A full ROLLBACK undoes everything including work before the savepoint.

21.4 Concurrency-safe updates (preventing lost updates) A deadlock occurs when two transactions each hold a lock the other needs:

The database detects the cycle and aborts one transaction (the "victim") with a deadlock error. Prevention:

Access resources in a consistent order (e.g., always lock the lower account number first).

Keep transactions short; never wait for user input inside a transaction.

Use appropriate indexes so updates lock fewer rows.

Retry the victim transaction in application code.

CREATE TABLE bank_accounts (
    account_no  VARCHAR(20)   PRIMARY KEY,
    holder      VARCHAR(100)  NOT NULL,
    balance     DECIMAL(15,2) NOT NULL CHECK (balance >= 0)
);

CREATE TABLE bank_transactions (
    txn_id      INT           PRIMARY KEY,
    from_acct   VARCHAR(20)   NOT NULL REFERENCES bank_accounts(account_no),
    to_acct     VARCHAR(20)   NOT NULL REFERENCES bank_accounts(account_no),
    amount      DECIMAL(15,2) NOT NULL CHECK (amount > 0),
    txn_time    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO bank_accounts VALUES ('ACC-100', 'Ananya', 50000.00), ('ACC-200', 'Ben', 10000.00);

BEGIN;   -- START TRANSACTION (MySQL) / BEGIN TRANSACTION (SQL Server)

UPDATE bank_accounts SET balance = balance - 20000 WHERE account_no = 'ACC-100';
UPDATE bank_accounts SET balance = balance + 20000 WHERE account_no = 'ACC-200';
INSERT INTO bank_transactions (txn_id, from_acct, to_acct, amount)
VALUES (1, 'ACC-100', 'ACC-200', 20000);

COMMIT;

BEGIN;
UPDATE bank_accounts SET balance = balance - 40000 WHERE account_no = 'ACC-100';
-- ERROR: new row violates check constraint "bank_accounts_balance_check"
-- PostgreSQL: the transaction is now aborted; any further statement fails until ROLLBACK
ROLLBACK;

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE bank_accounts SET balance = balance - 20000 WHERE account_no = 'ACC-100';
    IF @@ROWCOUNT = 0 THROW 50001, 'Source account not found', 1;

    UPDATE bank_accounts SET balance = balance + 20000 WHERE account_no = 'ACC-200';
    IF @@ROWCOUNT = 0 THROW 50002, 'Target account not found', 1;

    INSERT INTO bank_transactions (txn_id, from_acct, to_acct, amount)
    VALUES (2, 'ACC-100', 'ACC-200', 20000);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;   -- re-raise to the caller
END CATCH;

try:
    conn.begin()
    execute(debit)  → check rowcount == 1
    execute(credit) → check rowcount == 1
    execute(insert log)
    conn.commit()
except:
    conn.rollback()
    raise

BEGIN;

INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
VALUES (2001, 5, DATE '2026-04-01', 'PENDING', 75000.00);

SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (2001, 106, 1, 35000.00);   -- Standing Desk, but stock is 0 → business decides to undo this line

ROLLBACK TO SAVEPOINT order_created;   -- undo only the item insert

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (2001, 101, 1, 75000.00);   -- Laptop instead

COMMIT;

-- Unsafe read-modify-write in application code:
--   balance = SELECT balance FROM bank_accounts WHERE account_no = 'ACC-100';
--   UPDATE bank_accounts SET balance = <balance - 500> WHERE account_no = 'ACC-100';
-- Two sessions doing this concurrently can lose one update.

-- Safe option 1: atomic update expression
UPDATE bank_accounts SET balance = balance - 500
WHERE account_no = 'ACC-100' AND balance >= 500;     -- check rowcount = 1

-- Safe option 2: pessimistic lock
BEGIN;
SELECT balance FROM bank_accounts WHERE account_no = 'ACC-100' FOR UPDATE;   -- locks the row
-- (SQL Server: SELECT ... FROM bank_accounts WITH (UPDLOCK, ROWLOCK) WHERE ...)
UPDATE bank_accounts SET balance = balance - 500 WHERE account_no = 'ACC-100';
COMMIT;

-- Safe option 3: optimistic concurrency with a version column
UPDATE bank_accounts
SET balance = 29500, version = version + 1
WHERE account_no = 'ACC-100' AND version = 7;        -- 0 rows updated → someone else changed it; retry

T1: UPDATE accounts ... WHERE account_no = 'A'   (locks A)
T2: UPDATE accounts ... WHERE account_no = 'B'   (locks B)
T1: UPDATE accounts ... WHERE account_no = 'B'   (waits for T2)
T2: UPDATE accounts ... WHERE account_no = 'A'   (waits for T1)  → deadlock

-- Consistent ordering for transfers
UPDATE bank_accounts SET balance = balance + CASE account_no WHEN 'ACC-100' THEN -500 ELSE 500 END
WHERE account_no IN ('ACC-100', 'ACC-200');   -- single statement; or lock rows ordered by account_no first

Expected Result

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 101;
SAVEPOINT s1;
UPDATE products SET stock = stock - 100 WHERE product_id = 101;
ROLLBACK TO SAVEPOINT s1;
COMMIT;
SELECT product_id, stock FROM products WHERE product_id = 101;

Common Mistakes

Forgetting to COMMIT (Oracle, or when autocommit is off) — changes are invisible to others and locks are held.

Long-running transactions holding locks (and in PostgreSQL, preventing vacuum cleanup).

Assuming a failed statement in MySQL/SQL Server rolls back the entire transaction.

Mixing DDL into transactions on MySQL/Oracle (DDL implicitly commits).

Read-modify-write logic without locking or atomic updates (lost updates).

Not handling deadlock errors with retries.

Opening transactions across user interactions (e.g., waiting for a web form).

Best Practices

Keep transactions as short as possible, containing only related changes.

Always handle errors: ROLLBACK on failure, and retry on deadlocks/serialization failures.

Use atomic UPDATE ... SET x = x - n WHERE ... AND x >= n patterns.

Lock rows in a consistent order.

Check affected row counts.

Interview Relevance

Very high: "What is a transaction?", "Explain ACID with an example", "COMMIT vs ROLLBACK?", "What is a savepoint?", "What is a deadlock and how do you prevent it?", "Optimistic vs pessimistic locking?", "How does durability work?".

Practice Questions

Write a transaction that moves 5 units of stock from product 101 to a new product row and logs the movement.

Explain what happens if the database crashes after the first UPDATE of a transfer but before COMMIT.

Demonstrate a savepoint that undoes a single failed item in a multi-item order.

Describe two strategies for preventing lost updates.

Why is waiting for user input inside a transaction dangerous?