Problem Statement
Outline a safe pattern to perform a multi-step change: insert a row, update a counter, and read back the result.
Explanation
Wrap the steps in a transaction so all succeed or all roll back. Insert the detail row, update the aggregate with a precise predicate, then select the final state to confirm. Keep the transaction short to reduce lock time.
Check for errors at each step and commit only when all statements succeed. This preserves consistency under concurrent load.
Code Solution
SolutionRead Only
BEGIN; INSERT INTO orders(id, customer_id, total) VALUES(?, ?, ?); UPDATE customers SET order_count = order_count + 1 WHERE id = ?; SELECT order_count FROM customers WHERE id = ?; COMMIT;
