Managing MySQL Locks for Better Concurrency

When several requests read or change the same MySQL data, MySQL’s InnoDB storage engine uses locks to coordinate them. A transaction groups database operations into one unit and uses a lock to protect an index record or range. A competing transaction that needs the same data must wait, or handle an error if the query requests immediate failure. Long waits slow requests and can reduce how much work the database completes at once.

To reduce unnecessary waits, check which index records and ranges each statement scans. Then narrow the scan and keep transactions short. InnoDB’s default isolation level, REPEATABLE READ, also affects which ranges locking queries protect. Understanding these behaviors helps you choose indexes, transaction boundaries, and error handling that fit your workload.

The rows a query scans shape its locks

InnoDB uses row-level locking, but attaches locks to records in indexes, the structures MySQL uses to find rows. A record lock protects an index record. A gap lock prevents inserts into an interval between index records. A next-key lock combines a record lock with the gap before it. Under REPEATABLE READ, InnoDB uses next-key locks for searches and index scans in applicable cases to prevent another transaction from inserting a row into a range that has been searched. The InnoDB locking documentation describes these lock types and their behavior.

That is why a statement can lock more rows than it changes. When a query uses an index with unique values to find one row, it generally locks only the matching record. A range search or search through a nonunique index can lock the scanned range. If MySQL cannot use a suitable index and scans the whole table, it can lock every row it encounters, blocking inserts as well as updates to those rows. The transaction isolation documentation explains how search conditions and index access affect locking.

For a slow UPDATE, DELETE, or SELECT ... FOR UPDATE, inspect the query plan and confirm that indexes support the filter and join conditions. An index helps when MySQL uses it to examine fewer records; its mere presence does not guarantee a narrow lock footprint. An exclusive search through a secondary index can also lock corresponding records in the clustered index, where InnoDB stores the table rows. See which locks InnoDB statements set.

Keep transactions short and acquire locks consistently

A transaction holds its locks until it commits or rolls back. Finish database work promptly after the first change: avoid waiting on network calls, user input, or unrelated computation before committing. Smaller write transactions hold fewer locks for less time, leaving fewer opportunities for other requests to queue behind them.

When transactions touch multiple rows or tables, make them acquire locks in the same order. For example, if one code path updates a customer row and then an account row, another path that needs both should use that same order. A deadlock occurs when transactions form a cycle of waits, with each waiting for a lock held by another. Consistent ordering, suitable indexes, and smaller transactions reduce deadlock risk, as MySQL’s InnoDB deadlock guidance recommends.

InnoDB detects deadlocks by default and rolls back one transaction to break the cycle. The application should retry the entire transaction after a deadlock, because the victim transaction has been rolled back. Retrying only the statement that failed can leave the intended group of changes incomplete.

Change isolation only when its read behavior fits

InnoDB uses REPEATABLE READ by default. Ordinary consistent reads in a transaction use a snapshot, a view of the data from a point in time. Locking reads and writes follow locking rules that can include gap locks. At READ COMMITTED, each consistent read gets a fresh view of the data, and InnoDB disables ordinary gap locking for searches and scans. Gap locks still apply to foreign-key and duplicate-key checks. Another transaction can also insert rows that match an earlier search, so phantom rows can occur under this isolation level.

READ COMMITTED also releases locks on rows that do not match the WHERE condition after MySQL evaluates an UPDATE or DELETE, which can reduce deadlock probability in those cases. This does not fix deadlocks in general. Consider the change when fresh snapshots and the possibility of phantoms fit the application’s data requirements, and verify the replication requirements for your configuration before adopting it.

Use immediate lock outcomes only for suitable queries

A locking read with NOWAIT returns an error instead of waiting for a locked row. Use it when the caller can treat “busy now” as a normal outcome, such as returning a retryable response or choosing other work.

SKIP LOCKED returns rows that are not currently locked and omits the rest. That behavior suits some queue workers that claim separate jobs, provided each worker claims a row and changes its status in a transaction. It gives an inconsistent view of the table, so avoid it when the query must return a complete or logically consistent result. Both options apply to row-level locks and are unsafe for statement-based replication. MySQL documents these constraints in its section on locking reads.

Find the blocker before changing settings

To investigate current InnoDB lock waits, inspect sys.innodb_lock_waits. It summarizes which sessions are waiting and which are blocking them. Start with this query:

SELECT
  waiting_pid,
  waiting_query,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

A NULL blocking query does not prove that no session holds the lock; the blocking session may be idle rather than running a statement. For more detail, use INFORMATION_SCHEMA.INNODB_TRX to inspect transactions. Use performance_schema.data_locks and performance_schema.data_lock_waits to inspect locks and waits. The locking information examples show how to use these sources.

For the most recently detected deadlock, run SHOW ENGINE INNODB STATUS. To send all deadlock reports to the error log, MySQL provides innodb_print_all_deadlocks. If an ALTER TABLE or another statement that changes database definitions stalls, check metadata locks too. These locks protect database objects, so they are different from InnoDB row-lock waits. MySQL exposes them through performance_schema.metadata_locks; see the metadata locking documentation.

Handle deadlocks and timeouts differently

In MySQL 8.4, the documented default for innodb_lock_wait_timeout is 50 seconds. Check the deployed server’s setting before treating that value as its actual timeout. A row-lock timeout ordinarily rolls back the current statement, while a deadlock rolls back the victim transaction. Application error handling should distinguish the two and follow the server’s configured rollback behavior. The InnoDB parameter reference documents the timeout; MySQL’s error-handling documentation describes rollback behavior.

A lower timeout shortens a request’s wait while leaving the underlying lock conflict in place. Disabling deadlock detection is also a specialized tradeoff. MySQL notes that detection can become costly when many threads wait on the same lock, but relying on timeouts instead means deadlocked work takes longer to clear. Consider that change only after measuring the workload and confirming that the application handles the resulting errors.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com