Row Lock Wait Contention on Hot Tables
warningInnoDB row-level locking can cause queries to wait when multiple transactions attempt to modify the same rows. This creates visible latency spikes and reduced throughput on write-heavy workloads with high concurrency on specific tables.
Track innodb_row_lock_waits metric. If this counter increases rapidly under load, queries are frequently blocked waiting for row locks. Cross-reference with query latency metrics and look for slow query patterns involving UPDATE, DELETE, or SELECT FOR UPDATE. Check performance_schema for wait events on wait/synch/mutex/innodb/trx_sys_mutex or wait/lock/table/* events.
Identify hot rows/tables using performance_schema queries or sys schema views (sys.statements_with_runtimes_in_95th_percentile). Optimize transaction logic to minimize lock hold time—commit faster, batch smaller, or use optimistic locking patterns. Consider partitioning hot tables, adding covering indexes to reduce rows scanned, or refactoring schema to reduce contention. For extreme cases, evaluate read replicas to offload read traffic or application-level sharding.