Transactions

Some changes only make sense together: take money from one account and add it to another. A transaction makes a group of statements happen completely or not at all. By the end of this page you can run one, undo one, and let constraints refuse bad data for you.

The problem: half a change

The practice shop keeps each customer's store credit in a small table:

SELECT * FROM store_credit;

customer_id	balance_cents
1	5000
2	1200
3	0
4	2500
5	750

Customer 1 gives 10.00 of credit to customer 2. That is two statements:

UPDATE store_credit SET balance_cents = balance_cents - 1000 WHERE customer_id = 1;
UPDATE store_credit SET balance_cents = balance_cents + 1000 WHERE customer_id = 2;

If the program crashes, the power fails or the second statement hits an error between the two, customer 1 has lost 10.00 and customer 2 never received it. The money has vanished.

ACID in plain words

A transaction is a group of statements the database treats as one unit. Databases promise four things about transactions, known by their initials, ACID:

Letter	Name	In plain words
A	Atomic	All of it happens, or none of it does. Never half.
C	Consistent	The rules you set (keys, constraints) hold before and after.
I	Isolated	Other users do not see your half-finished work, only the result.
D	Durable	Once the database says "committed", the change survives a crash or power cut.

BEGIN, COMMIT and ROLLBACK

BEGIN ──► change ──► change ──► check ──┬──► COMMIT    (keep everything)
                                        └──► ROLLBACK  (throw everything away)

• BEGIN starts a transaction.
• COMMIT makes every change since BEGIN permanent and visible to others.
• ROLLBACK throws every change since BEGIN away, as if it never happened.

Without BEGIN, the database is in autocommit mode: each statement is its own small transaction and is committed as soon as it finishes. That is why an UPDATE in Database Manager's Query tab is permanent the moment it runs.

The transfer, done properly

BEGIN;
UPDATE store_credit SET balance_cents = balance_cents - 1000 WHERE customer_id = 1;
UPDATE store_credit SET balance_cents = balance_cents + 1000 WHERE customer_id = 2;
COMMIT;

SELECT * FROM store_credit WHERE customer_id IN (1, 2);

customer_id	balance_cents
1	4000
2	2200

Either both balances changed, or, if anything had failed before COMMIT, neither did.

A transfer that must not happen

Customer 5 has 750 cents. Someone tries to move 2000 cents from customer 5 to customer 4:

BEGIN;
UPDATE store_credit SET balance_cents = balance_cents + 2000 WHERE customer_id = 4;
UPDATE store_credit SET balance_cents = balance_cents - 2000 WHERE customer_id = 5;

The first statement works: customer 4 now has 4500, inside the transaction. The second fails:

CHECK constraint failed: balance_cents >= 0

The table was created with a rule that a balance may never go below zero (more on that below). Now look at the state:

customer_id	balance_cents
4	4500
5	750

Customer 4 got the money, customer 5 did not pay. In SQLite, a failed statement undoes only itself; the transaction stays open with the first change still in it. You decide what happens next, and the only right answer here is:

ROLLBACK;

Now customer 4 is back at 2500 and customer 5 at 750. Nothing happened.

Good to know

PostgreSQL is stricter: after an error, it refuses every further statement in the transaction until you ROLLBACK. In every database, a program should roll back when any step of a transaction fails.

Undo a mistake you made on purpose

A transaction is also a safety net for your own typing. Try the most dangerous statement from lesson 2 inside one:

BEGIN;
DELETE FROM order_items;                -- 305 rows deleted
SELECT COUNT(*) FROM order_items;       -- 0
ROLLBACK;
SELECT COUNT(*) FROM order_items;       -- 305 again

You will do exactly this in the lab.

Keep transactions short

While your transaction is open, other connections do not see your changes. In SQLite, once your transaction has written something, no other connection can write to the database until you commit or roll back. Server databases lock less, but still hold locks on the rows you changed. So:

• Do not start a transaction and go to lunch.
• Never leave a transaction open by accident. If you close the connection without committing, SQLite rolls the changes back.

Constraints: rules the database enforces

A constraint is a rule written into the schema. The database checks it on every INSERT and UPDATE and refuses the statement if it is broken. Constraints are the "C" in ACID: the rules hold no matter which program or person writes the data.

Here is how the shop's tables declare theirs:

CREATE TABLE customers (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  email   TEXT NOT NULL UNIQUE,
  city    TEXT NOT NULL,
  joined  TEXT NOT NULL
);

CREATE TABLE store_credit (
  customer_id   INTEGER PRIMARY KEY REFERENCES customers(id),
  balance_cents INTEGER NOT NULL CHECK (balance_cents >= 0)
);

Constraint	Rule	Example in the shop
NOT NULL	The column must have a value	Every customer has a city
UNIQUE	No two rows share the value	No two customers share an email
CHECK (…)	A condition every row must meet	balance_cents >= 0, stock >= 0, status IN ('placed', 'shipped', 'delivered', 'cancelled')
PRIMARY KEY	Unique and not empty	customers.id
REFERENCES	A foreign key; must point at a real row	orders.customer_id (see lesson 3)

What happens when you break each one. These are the exact messages SQLite gives for the practice shop:

INSERT INTO customers (name, email, city, joined)
VALUES ('Test Person', 'ben.carter@example.com', 'Leeds', '2026-11-09');

UNIQUE constraint failed: customers.email

INSERT INTO customers (name, email, joined)
VALUES ('Test Person', 'test.person@example.com', '2026-11-09');

NOT NULL constraint failed: customers.city

UPDATE products SET stock = -1 WHERE id = 1;

CHECK constraint failed: stock >= 0

In each case nothing is written. A constraint is a guard that never gets tired: put rules in the schema, not only in the app's code, and bad data cannot get in through a bug, a script or a hurried edit in a database tool.

Transactions in Database Manager

Database Manager keeps one connection open to a SQLite file while you work with it, so a transaction you start in the Query tab stays open across runs:

1. Type BEGIN; and select Run (Ctrl+Enter).

2. Run each change, one statement per run. Look at the result after each.

3. Run COMMIT; to keep the changes, or ROLLBACK; to throw them away.

Things to know:

• For a statement that changes no rows itself, such as BEGIN, COMMIT, ROLLBACK or CREATE INDEX, the row(s) affected line can show a number left over from the statement before. Ignore it.
• While the transaction is open, the Data tab shows your uncommitted changes, because it uses the same connection. Edits you make there become part of the transaction too.
• Backup does not work while a transaction is open: SQLite cannot copy the database from inside one. Commit or roll back first.

Check that an API call really wrote the row

Testers meet transactions from the other side. An API answers 201 Created, but did the row really reach the database? A bug can return success before the transaction commits, or after it has been rolled back.

1. In ApiWorkbench, send the request against a test environment, for example POST /orders, and note the id in the response.

2. In Database Manager, open the test environment's database and find that row on the Data tab, or run a query:

   SELECT * FROM orders WHERE id = 1121;

3. Check every column you sent, and the ones the server should have filled in, such as a status or a timestamp.

Use the test environment's database, with a read-only account if you can get one. See Functional testing for checking the same thing through the API itself.

Check yourself

QuizTransactions

Question 1 of 6

What does the A in ACID, atomic, promise?

Score: 0

What you learned

• A transaction makes several statements atomic: all or nothing. ACID adds consistent, isolated and durable.
• BEGIN starts one, COMMIT keeps it, ROLLBACK throws it away. Without BEGIN, every statement commits by itself.
• In SQLite, a failed statement leaves the transaction open; roll back yourself.
• NOT NULL, UNIQUE, CHECK, primary and foreign keys let the database refuse bad data.

Next steps

• Safety and backups: habits that keep data safe
• Edit data safely in Database Manager