InnoDB uses Multi-Version Concurrency Control (MVCC) to allow readers to see a consistent snapshot without blocking writers, which dramatically improves concurrency compared to simple table-level locking. To support this, InnoDB retains old row versions in the undo log until no transaction needs them anymore. Long-running transactions are therefore dangerous: they keep old versions alive, grow the undo log, and prevent purge from reclaiming space. The same transactions also hold locks longer, increasing contention. Keeping autocommit enabled for OLTP workloads keeps individual statements short, reduces lock hold time, and minimizes undo and redo pressure; disabling autocommit and forgetting to commit is one of the most common causes of mysterious performance and stability problems.
Isolation levels control how transactions interact. MySQL's InnoDB default, REPEATABLE READ, prevents dirty reads and non-repeatable reads within a transaction but uses gap locks to prevent phantom reads, which can reduce concurrency by blocking inserts into ranges. Switching to READ COMMITTED reduces gap locking and often improves throughput, at the cost of weaker consistency guarantees. READ UNCOMMITTED allows dirty reads and is rarely appropriate, while SERIALIZABLE is the strictest and the most contention-prone. Higher isolation levels generally increase lock hold times and reduce parallelism, so the choice of level should reflect the application's actual consistency needs rather than defaulting to the strictest option. Read-only transactions declared with START TRANSACTION READ ONLY can sometimes get optimized execution paths and avoid accidental writes.
Pessimistic locking also deserves attention. SELECT ... FOR UPDATE acquires row locks even for reads and can quickly become a hotspot in high-traffic code paths; it is appropriate only when strong consistency is essential for a critical invariant, in which case optimistic concurrency with version columns and conditional UPDATE or INSERT can avoid the lock overhead entirely. Deadlocks occur when transactions hold locks the others need in a cycle, and InnoDB detects and rolls back one of them; their likelihood can be reduced by accessing rows in a consistent order, keeping transactions short, and avoiding unnecessary locking. Metadata locks, which protect table definitions, can also stall DDL when long-running queries hold them; online DDL operations governed by ALGORITHM and LOCK clauses in ALTER TABLE minimize this impact but still require testing on large tables. Frequent or long lock waits indicate contention that can throttle throughput and increase latency, and they can be observed through performance_schema tables such as data_locks and data_lock_waits, along with related sys schema views. Transactions themselves should be kept small and short-lived, because overly large transactions hold locks longer, increase contention, grow the undo and redo logs, and make rollbacks more expensive. Finally, MAX_EXECUTION_TIME provides a safety net by aborting runaway SELECT statements, though it should be tuned carefully to avoid killing legitimate long jobs.