Free 30-day beta access · no payment · progress saved
The business problem
An order is recorded but its payment fails, leaving an incomplete state.
What you will be able to do
Use COMMIT, ROLLBACK and SAVEPOINT while understanding errors and concurrency.
Define a transaction boundary
Undo an operation
Use a savepoint
Understand isolation
Scope of this micro-course
This micro-course targets one focused skill. It does not replace a complete path or server administration training.
Prerequisites
INSERT, UPDATE and DELETE
Project and practice
8 verified exercises are available. Validation is based on the programme exercises.
Focused path
Your programme, in order.
Content remains centralised: completing an activity here also updates the path that contains it.
Micro-lessons
01
The problem to solve before syntax
A UPDATE without WHERE can modify an entire table. A restarted import can also create duplicates if the uniqueness and conflict behavior are not defined.
First write the SELECT that identifies the lines, run the change in a transaction, use RETURNING to observe the effects, and explicitly choose the conflict key of the UPSERT.
Check the number of records returned before and after, check the returned values and test resuming the import. A safe operation is repeatable or explicitly non-repeatable.
Debiting inventory and then failing before creating the order leaves an inconsistent state. Two transactions can also read the same stock and sell the last unit simultaneously.
Group the writes that form one decision in BEGIN/COMMIT. A transaction does not guarantee that an UPDATE matches a row. Here the order is inserted only from rows reserved by UPDATE…RETURNING: no stock means no order. Use ROLLBACK to undo the operation and SAVEPOINT to recover a substep.
Try zero stock, a missing product, then a stock of two: expect zero, zero and one order respectively. Introduce an error between two writes and roll back. This embedded lab supports atomicity tests; conflicts between two sessions need PostgreSQL server, not this single-user instance.
Commit a correct change and undo only the incorrect step. You must produce the result, check it on the source data and explain what it allows you to decide in this billing system.
Commit a correct change and undo only the incorrect step. You must produce the result, check it on the source data and explain what it allows you to decide in this national expeditions.
Commit a correct change and undo only the incorrect step. You must produce the result, control it on the source data and explain what it allows you to decide in this human resources management.
Commit a correct change and undo only the incorrect step. You must produce the result, check it on the source data and explain what it allows you to decide in this audit log.
Insert then update billing data 2 · training scenario
Make two controlled changes and check the final state. You must produce the result, check it on the source data and explain what it allows you to decide in this billing system.
Insert then update logistics data 3 · training scenario
Make two controlled changes and check the final state. You must produce the result, check it on the source data and explain what it allows you to decide in this national expeditions.