✏️ Explanatory Question
Level: Very Hard — A critical real-world design problem; network timeouts make "did it succeed?" ambiguous, and naive retries double-charge customers.
The core concept — idempotency: An operation is idempotent if performing it multiple times has the same effect as performing it once. The standard technique is an idempotency key: the client generates a unique key per logical action, and the server uses a UNIQUE constraint to guarantee the action executes only once.
UNIQUE index on the idempotency key means the second (retried) insert fails with a duplicate-key error instead of creating a second charge — atomic and race-proof.
paymentsCREATE TABLE payments (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
idempotency_key CHAR(36) NOT NULL, -- client-generated UUID
customer_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status ENUM('pending','completed','failed') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_idempotency (idempotency_key) -- THE safety guarantee
) ENGINE=InnoDB;-- Client generates ONE key for the logical payment, reused on every retry
-- e.g., idempotency_key = 'a1b2c3d4-...-uuid'
-- APPROACH 1: INSERT ... ON DUPLICATE KEY UPDATE (no-op on retry)
INSERT INTO payments (idempotency_key, customer_id, amount, status)
VALUES ('a1b2c3d4-uuid', 42, 5000.00, 'completed')
ON DUPLICATE KEY UPDATE id = id; -- retry hits existing row, does nothing
-- APPROACH 2: INSERT IGNORE, then read back the (single) row
INSERT IGNORE INTO payments (idempotency_key, customer_id, amount, status)
VALUES ('a1b2c3d4-uuid', 42, 5000.00, 'completed');
SELECT id, status FROM payments WHERE idempotency_key = 'a1b2c3d4-uuid';
-- First call inserts & returns it; retries just return the same rowSTART TRANSACTION;
-- 1) Claim the idempotency key. If it already exists, we stop.
INSERT INTO payments (idempotency_key, customer_id, amount, status)
VALUES ('a1b2c3d4-uuid', 42, 5000.00, 'pending');
-- If this throws duplicate-key (error 1062), ROLLBACK and return the
-- existing payment's result -> the retry is a safe no-op.
-- 2) Do the actual work exactly once (atomic decrement)
UPDATE wallets SET balance = balance - 5000.00
WHERE customer_id = 42 AND balance >= 5000.00;
-- 3) Mark completed
UPDATE payments SET status = 'completed'
WHERE idempotency_key = 'a1b2c3d4-uuid';
COMMIT;SELECT ... IF NOT EXISTS THEN INSERT is not safe — two requests can both pass the SELECT before either inserts, creating duplicates. Always let the UNIQUE constraint do the enforcement atomically.