Chapter 10 · Changing Data Safely
Safe Change Workflows with Transactions and Verification
Production-safe SQL is a workflow, not a single statement: define the target, lock the scope, change the data, verify invariants, record evidence, and commit only when every check passes.
Learning outcomes
A safe data change is an auditable decision process. Transactions provide atomicity, but the operator must still define expected rows, verify business invariants, handle retries, and preserve evidence.
Convert a business change into a preflight query and explicit assertions.
Use BEGIN, SAVEPOINT, ROLLBACK, and COMMIT deliberately.
Verify affected rows with RETURNING, changes(), and reconciliation queries.
Build idempotency and audit evidence into repeatable operations.
Write a production change runbook with a tested rollback path.
The governed change loop
A transaction protects atomicity; the surrounding runbook protects intent, observability, and recovery.
Step 1: turn assumptions into queries
SELECT order_id, external_ref, status, total_amountFROM sales_orderWHERE order_id = 101 AND status = 'draft' AND total_amount = 0;SELECT COUNT(*) AS expected_rowsFROM sales_orderWHERE order_id = 101 AND status = 'draft' AND total_amount = 0;Record the expected count and representative values before the change. If the live result differs, stop and investigate instead of adapting the mutation casually.
Step 2: open an intentional transaction
BEGIN IMMEDIATE;UPDATE sales_orderSET status = 'paid', total_amount = 69.00WHERE order_id = 101 AND status = 'draft' AND total_amount = 0RETURNING order_id, external_ref, status, total_amount;BEGIN IMMEDIATE asks SQLite for a write transaction at the start, so lock contention is discovered before the operator performs a long sequence of checks and changes.
Step 3: use savepoints for recoverable substeps
SAVEPOINT adjust_inventory;UPDATE productSET stock_qty = stock_qty - 1, updated_at = CURRENT_TIMESTAMPWHERE sku = 'SQL-QUERY' AND stock_qty >= 1;SELECT changes() AS inventory_rows_changed;-- If verification fails:ROLLBACK TO adjust_inventory;RELEASE adjust_inventory;-- If verification succeeds, just release it:-- RELEASE adjust_inventory;A savepoint does not replace the outer transaction. It creates a named rollback boundary within it.
Step 4: verify row counts and invariants
SELECT changes() AS rows_changed;SELECT order_id, status, total_amountFROM sales_orderWHERE order_id = 101;SELECT COUNT(*) AS invalid_ordersFROM sales_orderWHERE total_amount < 0 OR status NOT IN ('draft', 'paid', 'cancelled', 'refunded');PRAGMA foreign_key_check;changes() reports the most recent top-level DML change count for the connection. Business assertions and foreign-key checks test properties that a row count alone cannot prove.
Step 5: make retries recognizable
INSERT INTO change_request ( request_key, operation)VALUES ( 'order-101-mark-paid-v1', 'Mark WEB-101 paid at 69.00')ON CONFLICT (request_key) DO NOTHING;SELECT changes() AS request_claimed;Continue with the mutation only when the request ledger reports one inserted row. A retry that reports zero is already claimed or applied and must be reconciled rather than repeated blindly.
Complete guarded workflow
BEGIN IMMEDIATE;INSERT INTO change_request (request_key, operation)VALUES ('order-101-mark-paid-v1', 'Mark WEB-101 paid at 69.00')ON CONFLICT (request_key) DO NOTHING;-- The application must assert that the prior statement inserted one row.UPDATE sales_orderSET status = 'paid', total_amount = 69.00WHERE order_id = 101 AND status = 'draft' AND total_amount = 0RETURNING order_id, status, total_amount;-- Assert one returned row and all post-change invariants.COMMIT;Some assertions must be enforced by application code, a migration framework, a stored procedure, or an operator runbook. A COMMIT should be conditional on those checks succeeding.
Change evidence record
| Field | Evidence |
|---|---|
| Change ID | Stable ticket, migration, or request key. |
| Owner and approver | Who executed and who authorized the change. |
| Target definition | Exact SELECT, predicate, and expected count. |
| Recovery point | Backup, snapshot, replica, or reversible script. |
| Mutation | Version-controlled SQL and parameter values. |
| Verification | RETURNING rows, counts, invariants, and sampled results. |
| Outcome | Commit or rollback time, plus incident notes if applicable. |
Operational safety checklist
Bounded target
Prefer keys, explicit time windows, and expected old state.
Transaction
Keep dependent writes and verification in one decision boundary.
Idempotency
Use stable unique request keys or naturally idempotent predicates.
Rollback
Test recovery before executing the change, not after failure.
Reusable Chapter 10 practice database
Run this setup once in a disposable SQLite database. Every lesson uses the same constrained tables so that write behavior, references, conflicts, and verification can be compared consistently.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS change_request;DROP TABLE IF EXISTS order_archive;DROP TABLE IF EXISTS order_item;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS product_feed;DROP TABLE IF EXISTS customer_stage;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'closed')), loyalty_points INTEGER NOT NULL DEFAULT 0 CHECK (loyalty_points >= 0), deleted_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0), discontinued_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE sales_order ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL UNIQUE, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'paid', 'cancelled', 'refunded')), total_amount REAL NOT NULL DEFAULT 0 CHECK (total_amount >= 0), ordered_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, product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price REAL NOT NULL CHECK (unit_price >= 0), PRIMARY KEY (order_id, line_no)) STRICT;CREATE TABLE customer_stage ( email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('active', 'paused', 'closed'))) STRICT;CREATE TABLE product_feed ( sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL CHECK (stock_qty >= 0), source_time TEXT NOT NULL) STRICT;CREATE TABLE order_archive ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL, customer_id INTEGER NOT NULL, status TEXT NOT NULL, total_amount REAL NOT NULL, ordered_at TEXT NOT NULL, archived_at TEXT NOT NULL) STRICT;CREATE TABLE change_request ( request_key TEXT PRIMARY KEY, operation TEXT NOT NULL, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO customer (customer_id, email, full_name, status, loyalty_points, updated_at)VALUES (1, 'nadia@example.com', 'Nadia Rahimi', 'active', 120, '2026-08-01 09:00:00'), (2, 'omar@example.com', 'Omar Haddad', 'active', 80, '2026-08-01 09:00:00'), (3, 'lina@example.com', 'Lina Chen', 'paused', 40, '2026-08-01 09:00:00'), (4, 'ava@example.com', 'Ava Morgan', 'active', 0, '2026-08-01 09:00:00'), (5, 'noah@example.com', 'Noah Silva', 'closed', 15, '2026-08-01 09:00:00');INSERT INTO product (product_id, sku, product_name, unit_price, stock_qty, updated_at)VALUES (10, 'SQL-FOUND', 'Database Foundations', 49.00, 35, '2026-08-01 09:00:00'), (11, 'SQL-QUERY', 'SQL Query Practice', 69.00, 18, '2026-08-01 09:00:00'), (12, 'SQL-CARD', 'SQL Reference Card', 15.00, 80, '2026-08-01 09:00:00'), (13, 'SQL-LAB', 'SQLite Lab Bundle', 29.00, 22, '2026-08-01 09:00:00'), (14, 'DQ-WORK', 'Data Quality Workbook', 24.50, 0, '2026-08-01 09:00:00');INSERT INTO sales_order (order_id, external_ref, customer_id, status, total_amount, ordered_at)VALUES (100, 'WEB-100', 1, 'paid', 79.00, '2026-07-01 10:00:00'), (101, 'WEB-101', 2, 'draft', 0.00, '2026-07-10 11:00:00'), (102, 'WEB-102', 3, 'cancelled', 29.00, '2025-12-01 12:00:00'), (103, 'WEB-103', 4, 'paid', 69.00, '2026-08-01 13:00:00'), (104, 'WEB-104', 5, 'refunded', 15.00, '2025-11-15 14:00:00');INSERT INTO order_item VALUES (100, 1, 10, 1, 49.00), (100, 2, 13, 1, 30.00), (102, 1, 13, 1, 29.00), (103, 1, 11, 1, 69.00), (104, 1, 12, 1, 15.00);INSERT INTO customer_stage VALUES ('marta@example.com', 'Marta Costa', 'active'), ('reza@example.com', 'Reza Nouri', 'active'), ('lina@example.com', 'Lina Chen', 'active');INSERT INTO product_feed VALUES ('SQL-FOUND', 'Database Foundations', 52.00, 31, '2026-08-05 06:00:00'), ('SQL-CARD', 'SQL Reference Card', 15.00, 75, '2026-08-05 06:00:00'), ('SQL-OPS', 'Database Operations', 89.00, 12, '2026-08-05 06:00:00');The setup drops tables. Never execute training reset scripts against a production database.
Capstone practice
Prepare a runbook that marks order 101 paid, decrements one unit of SQL-QUERY inventory, records a request key, verifies all counts, and rolls back on any mismatch.
BEGIN IMMEDIATE;INSERT INTO change_request (request_key, operation)VALUES ('capstone-order-101-v1', 'Pay order 101 and reserve inventory')ON CONFLICT (request_key) DO NOTHING;UPDATE sales_orderSET status = 'paid', total_amount = 69.00WHERE order_id = 101 AND status = 'draft' AND total_amount = 0;UPDATE productSET stock_qty = stock_qty - 1, updated_at = CURRENT_TIMESTAMPWHERE sku = 'SQL-QUERY' AND stock_qty >= 1;SELECT order_id, status, total_amountFROM sales_orderWHERE order_id = 101;SELECT sku, stock_qtyFROM productWHERE sku = 'SQL-QUERY';PRAGMA foreign_key_check;-- COMMIT only after the application verifies every expected count.ROLLBACK;Checkpoint
Approve or roll back
- What does a transaction guarantee, and what does it not prove?
- Why acquire a write transaction before a long change workflow?
- What is the purpose of a savepoint?
- Why are row counts insufficient by themselves?
- What makes a change safely retryable?
Review the answers
A transaction supplies atomicity and isolation rules but does not prove business intent. Early lock acquisition exposes contention. Savepoints create internal rollback boundaries. Invariants and sampled values complement counts. Stable unique request identity or naturally idempotent predicates make retries recognizable.
Chapter 10 summary
- INSERT, UPDATE, and DELETE are set-based operations governed by constraints and predicates.
- Upsert behavior must be tied to an enforced unique identity.
- Retention, archival, and soft deletion are data-lifecycle choices.
- Transactions, assertions, evidence, and recovery plans form the safe-change workflow.
Chapter 11 moves from manipulating rows to defining databases, schemas, tables, constraints, and controlled schema evolution.