Skip to content
SQL

SQL Transactions: BEGIN, COMMIT, ROLLBACK and SAVEPOINT

Ihsan Ullah · 12 min read
SQL transactions, Lesson 19 of the SQL course: BEGIN, COMMIT, ROLLBACK and SAVEPOINT

SQL course · Module 5: Creating and changing data · Lesson 19 of 40

Quick answer: A transaction groups several SQL statements into one all-or-nothing unit. Start it with BEGIN, then COMMIT to save every change or ROLLBACK to undo them all. Without BEGIN, PostgreSQL commits each statement on its own (autocommit). If one statement fails inside a transaction, the whole transaction is aborted: PostgreSQL ignores further commands until you end it, and COMMIT then rolls back. SAVEPOINT lets you undo only part of a transaction.

In this lesson you will learn:

  • what SQL transactions are, and how autocommit works in psql
  • how to save changes with BEGIN and COMMIT, or undo them with ROLLBACK
  • what “current transaction is aborted” means and how to get out of it
  • how SAVEPOINT undoes part of a transaction
  • what ACID means, and what other sessions see while your transaction is open

Before you start: Lesson 17, SQL INSERT, UPDATE and DELETE: Change Data Safely with RETURNING and Lesson 18, SQL Upsert: INSERT … ON CONFLICT and MERGE.

We ran every example on PostgreSQL 16.15 with the shop database, in order, and checked the current PostgreSQL 18 manual. The lesson adds orders, so run \i shop.sql in psql before you begin, as in Lesson 3, to get the same ids.

What is a transaction?

Placing an order in our shop takes two steps: one row in orders, then its lines in order_items. If the first step works and the second fails, you have an order with no items. A transaction prevents this. The PostgreSQL tutorial says a transaction “bundles multiple steps into a single, all-or-nothing operation” (PostgreSQL docs: Transactions).

You have used transactions all along without noticing. The same page says: “If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it.” This is called autocommit. psql has a variable for it:

\echo :AUTOCOMMIT
on

on is the default: “each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command” (PostgreSQL docs: psql). Every INSERT, UPDATE and DELETE in Lessons 17 and 18 was saved the moment it finished.

BEGIN and COMMIT: save several changes together

Omar (customer 10) has never ordered. Let us place his first order, with two products, as one transaction:

BEGIN;

INSERT INTO orders (customer_id, order_date, status)
VALUES (10, '2026-11-08', 'pending')
RETURNING id;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (23, 2, 1, 89.00),
       (23, 1, 1, 24.99);

COMMIT;
BEGIN
 id 
----
 23
(1 row)

INSERT 0 1
INSERT 0 2
COMMIT

RETURNING id gave us the new order id, 23, which we used for the lines. Until COMMIT, the changes existed only inside our transaction. COMMIT saved them together. The order is there with both lines (the join is from Lesson 12):

SELECT o.id, o.customer_id, o.status, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 23
ORDER BY oi.product_id;
 id | customer_id | status  | product_id | quantity 
----+-------------+---------+------------+----------
 23 |          10 | pending |          1 |        1
 23 |          10 | pending |          2 |        1
(2 rows)

ROLLBACK: undo everything since BEGIN

Now say we start to delete the order, then change our mind:

BEGIN;
DELETE FROM order_items WHERE order_id = 23;
DELETE FROM orders WHERE id = 23;
SELECT count(*) FROM orders WHERE id = 23;
BEGIN
DELETE 2
DELETE 1
 count 
-------
     0
(1 row)

Inside the transaction, the order is gone. ROLLBACK cancels both deletes:

ROLLBACK;
SELECT count(*) FROM orders WHERE id = 23;
ROLLBACK
 count 
-------
     1
(1 row)

Lesson 17 used this same trick as a safety net for UPDATE without WHERE.

What happens after an error in a transaction

This part is where beginners get stuck. We start a second order for Omar, but type quantity 0 by mistake. The CHECK (quantity > 0) rule from Lesson 16 rejects it. Then we send the corrected line. Here is the real psql session, prompts included:

shop=# BEGIN;
BEGIN
shop=*# INSERT INTO orders (customer_id, order_date, status)
shop-*# VALUES (10, '2026-11-08', 'pending')
shop-*# RETURNING id;
 id 
----
 24
(1 row)

INSERT 0 1
shop=*# INSERT INTO order_items (order_id, product_id, quantity, unit_price)
shop-*# VALUES (24, 5, 0, 32.00);
ERROR:  new row for relation "order_items" violates check constraint "order_items_quantity_check"
DETAIL:  Failing row contains (24, 5, 0, 32.00).
shop=!# INSERT INTO order_items (order_id, product_id, quantity, unit_price)
shop-!# VALUES (24, 5, 1, 32.00);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
shop=!# COMMIT;
ROLLBACK

Three things happened:

  1. After the error, the correct INSERT was refused with current transaction is aborted, commands ignored until end of transaction block. One error spoils the whole transaction. PostgreSQL will not run anything else in it.
  2. We typed COMMIT, but psql answered ROLLBACK. Nothing was saved, not even the order row. SELECT count(*) FROM orders WHERE id = 24; returned 0.
  3. Watch the prompt. psql shows the transaction state after the database name: shop=# means no transaction, * means “in a transaction block” and ! means “in a failed transaction block” (PostgreSQL docs: psql prompting).

So when you see current transaction is aborted, scroll up and find the first error. That is the real problem. Fix it, run ROLLBACK, and start again from BEGIN.

SQL transactions in psql for Lesson 19: the prompt shop=# means no transaction, shop=*# means in a transaction after BEGIN, and shop=!# means a failed transaction where only ROLLBACK or ROLLBACK TO SAVEPOINT helps.
The psql prompt shows the transaction state. After an error, every command is ignored until you run ROLLBACK; even COMMIT only rolls back. Prompts and messages from a real PostgreSQL 16.15 session.

psql also has a helper: after \set ON_ERROR_ROLLBACK on, psql places a hidden savepoint before each statement, so a failed statement is undone and “the transaction continues”. In our test, the corrected INSERT then worked. It is off by default.

SAVEPOINT: undo only part of a transaction

A savepoint is a marker inside a transaction. ROLLBACK TO SAVEPOINT undoes the work done after the marker and keeps the work before it. The tutorial notes that it “is the only way to regain control of a transaction block that was put in aborted state by the system due to an error, short of rolling it back completely”.

BEGIN;

INSERT INTO orders (customer_id, order_date, status)
VALUES (10, '2026-11-08', 'pending')
RETURNING id;

SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (25, 5, 0, 32.00);

ROLLBACK TO SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (25, 5, 1, 32.00);

COMMIT;
BEGIN
 id 
----
 25
(1 row)

INSERT 0 1
SAVEPOINT
ERROR:  new row for relation "order_items" violates check constraint "order_items_quantity_check"
DETAIL:  Failing row contains (25, 5, 0, 32.00).
ROLLBACK
INSERT 0 1
COMMIT

The bad line failed, ROLLBACK TO SAVEPOINT (shown as ROLLBACK) went back to the marker, the order row survived, and this time COMMIT really committed:

SELECT o.id, o.status, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 25;
 id | status  | product_id | quantity 
----+---------+------------+----------
 25 | pending |          5 |        1
(1 row)

Notice the id: 25, not 24. The rolled-back order used 24, and as in Lesson 18, ids are not given back.

ACID in simple words

Database books describe transactions with four letters. The PostgreSQL glossary defines each one (PostgreSQL docs: Glossary):

letterglossary meaning (short)in this lesson
Atomicity“either all its operations complete as a single unit or none do”order 24 and its line: neither was saved
Consistencydata always follows the integrity constraintsthe CHECK rule stopped quantity 0
Isolationchanges are “not visible to concurrent transactions before it commits”the two-session test below
Durabilityafter commit, “the changes remain even after a system failure or crash”orders 23 and 25 stay saved

We did not crash the server to test durability. The manual says the changes are “logged in permanent storage (i.e., on disk) before the transaction is reported complete”.

What other sessions see

To see isolation, we ran two psql sessions on the same database at the same time. You can do the same in two terminal windows. Session A changes a price and does not commit yet:

--- Session A
BEGIN;
BEGIN
UPDATE products SET price = 99.00 WHERE id = 2;
UPDATE 1
SELECT name, price FROM products WHERE id = 2;
        name         | price 
---------------------+-------
 Mechanical Keyboard | 99.00
(1 row)

Session B looks at the same row, then tries to change it:

--- Session B
SELECT name, price FROM products WHERE id = 2;
        name         | price 
---------------------+-------
 Mechanical Keyboard | 89.00
(1 row)

UPDATE products SET price = 79.00 WHERE id = 2;

Session B still sees 89.00: A’s change is not committed, so others cannot see it. And B’s UPDATE did not finish. It waited. From session A, pg_stat_activity showed why:

SELECT wait_event_type, wait_event, query FROM pg_stat_activity WHERE query LIKE 'UPDATE products SET price = 79%';
 wait_event_type |  wait_event   |                      query                      
-----------------+---------------+-------------------------------------------------
 Lock            | transactionid | UPDATE products SET price = 79.00 WHERE id = 2;
(1 row)

When A ran COMMIT, B’s UPDATE then finished with UPDATE 1, and the final price was 79.00. This matches the manual: in the default Read Committed level, a query “never sees” uncommitted data, and a second updater of the same row “will wait for the first updating transaction to commit or roll back” (PostgreSQL docs: Transaction Isolation). Lesson 34 covers isolation levels, locks and deadlocks.

One more test: session B ran BEGIN, inserted a customer, and quit psql without COMMIT. Session A then counted that customer and got 0. An unfinished transaction is rolled back when its session ends; the psql manual warns that “if you exit the session without committing, your work will be lost”.

Try it yourself

Exercise 1: Try a price change, then undo it

In a transaction, set the price of Gel Pens 10-Pack (product 7) to 7.00 and show the new price. Then undo the change and check the price again.

Exercise 2: Keep the good part

In one transaction, mark order 22 as shipped, set a savepoint, then try to set its status to lost (not an allowed status). Go back to the savepoint, commit, and check the status.

Exercise 3: COMMIT after an error

Start a transaction, insert a product with price -5.00, and then run COMMIT. What does psql print, and was the product saved?

Solutions

Solution 1

BEGIN;
UPDATE products SET price = 7.00 WHERE id = 7 RETURNING name, price;
ROLLBACK;
SELECT name, price FROM products WHERE id = 7;
BEGIN
       name       | price 
------------------+-------
 Gel Pens 10-Pack |  7.00
(1 row)

UPDATE 1
ROLLBACK
       name       | price 
------------------+-------
 Gel Pens 10-Pack |  6.75
(1 row)

ROLLBACK brought back the old price, 6.75.

Solution 2

BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 22;
SAVEPOINT shipped;
UPDATE orders SET status = 'lost' WHERE id = 22;
ROLLBACK TO SAVEPOINT shipped;
COMMIT;
SELECT id, status FROM orders WHERE id = 22;
BEGIN
UPDATE 1
SAVEPOINT
ERROR:  new row for relation "orders" violates check constraint "orders_status_check"
DETAIL:  Failing row contains (22, 3, 2026-10-30, lost).
ROLLBACK
COMMIT
 id | status  
----+---------
 22 | shipped
(1 row)

The savepoint kept the shipped update and threw away only the failed one.

Solution 3

BEGIN;
INSERT INTO products (name, category, price) VALUES ('Desk Mat', 'Accessories', -5.00);
COMMIT;
SELECT count(*) FROM products WHERE name = 'Desk Mat';
BEGIN
ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (11, Desk Mat, Accessories, -5.00).
ROLLBACK
 count 
-------
     0
(1 row)

psql answered the COMMIT with ROLLBACK, and the count is 0: nothing was saved.

When you finish, run \i shop.sql to remove the extra orders.

Common mistakes

  • Forgetting COMMIT. Your changes are visible only to you, and they are lost when the session ends. Meanwhile other sessions that update the same rows wait, as session B did (Transaction Isolation).
  • Typing more commands after an error. Each one fails with current transaction is aborted, commands ignored until end of transaction block. Run ROLLBACK and start again.
  • Thinking COMMIT saved a failed transaction. psql answers ROLLBACK. Read the reply, not your command.
  • COMMIT or ROLLBACK with no open transaction. It is harmless, but you get WARNING: there is no transaction in progress. A second BEGIN gives WARNING: there is already a transaction in progress.
  • SAVEPOINT outside a transaction. It fails with SAVEPOINT can only be used in transaction blocks. Run BEGIN first.

Frequently asked questions

What happens if I close psql without COMMIT?

The open transaction is rolled back, and its changes are lost (PostgreSQL docs: psql). In our two-session test, a customer inserted after BEGIN was gone once that session quit. Changes made in autocommit mode (without BEGIN) were already saved, so they stay.

Can I roll back CREATE TABLE?

In PostgreSQL, yes. In our test, BEGIN; CREATE TABLE demo_tx (x integer); ROLLBACK; left no table behind: a later SELECT gave relation "demo_tx" does not exist. MySQL is different: statements like CREATE TABLE cause an implicit commit, “as if you had done a COMMIT before executing the statement” (MySQL 8.4: Statements That Cause an Implicit Commit).

Do MySQL and SQLite use BEGIN too?

Yes. In MySQL, “autocommit mode” is on by default, and BEGIN is an alias of START TRANSACTION (MySQL 8.4: START TRANSACTION, COMMIT, and ROLLBACK). SQLite’s statement list also includes BEGIN TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT (SQLite docs: SQL language). We ran only PostgreSQL for this lesson.

Summary

  • A transaction is all or nothing: BEGIN, your statements, then COMMIT or ROLLBACK.
  • Without BEGIN, autocommit saves each statement on its own.
  • After any error, the transaction is aborted; COMMIT turns into ROLLBACK. Fix the first error and start again.
  • SAVEPOINT and ROLLBACK TO SAVEPOINT undo only part of a transaction.
  • Other sessions do not see your changes until you commit, and they wait if they update the same rows.

Transactions also make data loads safe: shop.sql wraps its whole load in BEGIN and COMMIT, so a failed load leaves nothing half done. Project 1 in our data engineering projects list loads processed data into PostgreSQL, a good place to use the same habit.

Next lesson

Next: Lesson 20, SQL Subqueries: Scalar, IN, EXISTS and Correlated Subqueries.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *