A query doesn't just "run." It passes through five distinct stages before a single row reaches you — and almost every production incident traces back to one of them. This is the actual flow, followed by the dos and don'ts mapped to the exact stage that causes them.
Click any stage below to see what it actually does, and — for the two stages marked RISK — exactly how it takes prod down when nobody's watching.
click a stage above ↑ — stages 04 and 05 are where almost every prod incident actually happens
None of these are arbitrary house rules. Each one exists because of something specific that happens at steps 4 or 5 of the flow you just walked through.
The optimizer decides whether your query touches 50 rows or 5 million. EXPLAIN shows you its decision before you commit to it on prod. If you see type: ALL on a large table — that's the optimizer telling you, in plain text, that it found no usable index and intends to scan every row.
This is where row and table locks actually live. A write here doesn't just change data — it holds a lock for as long as the statement runs, and every other transaction wanting that same data queues up behind it.
Commit and rollback happen here, along with isolation-level bookkeeping. A transaction you forgot to close — even a plain SELECT under REPEATABLE READ — keeps an MVCC snapshot pinned open, which can block cleanup processes from reclaiming space.
Click to expand. Each one names the stage it protects and shows the actual query pattern.
Run EXPLAIN on every query that touches a table you don't fully trust the size of. If the plan shows type: ALL on something like OFBiz's OrderHeader or ReturnItem, stop — that means a full table scan, which on a large table means scanning and potentially locking millions of rows.
EXPLAIN SELECT * FROM ReturnItem WHERE returnId = 'X'; -- look at the "type" column: const/eq_ref/ref = good, ALL = full scan
A single big write holds row or table locks for its entire duration, queueing every other transaction touching that table behind it. This is the most common cause of "the SQL didn't even error but prod froze."
-- dangerous: locks every matching row at once, possibly the whole table UPDATE OrderItem SET statusId = 'ITEM_CANCELLED' WHERE orderId = 'X'; -- safer: batch it UPDATE OrderItem SET statusId = 'ITEM_CANCELLED' WHERE orderId = 'X' LIMIT 500; -- repeat with monitoring, or loop with a short sleep between batches
Give yourself an exit. Run the write, sanity-check what actually changed, and only then decide whether to keep it.
START TRANSACTION; UPDATE ...; -- check row count, sanity check results COMMIT; -- or ROLLBACK if something looks wrong
An open transaction — even a read-only SELECT under REPEATABLE READ — holds onto an MVCC snapshot. That snapshot can block the engine's internal cleanup processes from reclaiming old row versions. Always commit or roll back promptly; don't leave a session idle mid-transaction while you go check something else.
Most "slow query brought down prod" stories trace back to a missing index on a JOIN or WHERE column — not clever-but-wrong SQL syntax. Check the indexes before you start rewriting query logic.
SHOW INDEX FROM tablename;
If a replica exists, point your SELECT-heavy investigation and reporting queries there. That keeps ad-hoc OFBiz reporting from competing with live order processing for locks and I/O on the primary.
Run the read version of your write first. Confirm the exact row count and content it would touch — only convert it to a write once that result looks right.
-- run this first to see exactly what you'd affect SELECT * FROM ReturnItem WHERE returnId = 'X'; -- then convert to UPDATE only after confirming row count/content
For a much deeper dive into database internals — storage engines, indexing strategy, and how production systems actually behave under load — Arpit Bhayani's writing is one of the best ongoing resources on the subject: arpitbhayani.me