Analyzing High CPU Usage of lock_number_of_rows_locked in MySQL 5.6 Large Transactions

Background : A friend encountered a performance problem when rolling back an online transaction of about 100 GB on MySQL 5.6. Even after disabling the "double‑1" settings (sync_binlog=1 and innodb_flush_log_at_trx_commit=1), the rollback remained slow and impacted the production service.

Why large transactions are problematic in MySQL : Large transactions can cause heavy binlog I/O, increase master‑slave replication lag, block backups (flush‑table‑with‑read‑lock), enlarge InnoDB row‑lock ranges, and make rollbacks difficult.

Analysis : Monitoring tools (vmstat, iostat, top) showed that I/O was not the bottleneck; the wa% was low while a single CPU core was at 99.4 % usage. The focus therefore shifted to CPU consumption, specifically the lock_number_of_rows_locked function reported by perf top .

Function lock_number_of_rows_locked in MySQL 5.7.26 simply returns a pre‑computed counter:

return(trx_lock->n_rec_locks);

This counter is incremented each time a row lock is acquired, making the operation O(1) and inexpensive.

Function lock_number_of_rows_locked in MySQL 5.6.22 iterates over every lock structure and counts set bits:

for (lock = UT_LIST_GET_FIRST(trx_lock->trx_locks); lock != NULL; lock = UT_LIST_GET_NEXT(trx_locks, lock)) {
    if (lock_get_type_low(lock) == LOCK_REC) {
        ulint n_bits = lock_rec_get_n_bits(lock);
        for (n_bit = 0; n_bit < n_bits; n_bit++) {
            if (lock_rec_get_nth_bit(lock, n_bit)) {
                n_records++;
            }
        }
    }
}
return(n_records);

This nested loop is CPU‑intensive; when a large transaction holds many row locks, the counting can consume a significant portion of CPU, as observed in the perf top output.

Root causes identified:

Running MySQL 5.6.

Presence of a very large transaction (~100 GB) that acquires many row locks.

Frequent use of SHOW ENGINE INNODB STATUS , which triggers the lock‑counting routine.

Solution : Stop the monitoring tool that repeatedly calls SHOW ENGINE INNODB STATUS , and upgrade MySQL to 5.7 or 8.0 where the lock‑counting algorithm is O(1). After the upgrade, CPU usage dropped dramatically and I/O utilization improved.

Additional notes : The issue is tracked as MySQL bug #68647 , which was fixed by changing the algorithm in later versions. The article also recommends the book “深入理解 MySQL 主从原理 32 讲” for deeper understanding of MySQL replication.