# Transactions and Savepoints — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/x-tx

> All or nothing.

## 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.

![Three ideas: transactions and savepoints, isolation, row locks.](assets/figures/postgresql/section-6-map.svg) — Figure 6.1 — Transactions, isolation and locks.

## 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).

```sql
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.

**Quiz:** What happens to a transaction after one of its statements fails?

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

*Answer:* It is aborted until you roll back (or roll back to a savepoint). Errors must be handled explicitly.