Transactions in SQL: COMMIT, ROLLBACK and SAVEPOINT Examples

Learn where a SQL transaction begins and ends by tracing one three-account dataset through commit, rollback, savepoint recovery and a lost update.

KnowledgeGate Team

Exam prep & CS education

Updated 10 Nov 20267 min read
Transactions in SQL: COMMIT, ROLLBACK and SAVEPOINT Examples

Two correct UPDATE statements can still leave wrong data if one succeeds and the next fails. Knowing COMMIT and ROLLBACK is not enough unless you can mark the boundary, check each step and predict the state after an error. A three-account transfer succeeds only when the debit, credit and log row cross the same boundary; savepoint recovery and a lost update then change that same state in different ways. CS Fundamentals for Exams & Placements provides the wider DBMS route. The SQL is PostgreSQL-compatible; syntax, default isolation, error handling, DDL behaviour and autocommit controls vary by engine and client, so check your system's documentation.

What a SQL transaction guarantees, and what it does not

A transaction is a sequence of operations treated as one unit. BEGIN starts it, COMMIT makes completed changes durable, and ROLLBACK abandons uncommitted changes. In autocommit mode, each statement may otherwise become its own transaction.

Atomicity keeps our debit, credit and log row together. Consistency requires constraints and correct business rules to move between valid states. Isolation governs concurrent work, while durability retains a successful commit after failure. ACID cannot correct a wrong amount, add a missing rule or repair a boundary around half the work.

Command

Purpose

BEGIN

Start a transaction

COMMIT

Make the transaction's changes permanent

ROLLBACK

Undo uncommitted work and end the transaction

SAVEPOINT name

Mark a recoverable point inside the transaction

ROLLBACK TO SAVEPOINT name

Undo later work but keep the outer transaction active

RELEASE SAVEPOINT name

Remove a savepoint without committing

Run one complete SQL transaction on three accounts

Create the reusable data below. Use Stored Procedures in SQL: PostgreSQL Tutorial with Parameters, Transactions and Examples when the task is to package a transfer behind parameters, validation and ordered row locks. For command-level tracing, keep the raw transaction visible: identify the uncommitted state, the scope of each rollback and the exact durability boundary.

sql
CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    holder_name VARCHAR(30) NOT NULL,
    balance DECIMAL(12, 2) NOT NULL CHECK (balance >= 0)
);

CREATE TABLE transfer_log (
    transfer_id INTEGER PRIMARY KEY,
    from_account INTEGER NOT NULL REFERENCES accounts(account_id),
    to_account INTEGER NOT NULL REFERENCES accounts(account_id),
    amount DECIMAL(12, 2) NOT NULL CHECK (amount > 0)
);

INSERT INTO accounts (account_id, holder_name, balance) VALUES
    (101, 'Asha',   8000.00),
    (202, 'Bharat', 3500.00),
    (303, 'Charu',  2000.00);

The opening invariant is 8000 + 3500 + 2000 = 13500. Transfer 1500.00 from account 101 to 202:

sql
BEGIN;

UPDATE accounts
SET balance = balance - 1500.00
WHERE account_id = 101 AND balance >= 1500.00;

UPDATE accounts
SET balance = balance + 1500.00
WHERE account_id = 202;

INSERT INTO transfer_log
    (transfer_id, from_account, to_account, amount)
VALUES (1, 101, 202, 1500.00);

COMMIT;

Require exactly one affected row from each UPDATE before inserting the log and committing.

Account

Before

After commit

101, Asha

8000.00

6500.00

202, Bharat

3500.00

5000.00

303, Charu

2000.00

2000.00

Total

13500.00

13500.00

The committed state is 6500 + 5000 + 2000 = 13500, with log row 1. The CHECK rejects a negative balance. The guarded debit turns insufficient funds into zero affected rows, which the caller must detect.

Use ROLLBACK when a later statement cannot finish

Now attempt to move 900.00 from Asha to missing account 999:

sql
BEGIN;
UPDATE accounts
SET balance = balance - 900.00
WHERE account_id = 101 AND balance >= 900.00;

UPDATE accounts
SET balance = balance + 900.00
WHERE account_id = 999;
ROLLBACK;

The debit changes Asha's uncommitted balance from 6500.00 to 5600.00, but the credit affects zero rows. SQL does not infer what that means to the business. The caller must check it, issue ROLLBACK, and skip the log insert.

State

Asha

Bharat

Charu

Total

Before transaction

6500.00

5000.00

2000.00

13500.00

Temporary after debit

5600.00

5000.00

2000.00

12600.00

After rollback

6500.00

5000.00

2000.00

13500.00

The temporary 12600.00 is not committed data. After rollback, the balances return to 6500/5000/2000, and transfer_log still contains only transfer 1.

SQL transaction state flow showing a failed transfer: accounts 101, 202, and 303 total 13,500, debit 900, rollback restores balances, and no log is written.

Use SAVEPOINT to undo one part without losing earlier work

Start again from 6500/5000/2000. One outer transaction now handles a valid transfer, an invalid attempt and a valid alternative:

  1. Begin, move 600.00 from Asha to Bharat, and insert log row (2, 101, 202, 600.00). The state becomes 5900/5600/2000, still totalling 13500. Set SAVEPOINT after_bharat.

  2. Debit another 400.00 from Asha. The temporary state is 5500/5600/2000. Crediting account 999 affects zero rows.

  3. Run ROLLBACK TO SAVEPOINT after_bharat. Only step 2 disappears, so the still-uncommitted state returns to 5900/5600/2000; log row 2 remains inside the outer transaction.

  4. Take the valid alternative: debit Asha by 400.00, credit Charu by 400.00, and insert (3, 101, 303, 400.00). Run RELEASE SAVEPOINT after_bharat, then COMMIT.

The final balances are Asha 5500.00, Bharat 5600.00 and Charu 2400.00. The invariant holds because 5500 + 5600 + 2400 = 13500, and the log now has rows 1, 2 and 3.

A savepoint is a marker inside one transaction, not an independent nested commit. A later full rollback of the outer transaction would also remove both transfers performed in this batch.

Isolation prevents concurrent transactions from silently overwriting work

Take Charu's committed balance of 2400.00. Sessions A and B both read 2400. A calculates 2400 + 300 = 2700, while B calculates 2400 - 200 = 2200. A writes 2700; B then writes its stale result, 2200. The actual final value is 2200, although either serial order gives 2400 + 300 - 200 = 2500. A's 300 change has been lost.

For a direct adjustment, let the database update the stored value atomically with SET balance = balance + 300.00 or a guarded SET balance = balance - 200.00. If later logic genuinely depends on a value read earlier, use SELECT ... FOR UPDATE in PostgreSQL, keep the transaction short, and retry deadlock or serialization failures as required by the engine and client.

Transactions and Concurrency Control in DBMS develops isolation levels, schedules, locking and serializability further. Default isolation and anomaly prevention differ between engines, so BEGIN alone does not prevent every race.

Two-session lost-update timeline showing Charu’s balance changing from 2400 to stale 2200 instead of serial 2500, with atomic adjustments ending at 2500.

Common SQL transaction errors and the repair for each

Error

Consequence

Repair

Autocommit left on during a multi-statement transfer

Debit may commit before credit

Start an explicit transaction and verify the mode

First UPDATE placed before BEGIN

Rollback cannot undo that committed statement

Place the boundary before the first state change

Zero affected rows ignored

Missing account or insufficient funds passes silently

Require the expected row count

COMMIT executed in a cleanup block

Failed work can be committed

Commit only on the success path and rollback on the error path

Do not assume every statement error rolls back the whole transaction; check the engine and handle the failure. A savepoint is not a nested commit, so finish the outer transaction explicitly. Gather user input and external responses before starting, keeping the database unit short. Check the engine's transaction rules before mixing DDL with data changes; separate them if uncertain.

Return a pooled connection only after an explicit commit or rollback. An open transaction can contaminate the next request using it.

How exams and interviews test SQL transactions, with three exercises

Common questions ask you to trace balances after COMMIT, ROLLBACK or ROLLBACK TO SAVEPOINT; match a failure to an ACID property; identify a lost update; place the transaction boundary; or spot a missing row-count check. Practise with DBMS Transaction MCQs: 12 Solved (ACID, Locking).

  1. State trace: Start at 6500; subtract 900; create savepoint s1; subtract 600; roll back to s1; add 200; commit. The trace is 6500 - 900 = 5600, temporary 5600 - 600 = 5000, rollback to 5600, then 5600 + 200 = 5800.

  2. Invariant: The starting total is 13500. A 900 debit commits but its credit does not. The broken total is 13500 - 900 = 12600. Roll back the whole transfer to restore 13500.

  3. Concurrency: Both sessions read 2400; one writes 2700, the other writes stale 2200. The actual final is 2200, the correct serial final is 2500, and the anomaly is a lost update. Explain why atomic arithmetic or row locking is needed, rather than merely naming isolation.

SQL transactions: the short version and the next useful step

Begin before the first write, validate every affected-row count, commit only after all required steps succeed, rollback on any failure, and use a savepoint only when partial recovery is a deliberate business rule. A correct single-session script can still need an isolation or locking plan.

Use CS Fundamentals for Placements by Sanchit Sir to prepare DBMS and other core-CS concepts for technical interviews. Then rerun the three-account script, stop after every statement to predict the visible and uncommitted state, and solve the three exercises without looking at the arithmetic.