Transactions

Most important commands to remember

• psql -X -d lab — open the test database without startup customizations.
• BEGIN / COMMIT — define and commit a transaction in psql.
• ROLLBACK — discard the current transaction’s changes.

Commands and flags

Command or syntax	Meaning
-X -d lab	Skip psql startup files and select database lab.
CREATE TEMP TABLE	Create a session-local table removed when the connection closes.
integer NOT NULL	Store whole numbers and disallow null values.
INSERT / UPDATE / SELECT	Add the initial row / change its amount / read it.
BEGIN / COMMIT / ROLLBACK	Start / commit / abandon a transaction.
; / \q	End an SQL statement / leave psql.

The number 100 is a test balance in arbitrary whole units. No real account or currency is involved.

The concepts that matter

1. A transaction groups a logical change

A transaction groups database operations into one unit. For a transfer between accounts, subtracting from one account and adding to the other should succeed together or be abandoned together.

Without an explicit transaction, PostgreSQL normally treats each successful statement as its own transaction. Several statements in the same terminal are not automatically one all-or-nothing unit. The boundary must match the business change you intend to protect.

2. Commit and rollback decide the outcome

Inside a transaction, a session can see its own changes. COMMIT makes the transaction’s changes committed; ROLLBACK discards them. Seeing an updated value before commit is therefore not proof that the change has been committed.

If the connection is lost while a transaction is open, uncommitted work is abandoned. If the connection fails during commit, the client may not know whether commit succeeded. Repeating the operation blindly can then produce a duplicate effect.

3. ACID describes distinct properties

Atomicity means the transaction is treated as a unit. Consistency concerns preserving enforced invariants. Isolation governs interactions with concurrent transactions. Durability concerns committed changes surviving failures under the database’s configured guarantees.

These properties do not invent missing business rules. A database needs constraints and correct application logic to know that a balance or relationship must satisfy a particular condition. Nor does every isolation level behave as though transactions ran one at a time.

4. The transaction boundary does not include everything

A database rollback does not unsend an email or undo an external HTTP request. Those effects belong to other systems unless an explicit coordination design connects them.

Keep database changes and external delivery responsibilities clear. Patterns such as recording an outgoing event in the same transaction can support later reliable delivery. They still need retries and duplicate handling. Temporary tables in this example demonstrate SQL behavior, not crash-durable application storage.

One small example

Optional: open psql with the first block, then enter the SQL there in order. If a statement fails, stop and inspect the error before continuing. The final command belongs at the psql prompt.

psql -X -d lab

CREATE TEMP TABLE lab_balance (amount integer NOT NULL);
INSERT INTO lab_balance VALUES (100);
BEGIN;
UPDATE lab_balance SET amount = amount - 30;
SELECT amount FROM lab_balance;
ROLLBACK;
SELECT amount FROM lab_balance;
BEGIN;
UPDATE lab_balance SET amount = amount - 30;
COMMIT;
SELECT amount FROM lab_balance;

\q

The reads should show 70 inside the first transaction, 100 after rollback, and 70 after the second transaction commits. These are expected values derived from the test statements, not captured output.

The contrast shows that a session can observe a change that is later discarded. It does not test concurrent sessions or power-loss recovery. Leaving with \q closes the session and removes the temporary table, including its committed test row. Existing database tables are untouched.

Keep this idea: A transaction makes related database changes one decision; place the boundary around the work that must succeed or fail together.