Articles

MySQL Table Locks vs Row Locks: Diagnose and Avoid Contention (2026)

April 16, 2026 7 min read

MySQL’s locking mechanism isn’t just about preventing data corruption; it’s a performance bottleneck you’re likely wrestling with right now.

Let’s see it in action. Imagine two concurrent transactions trying to update the same row in a users table.

-- Transaction 1
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE user_id = 123;
-- ... other operations ...
COMMIT;

-- Transaction 2
START TRANSACTION;
UPDATE users SET balance = balance + 100 WHERE user_id = 123;
-- ... other operations ...
COMMIT;

If user_id = 123 is being updated, one of these transactions will acquire a lock on that specific row. The other transaction will then wait. If this wait is too long, you’ll see SHOW ENGINE INNODB STATUS report a deadlock, or your application will time out waiting for the lock to be released. The core issue is that while one transaction holds a lock on a specific row, other transactions needing to access that same row are blocked.

The fundamental difference lies in the granularity of protection.

  • Table Locks: The entire table is locked. If any transaction needs to read or write any row in that table, it must wait until the lock is released. This is the broadest, and often most damaging, type of lock.
  • Row Locks: Only the specific row(s) being accessed are locked. Other transactions can still read or write different rows within the same table concurrently. This is the default and preferred behavior for InnoDB.

The problem arises when queries, especially those run within transactions, inadvertently escalate to table locks, or when a high volume of row lock requests creates contention.

Diagnosing Lock Contention

The primary tool for diagnosing lock issues in InnoDB is SHOW ENGINE INNODB STATUS. Look for the LATEST DETECTED DEADLOCK and TRANSACTIONS sections.

1. Identifying Row Lock Waits: If you see transactions listed in the TRANSACTIONS section with a LOCK WAIT status, it means they are waiting for another transaction to release a lock. The output will show the transaction ID and the lock it’s waiting for.

Diagnosis Command:

SHOW ENGINE INNODB STATUS;

What to Look For: In the TRANSACTIONS section, find the LOCK WAIT entry. Note the transaction id and the index and record it’s waiting on. This tells you which specific row is causing the blockage.

2. Identifying Table Lock Waits (Less Common with InnoDB, but possible with MyISAM or explicit LOCK TABLES): If you’re using MyISAM or have explicitly used LOCK TABLES, you’ll see table-level locks reported. For InnoDB, this usually implies a very broad scan or an explicit LOCK TABLES statement.

Diagnosis Command:

SHOW OPEN TABLES WHERE In_use > 0;

What to Look For: This command shows tables that are currently locked. For InnoDB, In_use usually indicates a lock is held by an active transaction.

3. Analyzing Slow Queries: Long-running queries are prime candidates for holding locks for extended periods.

Diagnosis Command:

SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE digest_text LIKE 'SELECT % FROM your_table%' OR digest_text LIKE 'UPDATE % FROM your_table%' OR digest_text LIKE 'DELETE % FROM your_table%' ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Replace your_table with the table name you suspect is involved.

What to Look For: Queries with high SUM_TIMER_WAIT (total execution time) and high COUNT_STAR (number of executions) that are operating on the same data are likely contributing to contention.

Common Causes and Fixes

Cause 1: Inefficient Queries Holding Locks Too Long A SELECT query that performs a full table scan can acquire a table lock (in older MySQL versions or with specific isolation levels) or hold many row locks for an unnecessarily long time.

  • Diagnosis: Use EXPLAIN on the slow query. If it shows type: ALL (full table scan) or type: index (full index scan) on a large table, and the query is part of a transaction that’s causing waits, this is your culprit.
  • Fix: Add appropriate indexes. For example, if your query is SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31', ensure there’s an index on order_date.
    ALTER TABLE orders ADD INDEX idx_order_date (order_date);
  • Why it works: An index allows MySQL to quickly locate the specific rows needed without scanning the entire table or index, drastically reducing the time locks are held.

Cause 2: Updating the Same Row Concurrently Multiple transactions attempting to update the exact same row can lead to lock contention, especially if the updates are part of longer transactions.

  • Diagnosis: SHOW ENGINE INNODB STATUS will show LOCK WAIT transactions pointing to the specific record being contested.
  • Fix:
    • Batch Updates: If possible, group similar updates together. Instead of 100 individual updates to the same row, try a single, more complex update if your logic allows, or process them in a way that reduces the number of concurrent attempts.
    • Optimistic Locking: Implement versioning. Add a version column to your table. When updating, fetch the current version, increment it, and update using WHERE id = ? AND version = ?. If the WHERE clause doesn’t match (because another transaction updated it and incremented the version), the update fails, and you can retry.
    -- Example of optimistic locking update
UPDATE users SET balance = balance - 100, version = version + 1
WHERE user_id = 123 AND version = 5; -- Assuming current version was 5
  • Why it works: Batching reduces the number of lock requests. Optimistic locking avoids blocking by failing fast if the data has changed, allowing the application to handle the conflict.

Cause 3: Long-Running Transactions Transactions that perform many operations or wait for external resources before committing can hold locks for extended periods, blocking other operations.

  • Diagnosis: SHOW ENGINE INNODB STATUS will show ACTIVE transactions with large waktu values (time in seconds since start).
  • Fix: Keep transactions as short as possible. Perform non-database operations (like calling external APIs, reading files) outside the transaction. Commit or rollback promptly.
    -- Bad:
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
-- Call external payment gateway (can take seconds)
-- ...
COMMIT;

-- Better:
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
COMMIT;
-- Call external payment gateway (outside transaction)
-- ...
  • Why it works: Minimizing the duration locks are held reduces the window for contention.

Cause 4: Deadlocks Deadlocks occur when two or more transactions are waiting for each other to release locks.

  • Diagnosis: SHOW ENGINE INNODB STATUS will explicitly report LATEST DETECTED DEADLOCK. It provides a detailed graph of the transactions involved and the locks they hold and request.
  • Fix:
    • Consistent Access Order: Ensure all transactions that access multiple resources do so in the same order. If Transaction A updates Table X then Table Y, and Transaction B updates Table Y then Table X, they can deadlock. If both always update X then Y, deadlocks are less likely.
    • Retry Logic: Implement retry mechanisms in your application for transactions that fail due to deadlocks. InnoDB automatically rolls back one of the deadlocked transactions.
  • Why it works: Consistent access order prevents circular dependencies. Retry logic allows the application to recover from the automatic rollback.

Cause 5: Implicit Table Locks (MyISAM or specific DDL) While InnoDB primarily uses row-level locking, MyISAM tables use table-level locking. Certain Data Definition Language (DDL) operations can also briefly lock tables.

  • Diagnosis: SHOW OPEN TABLES WHERE In_use > 0; will show MyISAM tables locked, or SHOW PROCESSLIST; might show a Waiting for table metadata lock state.
  • Fix: Migrate MyISAM tables to InnoDB. For DDL, schedule such operations during low-traffic periods.
    ALTER TABLE your_table ENGINE=InnoDB;
  • Why it works: InnoDB’s row-level locking is far more concurrent. Migrating to InnoDB is a fundamental step towards scalability.

Cause 6: SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE These clauses explicitly request locks on rows read by a SELECT statement. If used excessively or on broad ranges, they can cause contention.

  • Diagnosis: Examine your application code for these clauses. Check SHOW ENGINE INNODB STATUS for LOCK WAIT transactions that might be originating from SELECT statements.
  • Fix: Use these clauses only when absolutely necessary to prevent race conditions. Ensure the WHERE clause is highly selective and backed by an index. Consider if a transaction with explicit locking is truly required, or if application-level logic can suffice.
    -- Only use if you truly need to lock these rows for update by this transaction
SELECT * FROM accounts WHERE account_id = 5 FOR UPDATE;
  • Why it works: These statements are powerful but must be used judiciously. Limiting their scope to essential, indexed rows minimizes blocking.

The next challenge you’ll encounter is managing the overhead of these row locks, especially under very high concurrency, leading to issues like transaction isolation violations or increased latency in SHOW ENGINE INNODB STATUS.

Want structured learning?

Take the full Express course →