SQLTransactions and Indexesadvanced26 min read

COMMIT and ROLLBACK

Learn how COMMIT, ROLLBACK, and SAVEPOINT control database transactions. Understand transaction boundaries, partial rollback, error recovery, PostgreSQL behavior, and safe transaction patterns in backend applications.

On This Page (Table of Contents)

In this lesson, you will learn

✓Explain how COMMIT and ROLLBACK control a transaction
✓Persist successful transaction changes using COMMIT
✓Undo uncommitted transaction changes using ROLLBACK
✓Create and use SAVEPOINTs for partial rollback
✓Explain what happens when a statement fails inside a PostgreSQL transaction
✓Use BEGIN, COMMIT, and ROLLBACK in practical database workflows
✓Understand transaction atomicity and its relationship to ACID
✓Avoid common transaction mistakes in application code
✓Recognize the difference between rolling back database changes and undoing external side effects

Introduction to COMMIT and ROLLBACK

## What is a database transaction?

A transaction is a group of database operations treated as one logical unit of work.

Imagine transferring ₹1,000 from Account A to Account B. The transaction must:

1. Subtract ₹1,000 from Account A.
2. Add ₹1,000 to Account B.

If the first operation succeeds but the second fails, the database should not leave the transfer half-completed.

COMMIT and ROLLBACK help control the outcome of a transaction.

## What does COMMIT do?

COMMIT completes the current transaction successfully.

In PostgreSQL, after a transaction commits, its changes are committed and can be observed by other transactions according to the applicable isolation rules.

Example:

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

COMMIT;

The balance change is committed if the transaction succeeds.

## What does ROLLBACK do?

ROLLBACK aborts the current transaction and discards its uncommitted changes.

Example:

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

ROLLBACK;

The update is undone as part of the transaction rollback.

## Why do these commands matter?

They help preserve data consistency when several operations must succeed or fail together.

Remember:

- BEGIN starts an explicit transaction.
- COMMIT completes it successfully.
- ROLLBACK aborts it.
- SAVEPOINT allows recovery to an earlier point within the transaction.

Important: COMMIT does not mean that every external action associated with your application is permanent or reversible. It controls the database transaction, not actions such as sending an email or calling another service.

Core Syntax and Transaction Boundaries

## Basic syntax

BEGIN;

-- Execute one or more SQL statements.

COMMIT;

Or, when the transaction must be abandoned:

BEGIN;

-- Execute one or more SQL statements.

ROLLBACK;

In PostgreSQL, BEGIN starts an explicit transaction block. COMMIT and ROLLBACK end that block.

## Example: Commit a change

BEGIN;

UPDATE products
SET price = price + 100
WHERE product_id = 1;

COMMIT;

The price change is committed if the statement succeeds.

## Example: Roll back a change

BEGIN;

UPDATE products
SET price = price + 100
WHERE product_id = 1;

ROLLBACK;

The uncommitted price change is discarded.

## What happens without an explicit BEGIN?

PostgreSQL normally executes each standalone statement in its own implicit transaction when it is not already inside an explicit transaction block.

For multi-statement workflows, use an explicit transaction when the statements must succeed or fail together.

## Important distinction

A transaction is not a substitute for validation.

Before committing important changes, use appropriate constraints, verify that the expected rows were affected, and handle errors correctly in the application.

Commit and Roll Back a Product Update

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE TEMP TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);
 
INSERT INTO products VALUES
    (1, 'Keyboard', 1200.00),
    (2, 'Mouse', 500.00);
 
-- Transaction 1: commit the update.
BEGIN;
 
UPDATE products
SET price = 1300.00
WHERE product_id = 1;
 
COMMIT;
 
SELECT product_id, product_name, price
FROM products
ORDER BY product_id;
 
-- Transaction 2: update, then undo the update.
BEGIN;
 
UPDATE products
SET price = 600.00
WHERE product_id = 2;
 
ROLLBACK;
 
SELECT product_id, product_name, price
FROM products
ORDER BY product_id;

Output

The first transaction commits the keyboard price change. The second transaction changes the mouse price temporarily, then ROLLBACK discards that update. The temporary table is used to make this demonstration self-contained.

Practical Example: A Bank Transfer

## Why multiple statements belong in one transaction

A bank transfer consists of related changes. If only one account is updated, the data becomes inconsistent with the intended transfer.

The transaction should:

1. Subtract the transfer amount from the sender.
2. Add the same amount to the receiver.
3. Commit only if the required operations succeed.

The following example demonstrates the transaction structure using a temporary table.

For a real banking system, additional safeguards are necessary, including sufficient-funds checks, concurrent-update protection, validation, authorization, and reliable error handling.

The example deliberately rolls back a transfer to demonstrate that neither balance change remains.

Transfer Money and Roll Back

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TEMP TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    account_name TEXT NOT NULL,
    balance NUMERIC(12, 2) NOT NULL
        CHECK (balance >= 0)
);
 
INSERT INTO accounts VALUES
    (1, 'Account A', 5000.00),
    (2, 'Account B', 2000.00);
 
BEGIN;
 
UPDATE accounts
SET balance = balance - 1000.00
WHERE account_id = 1;
 
UPDATE accounts
SET balance = balance + 1000.00
WHERE account_id = 2;
 
-- Simulate abandoning the transfer.
ROLLBACK;
 
SELECT account_id, account_name, balance
FROM accounts
ORDER BY account_id;

Output

Both balance updates are part of the same explicit transaction. ROLLBACK discards both changes, leaving the original balances. In a real application, COMMIT would be used only after the transfer's validation and required operations had succeeded.

Using SAVEPOINT for Partial Rollback

## What is a SAVEPOINT?

A SAVEPOINT marks a position inside the current transaction.

It lets you undo some operations without discarding all earlier changes.

Syntax:

BEGIN;

SAVEPOINT savepoint_name;

-- Execute additional statements.

ROLLBACK TO SAVEPOINT savepoint_name;

COMMIT;

ROLLBACK TO SAVEPOINT undoes the work performed after the specified savepoint. Earlier changes in the transaction remain pending.

## Important commands

**SAVEPOINT name**

Creates a named recovery point in the current transaction.

**ROLLBACK TO SAVEPOINT name**

Undoes changes made after the savepoint. The transaction remains active, allowing further work.

**RELEASE SAVEPOINT name**

Removes the savepoint. It does not commit the transaction.

## A simple example

BEGIN;

UPDATE products
SET price = 1300
WHERE product_id = 1;

SAVEPOINT before_second_update;

UPDATE products
SET price = 700
WHERE product_id = 2;

ROLLBACK TO SAVEPOINT before_second_update;

COMMIT;

The first update remains and is committed. The second update is undone.

## PostgreSQL detail

Rolling back to a savepoint removes savepoints created after that savepoint. The target savepoint remains available until it is released or the transaction ends.

SAVEPOINT does not create an independent nested transaction. The outer transaction still determines whether the retained changes are ultimately committed or rolled back.

Undo One Update but Keep Another

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE TEMP TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);
 
INSERT INTO products VALUES
    (1, 'Keyboard', 1200.00),
    (2, 'Mouse', 500.00);
 
BEGIN;
 
-- This update should be retained.
UPDATE products
SET price = 1300.00
WHERE product_id = 1;
 
SAVEPOINT before_mouse_update;
 
-- This update should be undone.
UPDATE products
SET price = 700.00
WHERE product_id = 2;
 
ROLLBACK TO SAVEPOINT before_mouse_update;
 
-- The savepoint is no longer needed.
RELEASE SAVEPOINT before_mouse_update;
 
-- Commit the retained keyboard update.
COMMIT;
 
SELECT product_id, product_name, price
FROM products
ORDER BY product_id;

Output

The keyboard update happened before the savepoint, so rolling back to the savepoint does not undo it. The mouse update happened after the savepoint and is undone. COMMIT then persists the remaining change.

Transaction States and Error Handling

## What if a statement fails?

A statement can fail because of a constraint violation, invalid SQL, or another database error.

In PostgreSQL, when an ordinary statement fails inside an explicit transaction block, the transaction generally enters an aborted state. Subsequent ordinary statements cannot proceed successfully until the transaction is rolled back or rolled back to a usable savepoint.

For example:

BEGIN;

INSERT INTO products(product_id, product_name, price)
VALUES (1, 'Keyboard', 1200);

-- Assume product_id = 1 already exists.
INSERT INTO products(product_id, product_name, price)
VALUES (1, 'Mouse', 500);

-- The transaction is now aborted.
-- This statement cannot execute successfully.
SELECT * FROM products;

ROLLBACK;

## Recovering with a savepoint

If you create a savepoint before a statement that may fail, you can roll back to that savepoint and continue the transaction.

BEGIN;

SAVEPOINT before_optional_insert;

-- Suppose this statement fails.
INSERT INTO products(product_id, product_name, price)
VALUES (1, 'Duplicate', 500);

-- After the error, recover to the savepoint.
ROLLBACK TO SAVEPOINT before_optional_insert;

-- The transaction can now continue.
SELECT * FROM products;

COMMIT;

Run this pattern in a controlled test where the insert is known to fail.

## Application-level error handling

In backend code, the application should:

1. Begin a transaction.
2. Execute the required statements.
3. Commit if all required operations succeed.
4. Roll back if an error prevents the operation from completing.
5. Release database connections safely.

If the connection fails during COMMIT, the application may not know whether the transaction committed. Blindly retrying a non-idempotent operation can cause duplicate effects. Use an appropriate recovery or idempotency strategy for important workflows.

Real-World Architecture and Best Practices

## Transactions in backend applications

Backend services often group related SQL statements into a transaction.

Examples include:

- Creating an order and its order items.
- Updating an inventory record while recording a shipment.
- Transferring money between accounts.
- Saving a business record and its required audit entry.

## A typical transaction workflow

1. Obtain a database connection.
2. Begin a transaction on that connection.
3. Execute all related SQL statements using the same connection.
4. Check required results and constraints.
5. Commit on success.
6. Roll back on failure.
7. Release the connection.

Use the transaction API provided by your database driver or ORM. Do not assume that two statements sent through unrelated pooled connections belong to the same transaction.

## Best practices

- Keep transactions as short as practical.
- Validate inputs before performing database changes.
- Use database constraints to protect data integrity.
- Check whether updates affected the expected number of rows.
- Handle errors and rollback paths deliberately.
- Use parameterized queries to avoid SQL injection.
- Understand transaction isolation and concurrency behavior.
- Avoid network requests or long user interactions while holding a transaction open.
- Do not assume ROLLBACK can undo an email, an HTTP request, or a message already sent to an external system.
- Test failure paths as well as successful commits.

For workflows involving a database and an external service, patterns such as a transactional outbox can help coordinate reliable delivery. A database rollback alone cannot reverse an external side effect.

Common Mistakes

## Mistake 1: Forgetting to commit

A developer executes updates inside an explicit transaction but never commits them. The changes remain uncommitted until the transaction ends or the connection is closed, and other transactions may not see them.

**Correction:** Commit when the complete operation succeeds.

## Mistake 2: Committing after a failed operation

In PostgreSQL, an ordinary statement error can leave the explicit transaction in an aborted state. A subsequent COMMIT does not turn the failed transaction into a successful one; PostgreSQL rolls back the aborted transaction.

**Correction:** Handle the error and explicitly roll back or roll back to a savepoint before continuing.

## Mistake 3: Using ROLLBACK after COMMIT to undo changes

Once a transaction has committed, issuing ROLLBACK in a new transaction does not undo the previous commit.

**Correction:** Use a new compensating transaction, a controlled correction, or a recovery procedure when committed data must be changed.

## Mistake 4: Assuming SAVEPOINT commits data

SAVEPOINT only establishes a recovery point inside the current transaction.

**Correction:** Commit the outer transaction when the remaining changes are ready to be committed.

## Mistake 5: Confusing RELEASE SAVEPOINT with COMMIT

RELEASE SAVEPOINT removes a savepoint. It does not commit the transaction.

**Correction:** Use COMMIT to finish the transaction.

## Mistake 6: Assuming database rollback undoes external actions

If an application sends an email and then rolls back a database transaction, the email is not automatically recalled.

**Correction:** Coordinate external effects with an appropriate application design, such as a transactional outbox where suitable.

## Mistake 7: Holding a transaction open too long

Long-running transactions can retain locks, increase contention, and interfere with database maintenance.

**Correction:** Keep transactions short and avoid waiting for user input or slow external services while a transaction is open.

## Mistake 8: Retrying a transaction without considering duplicate effects

A connection failure can make the result of COMMIT uncertain to the client. Retrying blindly may duplicate an operation.

**Correction:** Use idempotency keys, unique constraints, or a suitable recovery strategy for operations that must not be duplicated.

Practice Exercises

Use PostgreSQL to solve the following exercises.

### Exercise 1: Commit an update

Create a temporary products table, update a product's price inside a transaction, and commit the change. Verify the final value.

### Exercise 2: Roll back an update

Update a product's price inside a transaction and execute ROLLBACK. Verify that the original price remains.

### Exercise 3: Transfer between accounts

Create two accounts and perform a transfer using two UPDATE statements in one transaction. Commit the transfer and verify both balances.

### Exercise 4: Simulate a failed transfer

Repeat the transfer example but roll back the transaction. Verify that neither account balance changes.

### Exercise 5: Use a savepoint

Update one product, create a savepoint, update another product, roll back to the savepoint, and commit. Verify which update remains.

### Exercise 6: Release a savepoint

Create a savepoint, make an update, release the savepoint, and commit. Explain why releasing the savepoint does not undo the update.

### Exercise 7: Recover from a constraint error

Create a table with a primary key. Insert a row, create a savepoint, and attempt to insert a duplicate key. Roll back to the savepoint and complete the transaction.

### Exercise 8: Roll back the whole transaction

Make two updates in one transaction and roll back. Explain why both changes are undone.

### Exercise 9: Investigate transaction scope

Begin a transaction on one database connection and execute a statement through a different connection. Explain why the second statement does not automatically belong to the first transaction.

### Exercise 10: Design backend error handling

Write pseudocode for a backend service that begins a transaction, inserts an order, inserts its items, commits on success, rolls back on failure, and releases the connection safely.

Explain how the service should respond if the connection is lost while COMMIT is being processed.

Knowledge Check

### Question 1

What does COMMIT do?

A. Deletes a table.
B. Completes the current transaction successfully.
C. Creates a savepoint.
D. Reverses all previous transactions.

**Answer: B.** COMMIT completes the current transaction successfully.

### Question 2

What does ROLLBACK do inside an explicit transaction?

A. Discards the transaction's uncommitted changes.
B. Permanently saves every update.
C. Creates a new table.
D. Releases a savepoint only.

**Answer: A.** ROLLBACK aborts the current transaction.

### Question 3

What is the purpose of SAVEPOINT?

A. Permanently save the transaction.
B. Create a point for partial rollback.
C. Create a database index.
D. End the transaction.

**Answer: B.** A savepoint provides a point to which the transaction can roll back.

### Question 4

What does ROLLBACK TO SAVEPOINT do?

A. Always rolls back the entire transaction.
B. Commits changes made before the savepoint.
C. Undoes changes made after the savepoint while retaining earlier pending changes.
D. Drops the table.

**Answer: C.** The outer transaction remains active after a successful rollback to a savepoint.

### Question 5

Does RELEASE SAVEPOINT commit a transaction?

A. Yes.
B. No.
C. Only when the table has a primary key.
D. Only in read-only transactions.

**Answer: B.** RELEASE SAVEPOINT removes the named savepoint; it does not commit the transaction.

### Question 6

Can ROLLBACK in a new transaction undo an earlier committed transaction?

A. Yes, automatically.
B. No.
C. Only if a savepoint exists.
D. Only if the earlier transaction updated one row.

**Answer: B.** A committed transaction is not undone by a later ROLLBACK. A separate corrective transaction or recovery mechanism is needed.

### Question 7

What generally happens after an ordinary SQL statement fails inside a PostgreSQL explicit transaction?

A. The transaction automatically commits.
B. The transaction enters an aborted state until recovery or rollback.
C. Every database table is dropped.
D. The failed statement is silently ignored and the transaction always continues.

**Answer: B.** Roll back the transaction or roll back to a usable savepoint before continuing.

### Question 8

Can a database ROLLBACK undo an email already sent by the application?

A. Yes, always.
B. No, not by itself.
C. Only when SAVEPOINT is used.
D. Only when COMMIT is issued first.

**Answer: B.** Database transactions do not automatically reverse external side effects.

### Question 9

Why should a transaction usually be kept short?

A. To avoid using SQL.
B. To reduce the time locks and other transaction-related resources are held.
C. To prevent all constraint checks.
D. To make every query use an index.

**Answer: B.** Short transactions help reduce contention and resource retention.

### Question 10

What should an application consider if the connection fails during COMMIT?

A. The transaction definitely rolled back.
B. The transaction definitely committed.
C. The outcome may be uncertain to the client and needs a safe recovery strategy.
D. All external actions are automatically undone.

**Answer: C.** The application may need idempotency or a way to reconcile the operation's status.

SQL Interview Questions

### 1. What is the difference between COMMIT and ROLLBACK?

COMMIT completes the current transaction successfully. ROLLBACK aborts the transaction and discards its uncommitted changes.

### 2. What is a SAVEPOINT?

A SAVEPOINT marks a point inside a transaction to which the transaction can partially roll back.

### 3. What is the difference between ROLLBACK and ROLLBACK TO SAVEPOINT?

ROLLBACK aborts the current transaction. ROLLBACK TO SAVEPOINT undoes changes made after a particular savepoint while retaining earlier pending changes.

### 4. Does RELEASE SAVEPOINT commit the transaction?

No. It removes the savepoint. The transaction remains active until it is committed or rolled back.

### 5. Can you roll back a transaction after COMMIT?

Not by issuing ROLLBACK in a new transaction. A separate corrective transaction or a database recovery procedure may be required.

### 6. What happens when a statement fails in a PostgreSQL transaction?

An ordinary statement error generally puts the transaction into an aborted state. The transaction must be rolled back, or rolled back to a usable savepoint, before normal statements can continue.

### 7. How do transactions support atomicity?

Atomicity means the transaction's operations are treated as one logical unit: the transaction commits as a whole or its uncommitted database changes are rolled back.

### 8. Why should related updates be in one transaction?

If the updates represent one logical operation, a single transaction helps prevent partial completion from being committed.

### 9. Can ROLLBACK undo a message sent to an external service?

No. Database rollback only controls database transaction changes. External effects need separate coordination or compensation.

### 10. Why are long-running transactions a problem?

They can hold locks, increase contention, retain resources, and interfere with database maintenance.

### 11. What is the difference between a savepoint and a nested transaction?

A savepoint provides partial rollback within the existing transaction. It does not create an independent transaction that can commit separately.

### 12. What happens if the client loses its connection during COMMIT?

The client may not know whether the database committed the transaction. A safe application needs a way to reconcile the result and avoid duplicating non-idempotent operations.

### 13. How should a backend service handle transaction errors?

Use a transaction-aware database connection, execute related operations together, commit on success, roll back on failure, and release the connection safely.

### 14. Does COMMIT guarantee that other transactions immediately see every change?

Visibility depends on the database's transaction and isolation rules. In PostgreSQL, a successful commit makes the changes committed, but another transaction's snapshot can affect when those changes become visible to its queries.

Lesson Summary

COMMIT, ROLLBACK, and SAVEPOINT help control the outcome of database transactions.

Remember:

- BEGIN starts an explicit transaction in PostgreSQL.
- COMMIT completes the transaction successfully.
- ROLLBACK discards the transaction's uncommitted changes.
- SAVEPOINT marks a point for partial rollback.
- ROLLBACK TO SAVEPOINT undoes work after that point.
- RELEASE SAVEPOINT removes a savepoint but does not commit.
- A PostgreSQL transaction generally needs recovery after an ordinary statement error.
- Related database operations should share a transaction when they must succeed or fail together.
- Database rollback does not undo external actions.
- Keep transactions short and handle errors deliberately.
- Use safe recovery and idempotency strategies when commit outcomes are uncertain.

You should now be able to commit successful changes, roll back failed transactions, recover to savepoints, and explain transaction behavior in SQL interviews.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming ROLLBACK can undo a previously committed transaction

Why it happens: The developer confuses rolling back pending changes with reversing a completed transaction.

Incorrect Approach

sql

BEGIN;
 
UPDATE products
SET price = 1500
WHERE product_id = 1;
 
COMMIT;
 
-- This does not undo the previous transaction.
ROLLBACK;

Correct Solution

sql

-- Start a new corrective transaction when a
-- committed value must be changed intentionally.
BEGIN;
 
UPDATE products
SET price = 1200
WHERE product_id = 1;
 
COMMIT;

Key takeaway: Once a transaction commits, a later ROLLBACK cannot reverse it. A corrective transaction must explicitly apply the intended change.

#2 Treating RELEASE SAVEPOINT as COMMIT

Why it happens: The developer assumes releasing a savepoint makes the changes permanent.

Incorrect Approach

sql

BEGIN;
 
SAVEPOINT before_update;
 
UPDATE products
SET price = 1300
WHERE product_id = 1;
 
RELEASE SAVEPOINT before_update;
 
-- The transaction is still open.

Correct Solution

sql

BEGIN;
 
SAVEPOINT before_update;
 
UPDATE products
SET price = 1300
WHERE product_id = 1;
 
RELEASE SAVEPOINT before_update;
 
COMMIT;

Key takeaway: RELEASE SAVEPOINT removes the recovery point. COMMIT completes the outer transaction.

#3 Continuing after a PostgreSQL transaction error without recovery

Why it happens: The developer expects the next statement to run even though the transaction is in an aborted state.

Incorrect Approach

sql

BEGIN;
 
-- Assume this insert violates a constraint.
INSERT INTO products(product_id, product_name, price)
VALUES (1, 'Duplicate', 500);
 
-- This cannot run successfully while the
-- transaction remains aborted.
SELECT * FROM products;

Correct Solution

sql

BEGIN;
 
SAVEPOINT before_insert;
 
-- Assume this insert violates a constraint.
INSERT INTO products(product_id, product_name, price)
VALUES (1, 'Duplicate', 500);
 
-- After the error, recover to the savepoint.
ROLLBACK TO SAVEPOINT before_insert;
 
SELECT * FROM products;
 
COMMIT;

Key takeaway: In PostgreSQL, a failed statement generally aborts the current transaction. A usable savepoint allows the transaction to recover from the failed operation and continue.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

BEGIN starts an explicit transaction in PostgreSQL.
COMMIT completes a transaction and makes its changes eligible to be observed by other transactions according to the database's isolation rules.
ROLLBACK aborts the current transaction and discards its uncommitted changes.
SAVEPOINT creates a point within a transaction to which you can roll back.
ROLLBACK TO SAVEPOINT undoes changes made after that savepoint while retaining earlier transaction changes.
RELEASE SAVEPOINT removes a savepoint without committing the transaction.
A successful SQL statement is not necessarily permanently committed.
After an ordinary statement error in a PostgreSQL transaction, the transaction generally remains in an aborted state until it is rolled back or rolled back to a usable savepoint.
COMMIT and ROLLBACK do not automatically undo external actions such as an email already sent by an application.
Transaction boundaries should be deliberate, short, and handled correctly by application code.

Was this lesson helpful?

Found an error? Report an issue