A transaction groups related operations into a unit that can commit or roll back. Understanding that boundary is essential when partial updates or simultaneous requests could leave conflicting state.
Choose a boundary around one business change
If two stored changes must succeed together, place them in one appropriate transaction. A commit makes the changes permanent; a rollback abandons them. This does not mean every external action belongs inside a long-running database transaction. An email or remote API call may need a separate delivery strategy because the database cannot simply undo it.
Consider other requests reading and writing
Concurrent requests can observe or compete for related data. Isolation behaviour, locks and atomic updates depend on the database and operation. A read followed by an unchecked write can lose an update. Define the invariant, then choose a concurrency strategy that preserves it and test simultaneous attempts.
Plan for retries without repeating effects
Deadlocks or network uncertainty can require a retry. Keep the retried unit safe and use identifiers or constraints where duplicate effects would matter. A transaction alone does not establish exactly-once delivery across external systems. Treat failure messages and unchanged data after rejection as part of the workflow.
Worked example
The fictional example reduces one balance and then rolls the transaction back. The final query still shows 100. It demonstrates rollback only; a real transfer also needs sufficient-funds rules, permission checks and a concurrency policy.
CREATE TABLE balances (id INTEGER PRIMARY KEY, amount INTEGER);
INSERT INTO balances VALUES (1,100);
BEGIN;
UPDATE balances SET amount = amount - 20 WHERE id = 1;
ROLLBACK;
SELECT amount FROM balances WHERE id = 1;
Expected output
100Check your understanding
Try each question before opening its explanation. These are free practice prompts; they do not use game chances.
Does rollback reverse an email already sent?
No. A database rollback affects its transactional changes, not arbitrary external side effects. Design external delivery and retries separately.
Why can two individually valid requests cause a problem?
They may both make decisions from the same earlier state. The combined result can violate an invariant unless updates are coordinated with a suitable atomic or locking strategy.
What does the final balance show in this example?
It remains 100 because the update was rolled back. This gives a visible check that the temporary transactional change did not become permanent.
Sources and further reading
These original examples explain foundational concepts. Use the official references for complete, current details and version-specific behaviour.
About this guide: AI-assisted educational content with original examples and linked primary references. Found an issue? Tell us what to improve.