Transaction Patterns
Common patterns include bank transfer, inventory reservation, idempotent upsert, and deferred constraint checking.
MySQL
Intermediate
4 min read
Common Transaction Patterns
Example
-- Idempotent order creation (safe to retry)
INSERT INTO orders (id, user_id, status) VALUES (UUID(), 1, "pending")
ON DUPLICATE KEY UPDATE status = VALUES(status);
-- Inventory reservation with rollback safety
START TRANSACTION;
UPDATE inventory SET reserved = reserved + 1, available = available - 1
WHERE product_id = 5 AND available >= 1;
SELECT ROW_COUNT() INTO @updated;
IF @updated = 0 THEN ROLLBACK; ELSE COMMIT; END IF; Pro Tip
ON DUPLICATE KEY UPDATE makes inserts idempotent — safe to replay on network timeout.