Transactions and Savepoints

BEGIN, COMMIT, ROLLBACK, SAVEPOINT

A transaction groups statements so they succeed or fail together (atomicity) and their changes become visible together on COMMIT. After an error, PostgreSQL marks the transaction as aborted until you roll back. A SAVEPOINT lets you roll back part of a transaction and continue. Keep transactions short: long-running transactions hold locks and prevent cleanup of old row versions.

Correct results with many users

Transactions group changes, isolation levels define what concurrent transactions see, and locks prevent lost updates.

Recovering from an error with a savepoint, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. Inside one transaction, the notebook price rises 10%; an invalid update to the lamp fails the CHECK constraint; rolling back to the savepoint discards only the failed step, and COMMIT keeps the notebook change (132.00).

BEGIN;
UPDATE products SET price = price * 1.10 WHERE name = 'Notebook';
SAVEPOINT before_lamp;
UPDATE products SET price = -1 WHERE name = 'Desk lamp';
ROLLBACK TO SAVEPOINT before_lamp;
COMMIT;
SELECT name, price FROM products ORDER BY id;

Output:

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (3, Desk lamp, -1.00, {"color": "white", "watts": 9}).
     name     |  price  
--------------+---------
 Notebook     |  132.00
 Fountain pen |  899.00
 Desk lamp    | 1499.00
(3 rows)

Keep transactions short

Do slow work (network calls, user input) outside the transaction and only wrap the database writes.

त्वरित जाँच: What happens to a transaction after one of its statements fails?

• Later statements still run normally
• It commits automatically
• It is aborted until you roll back (or roll back to a savepoint)
• The database restarts