Deadlocks in a Stock Transfer Procedure
A logistics application uses InnoDB and a stored procedure transfer_stock(p_sku, p_from, p_to, p_qty) to move inventory between warehouses. The core table is stock(warehouse_id INT, sku VARCHAR(32), qty INT, PRIMARY KEY (warehouse_id, sku)). The procedure starts a transaction, runs SELECT qty INTO v_qty FROM stock WHERE warehouse_id = p_from AND sku = p_sku FOR UPDATE, checks availability, decrements the source row, increments the destination row, inserts into transfer_log, and commits. An EXIT HANDLER FOR SQLEXCEPTION rolls back and resigns the error. During a peak promotion, transfers between warehouse 10 and warehouse 20 run concurrently in both directions. The application logs many ERROR 1213 (40001): Deadlock found when trying to get lock failures, and users sometimes retry the whole call. The procedure is also called when the destination row may not exist yet.
1. Explain why concurrent transfers between warehouse 10 and warehouse 20 deadlock, and rewrite the locking step to reduce the chance.
Read the worked answer
Session A locks the source row (10, sku) and then tries to lock (20, sku). Session B locks (20, sku) and then tries to lock (10, sku). Each session holds one row and waits for the other, so InnoDB detects a cycle and rolls back one transaction with error 1213.
The fix is to acquire all required row locks in a deterministic order before updating. For example:
SELECT warehouse_id, qty
FROM stock
WHERE sku = p_sku
AND warehouse_id IN (p_from, p_to)
ORDER BY warehouse_id
FOR UPDATE;
Then compute the new quantities and apply the updates. Ordering by warehouse_id makes both sessions request the lower-numbered warehouse first, which removes the cycle for this pair of rows. If the destination row may be missing, the SELECT ... FOR UPDATE will not lock a nonexistent row; insert the destination row with INSERT ... ON DUPLICATE KEY UPDATE before or as part of the transfer, still respecting the same order. Deadlocks can still occur with other transactions, so retry handling is still required.
2. What does the `EXIT HANDLER FOR SQLEXCEPTION` actually do when a deadlock occurs, and where should retry logic live?
Read the worked answer
When InnoDB detects a deadlock, it rolls back one transaction automatically. The procedure's EXIT HANDLER then runs ROLLBACK and RESIGNAL. The extra ROLLBACK is harmless because the transaction is already gone, and RESIGNAL propagates the original error to the caller.
The procedure cannot safely retry the transfer inside the same call after a deadlock. The transaction has been aborted, the locks are released, and local variables may no longer represent committed state. Retry belongs in the application or calling layer, which can start a fresh transaction and rerun the procedure. Retry only on transient errors such as 1213 (deadlock) and 1205 (lock wait timeout), use a bounded number of attempts with backoff, and do not retry on business errors such as insufficient stock.
Because a transfer changes balances, retries must not double-apply. Add a client-supplied request_id with a unique key in transfer_log, and insert it as part of the transaction. If the retry hits a duplicate key, the transfer was already committed and the application can treat it as success.
3. The destination row may not exist. What bug does the current `UPDATE` have, and what is a safe MySQL 8.0 upsert pattern?
Read the worked answer
UPDATE stock SET qty = qty + p_qty WHERE warehouse_id = p_to AND sku = p_sku; affects zero rows when the destination does not exist, and MySQL does not raise an error by default. The transfer would decrement the source and silently fail to increment the destination.
Use an upsert after locking in the deterministic order:
INSERT INTO stock (warehouse_id, sku, qty)
VALUES (p_to, p_sku, p_qty) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;
The AS new alias form avoids the deprecated VALUES() function in MySQL 8.0.20 and later. The primary key (warehouse_id, sku) makes the duplicate-key path exact. If the destination row is missing, the insert creates it; if it exists, the update adds the quantity. Keep the corresponding source update in the same transaction, and ensure both rows are handled in a consistent order to avoid reintroducing the deadlock.