MySQL中表锁与行锁的区别、适用场景及控制主体解析

Hey Jonez, great question—table vs row locks are a foundational part of MySQL concurrency control, so it makes total sense to want clarity here. Let’s break this down step by step:

Your initial understanding is mostly on point, with a few key details to add:

• 表锁: Locks an entire table outright. Once applied (either as a read or write lock), no other transaction can modify the table (for write locks) or write to it (for read locks) until the lock is released. MySQL’s older MyISAM storage engine relies exclusively on table locks.
• 行锁: Locks only specific rows a transaction is accessing. All other rows in the table remain available to other transactions—this is what enables high concurrency in modern systems. InnoDB, MySQL’s default modern engine, uses row-level locking.

A critical caveat: Row locks can escalate to table locks if your query doesn’t use an index. InnoDB can’t efficiently target specific rows without an index, so it falls back to locking the entire table.

表锁 is a better fit when: #

• You’re running bulk operations that touch most or all rows (e.g., UPDATE users SET status = 'inactive' WHERE last_login < '2020-01-01'). Table locks avoid the overhead of locking hundreds/thousands of individual rows.
• You’re using a storage engine that doesn’t support row locks (like MyISAM).
• The table is very small—locking the whole table is faster than managing row-level locks.

行锁 is ideal for: #

• High-concurrency OLTP systems (e.g., e-commerce order processing, user profile updates). Most operations here target single rows or small subsets, so row locks let multiple transactions run in parallel without blocking each other.
• Scenarios where you need to minimize contention—like when multiple users are updating different customer records at the same time.

It’s a mix of automatic storage engine behavior and manual configuration:

• Automatic control by storage engines: InnoDB automatically uses row locks for transactional operations (like INSERT, UPDATE, DELETE) when indexes are used. MyISAM automatically uses table locks for all write operations.
• Manual control by developers/DBAs: You can explicitly manage locks using SQL commands:
  • Use LOCK TABLES my_table READ or LOCK TABLES my_table WRITE to manually apply table locks.
  • Use SELECT ... FOR UPDATE to explicitly lock specific rows for write operations (prevents other transactions from modifying those rows until your transaction finishes).
  • Use SELECT ... LOCK IN SHARE MODE to apply a shared read lock (other transactions can read the rows but can’t modify them).
• You can also influence lock behavior via transaction isolation levels (e.g., InnoDB’s gap locks are enabled by default in the REPEATABLE READ level to prevent phantom reads).

• Lock granularity tradeoffs: Table locks have low overhead but kill concurrency; row locks enable high concurrency but have higher overhead (and risk of deadlocks if not managed carefully).
• Deadlocks with row locks: Since multiple transactions can lock different rows, it’s possible for them to end up waiting for each other’s locks (e.g., Transaction 1 locks row A and waits for row B; Transaction 2 locks row B and waits for row A). InnoDB automatically detects and resolves deadlocks by rolling back one of the transactions.
• Gap locks: InnoDB uses gap locks (locks on the space between index values) in REPEATABLE READ mode to prevent phantom reads. This means even if a row doesn’t exist yet, the gap where it would be might be locked.

内容的提问来源于stack exchange，提问作者Jonez