How to Prevent MySQL Deadlocks in High‑Concurrency Apps: 5 Proven Solutions

MySQL deadlock (DeadLock) occurs when two or more transactions each hold locks needed by the other and wait indefinitely, causing the database to halt the involved transactions.

For example, transaction A locks record 1 and waits for record 2, while transaction B locks record 2 and waits for record 1, forming a circular wait.

Solution 1: Unified lock order – Ensure all transactions acquire locks in the same sequence (e.g., always lock the row with the smaller id first). This greatly reduces the chance of circular waiting.

-- transaction A and transaction B must follow this order
START TRANSACTION;
SELECT * FROM users WHERE id=1 FOR UPDATE;
SELECT * FROM accounts WHERE user_id=1 FOR UPDATE;
-- business logic...
COMMIT;

Solution 2: Shorten transactions – Keep transactions brief by moving time‑consuming RPC calls, heavy calculations, batch processing, or user‑interaction logic outside the transaction.

External operations such as API calls or large computations should be performed before or after the transaction.

Short transactions reduce lock‑holding time and shrink the deadlock window.

-- time‑consuming work outside the transaction
SELECT quantity FROM stock WHERE goods_id=1;
CALL external_notify();

-- extremely short transaction
START TRANSACTION;
UPDATE stock SET quantity = quantity - 1 WHERE goods_id=1 AND quantity >= 1;
UPDATE orders SET status='paid' WHERE id=xxx;
COMMIT;

Solution 3: Optimize indexes – Make sure SQL statements can use appropriate indexes to avoid full‑table scans and large lock ranges.

-- create a composite index
ALTER TABLE orders ADD INDEX idx_user_ct (user_id, create_time);

-- optimized SQL (range greatly reduced)
UPDATE orders SET status='paid' WHERE user_id = 100 AND create_time='2025-06-09' LIMIT 10;

For high‑frequency UPDATE/DELETE statements, verify the execution plan to ensure indexes are used, preventing excessive lock contention.

Solution 4: Avoid large‑scale SQL – Split “big SQL” into smaller batches, reducing the number of rows locked in a single transaction. Small batches with retry logic are more stable under high concurrency.

Solution 5: Application‑level retry – Treat deadlocks as recoverable errors; for idempotent transactions, catch the deadlock error, apply random back‑off, and retry the transaction.