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

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 |
|---|---|
| Start a transaction |
| Make the transaction's changes permanent |
| Undo uncommitted work and end the transaction |
| Mark a recoverable point inside the transaction |
| Undo later work but keep the outer transaction active |
| 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.
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:
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:
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.

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:
Begin, move
600.00from Asha to Bharat, and insert log row(2, 101, 202, 600.00). The state becomes5900/5600/2000, still totalling13500. SetSAVEPOINT after_bharat.Debit another
400.00from Asha. The temporary state is5500/5600/2000. Crediting account999affects zero rows.Run
ROLLBACK TO SAVEPOINT after_bharat. Only step 2 disappears, so the still-uncommitted state returns to5900/5600/2000; log row 2 remains inside the outer transaction.Take the valid alternative: debit Asha by
400.00, credit Charu by400.00, and insert(3, 101, 303, 400.00). RunRELEASE SAVEPOINT after_bharat, thenCOMMIT.
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.

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 | 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 |
| 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).
State trace: Start at
6500; subtract900; create savepoints1; subtract600; roll back tos1; add200; commit. The trace is6500 - 900 = 5600, temporary5600 - 600 = 5000, rollback to5600, then5600 + 200 = 5800.Invariant: The starting total is
13500. A900debit commits but its credit does not. The broken total is13500 - 900 = 12600. Roll back the whole transfer to restore13500.Concurrency: Both sessions read
2400; one writes2700, the other writes stale2200. The actual final is2200, the correct serial final is2500, 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.
Keep learning
Stored Procedures in SQL: PostgreSQL Tutorial with Parameters, Transactions and Examples
Build and test a PostgreSQL procedure that validates a transfer, locks accounts, updates balances and writes an audit row. Then trace rollback and INOUT behaviour.
SQL Tutorial: The Complete Learning Path from First Query to Transactions
Learn SQL in a deliberate order, using one small dataset to practise table creation, filtering, joins, grouping, subqueries and transactions.
ISRO CS DBMS PYQs: Solved Patterns for Keys, Normalisation, SQL and Transactions
Learn to classify and solve representative ISRO CS DBMS problems through closures, normal forms, SQL row tracing, index I/O and precedence graphs.
DBMS Interview Questions: Keys, Normalisation and Transactions Through One Worked Schema
Build defensible DBMS interview answers by tracing keys, normal forms, ACID and isolation through one university-enrolment database.





