How Disabling InnoDB Deadlock Detection Supercharges MySQL 5.6 in Flash‑Sale Workloads

Introduction

The author modifies MySQL 5.6.19 and Percona 5.6.25 by integrating Facebook’s deadlock‑detection‑disable patch, then runs a series of sysbench benchmarks that mimic a flash‑sale (hot‑update) situation where many concurrent transactions decrement a single stock record.

Test Versions

MySQL 5.6.19 native

MySQL 5.6.19 with deadlock detection disabled (5.6.1.9+no deadlock check ndlc)

Percona 5.6.25 native (percona+tp)

Percona 5.6.25 with deadlock detection disabled (percona+tp+nldc)

Test Environment

Benchmarks were executed with sysbench 0.5, isolation level READ‑COMMITTED, on a 32‑core CPU, 15 GB RAM, data stored on SSD and logs on SAS disks. The workload consists of a single UPDATE statement that decrements a stock column.

CREATE TABLE `test_update` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stock` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO test_update (id, stock) VALUES (1,500000);
UPDATE test_update SET stock = stock - 1 WHERE id = 1 AND stock > 3;

Percona thread‑pool parameters used:

SET GLOBAL thread_pool_size=16;
SET GLOBAL thread_pool_oversubscribe=1;
SET GLOBAL innodb_thread_concurrency=32;

Metrics Definitions

TPS : transactions per second reported by sysbench.

Time : total time to reduce stock by 500 k units.

threads_running : active MySQL threads.

connection : total client connections.

response_time : 99th‑percentile response time (ms).

avg_rt : average response time (ms).

thread_handling : MySQL thread model (one‑thread‑per‑connection in this test).

innodb_thread_concurrency : InnoDB concurrency limit (set to CPU core count).

Performance Results

Benchmark results for 8, 16, 32, 64, 128, 512, 1024, 2048, and 4096 concurrent threads are plotted in the following charts:

For thread counts above 256, the charts show that disabling deadlock detection dramatically improves TPS, especially for the native MySQL build.

Observations by Client Count

8 clients: native MySQL has the highest TPS, but such low concurrency is unrealistic for production.

16–32 clients: native version still leads.

64–128 clients: native MySQL with deadlock detection disabled achieves the highest TPS.

1024 clients: native MySQL with deadlock detection disabled reaches 3 924 TPS (≈120× improvement over the default 30 TPS).

Deadlock Detection Mechanism

InnoDB builds a wait‑for graph for each lock request; if a cycle is found, the transaction with the smallest undo log is rolled back. The detection itself consumes CPU, and in hot‑update scenarios the large lock queue makes detection a bottleneck.

The original MySQL 5.7.15 merged Facebook’s innodb_deadlock_detect patch, which allows the feature to be turned off via a system variable. The author applied the same patch to MySQL 5.6.19 and Percona 5.6.25.

Thread Handling Options

MySQL supports three thread‑handling modes:

no‑threads : single‑threaded debug mode.

one‑thread‑per‑connection : a dedicated thread per client (default for low concurrency).

dynamically‑loaded (thread pool): a pre‑allocated pool of worker threads that serve connections, reducing context‑switch overhead at high concurrency.

MySQL Enterprise 5.5 offers a proprietary thread‑pool plugin; MariaDB open‑sourced a similar implementation, which Percona 5.6.25 incorporates.

Conclusions

Disabling InnoDB deadlock detection in MySQL 5.6.19 raises TPS from ~30 to ~3 924 at 1 024 concurrent clients, a 120‑fold gain without code changes.

Percona’s built‑in thread pool further stabilizes performance; with both thread pool and deadlock detection disabled, TPS approaches the native MySQL result.

For micro‑service architectures with many DB connections, upgrading to Percona (or MySQL 8 with thread pool) is recommended for stability.

Applying the deadlock‑disable patch is low‑risk for existing applications, but thorough testing is advised.