Chapter 13 · Transactions and Concurrency
COMMIT, ROLLBACK, and Savepoints
Transactions need more than a beginning and an end. Savepoints create named recovery positions inside a larger unit of work so an application can undo a risky step without discarding everything already validated.
Learning outcomes
Control success, failure, and partial recovery
Describe the exact effect of COMMIT and ROLLBACK.
Use named savepoints to create recoverable sub-steps inside a transaction.
Distinguish ROLLBACK TO from a full ROLLBACK and understand why RELEASE matters.
Design batch workflows that isolate expected row-level failures without hiding systemic failure.
Verify transaction state and postconditions before final commit.
The transaction outcome commands
| Command | Scope | Result |
|---|---|---|
| COMMIT | Current transaction | Makes its changes durable and visible according to the database configuration. |
| ROLLBACK | Current transaction | Discards all uncommitted changes and ends the transaction. |
| SAVEPOINT name | Current transaction stack | Creates a named recovery point; in SQLite it can also begin a transaction when none is active. |
| ROLLBACK TO name | Changes after the savepoint | Undoes later work but keeps the outer transaction and named savepoint active. |
| RELEASE name | Named savepoint and nested savepoints | Removes the savepoint boundary; releasing the outermost savepoint commits. |
Releasing an inner savepoint only merges its work into the surrounding transaction. A later full rollback can still undo it.
A savepoint stack
Savepoints are nested recovery markers. ROLLBACK TO rewinds to a marker; RELEASE removes the marker; COMMIT finalizes the outer transaction.
Set up an order workflow
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS payment_attempt;DROP TABLE IF EXISTS order_item;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS inventory;CREATE TABLE inventory ( sku TEXT PRIMARY KEY, stock_qty INTEGER NOT NULL CHECK (stock_qty >= 0)) STRICT;CREATE TABLE sales_order ( order_id INTEGER PRIMARY KEY, request_key TEXT NOT NULL UNIQUE, status TEXT NOT NULL CHECK (status IN ('draft','confirmed','cancelled')), total_cents INTEGER NOT NULL CHECK (total_cents >= 0), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE order_item ( order_id INTEGER NOT NULL REFERENCES sales_order(order_id) ON DELETE CASCADE, line_no INTEGER NOT NULL, sku TEXT NOT NULL REFERENCES inventory(sku), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0), PRIMARY KEY (order_id, line_no)) STRICT, WITHOUT ROWID;CREATE TABLE payment_attempt ( attempt_id TEXT PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES sales_order(order_id), provider_ref TEXT UNIQUE, state TEXT NOT NULL CHECK (state IN ('started','authorized','failed')), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO inventory VALUES ('SQL-TXN', 8), ('SQL-CARD', 40), ('DB-DESIGN', 6);Commit the complete order
BEGIN IMMEDIATE;INSERT INTO sales_order (order_id, request_key, status, total_cents)VALUES (1001, 'req-order-1001', 'draft', 4500);INSERT INTO order_item (order_id, line_no, sku, quantity, unit_cents)VALUES (1001, 1, 'SQL-TXN', 1, 4500);UPDATE inventorySET stock_qty = stock_qty - 1WHERE sku = 'SQL-TXN' AND stock_qty >= 1;SELECT changes() AS reserved_rows;UPDATE sales_orderSET status = 'confirmed'WHERE order_id = 1001 AND status = 'draft';COMMIT;A successful COMMIT ends the transaction. A later ROLLBACK cannot undo already committed data; recovery would require a new compensating transaction.
Roll back the entire operation
BEGIN IMMEDIATE;INSERT INTO sales_order (order_id, request_key, status, total_cents)VALUES (1002, 'req-order-1002', 'draft', 8900);UPDATE inventorySET stock_qty = stock_qty - 2WHERE sku = 'DB-DESIGN' AND stock_qty >= 2;-- A business validation fails before confirmation.ROLLBACK;SELECT COUNT(*) AS order_existsFROM sales_orderWHERE order_id = 1002;SELECT stock_qtyFROM inventoryWHERE sku = 'DB-DESIGN';After rollback, the order is absent and stock returns to its prior value. The SELECT statements run in new autocommit transactions.
Use a savepoint for a risky sub-step
BEGIN IMMEDIATE;INSERT INTO sales_order (order_id, request_key, status, total_cents)VALUES (1003, 'req-order-1003', 'draft', 6000);INSERT INTO order_item (order_id, line_no, sku, quantity, unit_cents)VALUES (1003, 1, 'SQL-CARD', 4, 1500);SAVEPOINT reserve_stock;UPDATE inventorySET stock_qty = stock_qty - 4WHERE sku = 'SQL-CARD' AND stock_qty >= 4;SELECT changes() AS reserved_rows;-- Imagine verification rejects this reservation policy.ROLLBACK TO reserve_stock;RELEASE reserve_stock;-- The order header and item still exist inside the outer transaction.UPDATE sales_orderSET status = 'cancelled'WHERE order_id = 1003;COMMIT;ROLLBACK TO reserve_stock undoes only the stock update. The earlier order rows remain. RELEASE reserve_stock removes the still-active marker before the outer commit.
Handle a row-level import failure
In a batch, a savepoint can isolate an expected bad row while the transaction keeps valid rows. Do not use this pattern to suppress infrastructure errors or unknown corruption.
BEGIN;SAVEPOINT row_1;INSERT INTO inventory (sku, stock_qty)VALUES ('SQL-INDEX', 12);RELEASE row_1;SAVEPOINT row_2;-- This duplicate key is expected to fail:-- INSERT INTO inventory (sku, stock_qty)-- VALUES ('SQL-TXN', 99);ROLLBACK TO row_2;RELEASE row_2;SAVEPOINT row_3;INSERT INTO inventory (sku, stock_qty)VALUES ('SQL-MVCC', 9);RELEASE row_3;COMMIT;The importer should record why row 2 was rejected. Silent rollback creates an incomplete dataset with no audit trail.
Savepoint rules that prevent surprises
Use descriptive markers
Names such as reserve_inventory are safer than sp1 during debugging.
Treat savepoints as nested
Rolling back to an outer marker removes work and nested markers created after it.
Close resolved markers
A successful or deliberately rolled-back sub-step should release its savepoint.
Classify failures
Constraint failures may be row-local; connection loss or disk errors usually invalidate the whole attempt.
Record rejected work
A batch that continues should preserve rejection reason, input identity, and retry status.
Checkpoint
Choose the correct outcome
- What is the difference between ROLLBACK and ROLLBACK TO?
- Does RELEASE of an inner savepoint guarantee that its changes survive an outer rollback?
- Why should an importer record rejected rows?
- After COMMIT, can the same transaction be rolled back?
- When should a savepoint not be used to continue?
Review the answers
ROLLBACK ends the transaction and discards all work; ROLLBACK TO rewinds only to a marker and leaves the transaction active. Releasing an inner savepoint does not protect it from an outer rollback. Rejection records make partial success observable and repairable. Committed work requires a new compensating transaction. Do not continue after failures that make connection or database state uncertain.
Summary and references
- COMMIT finalizes the unit; ROLLBACK discards it.
- Savepoints provide named partial-recovery positions.
- ROLLBACK TO does not end the outer transaction and does not automatically remove the marker.
- Batch continuation must be explicit, auditable, and limited to understood row-level failures.
- Verify state before the final commit rather than treating command success as business success.