Database Transactions: Keeping Changes Together
Understand commit and rollback through a stock transfer between locations.
You are reading a translated version.
One operation, several changes
Moving stock means reducing the quantity at its source and increasing it at its destination. If only one change succeeds, inventory records become inconsistent.
START TRANSACTION;
UPDATE inventory SET quantity = quantity - 2
WHERE id = 1 AND quantity >= 2;
-- Aplikasi harus memastikan tepat satu baris berubah.
UPDATE inventory SET quantity = quantity + 2
WHERE id = 2;
-- COMMIT hanya bila kedua perubahan memenuhi aturan.
COMMIT;
The comments in the original example require the application to verify that exactly one row changes and to commit only when both updates satisfy the rules.
Check results, not just errors
A query affecting zero rows does not necessarily raise an SQL error. The application must check the affected-row counts and issue ROLLBACK if source stock is insufficient or the destination cannot be found.
Use a transactional engine
MySQL's InnoDB tables support transactions. Make sure every participating table uses a suitable engine.
Consider concurrency
Concurrent requests may affect the same data. Row locks and a consistent update order help coordinate changes. Applications should also handle deadlocks with bounded retries.