MySQL 为什么大表的表级锁比行级锁好？

Why is table-level locking better than row-level locking for large tables?

提问by Jason Baker

According to the MySQL manual:

根据MySQL 手册：

For large tables, table locking is often better than row locking,

对于大表，表锁往往比行锁好，

Why is this? I would presume that row-level locking is better because when you lock on a larger table, you're locking more data.

为什么是这样？我认为行级锁定更好，因为当你锁定一个更大的表时，你锁定了更多的数据。

采纳答案by μBio

from the (pre-edit) link

从（预编辑）链接

Slower than page-level or table-level locks when used on a large part of the table because you must acquire many more locks

当用于表的大部分时，比页级或表级锁慢，因为您必须获得更多的锁

use a row level lock if you are only hitting a row or two. If your code hits many or unknown rows, stick with table lock.

如果您只点击一两行，请使用行级锁。如果您的代码遇到许多行或未知行，请坚持使用表锁。

回答by DVK

• Row locking needs more memory than table or page level locking.

• Have to acquire many more locks with row locking, which expends more resources

• 行锁定比表或页级锁定需要更多的内存。

• 必须使用行锁获取更多锁，这会消耗更多资源

From http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/

来自http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/

• Advantages of row-level locking:

  • Fewer lock conflicts when accessing different rows in many threads.
  • Fewer changes for rollbacks.
  • Makes it possible to lock a single row a long time.

• Disadvantages of row-level locking:

  • Takes more memory than page-level or table-level locks.
  • Is slower than page-level or table-level locks when used on a large part of the table because you must acquire many more locks.
  • Is definitely much worse than other locks if you often do GROUP BY operations on a large part of the data or if you often must scan the entire table.
  • With higher-level locks, you can also more easily support locks of different types to tune the application, because the lock overhead is less than for row-level locks.

• Table locks are superior to page-level or row-level locks in the following cases:

  • Most statements for the table are reads.
  • Read and updates on strict keys, where you update or delete a row that can be fetched with a single key read: UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value;
  • SELECT combined with concurrent INSERT statements, and very few UPDATE and DELETE statements.
  • Many scans or GROUP BY operations on the entire table without any writers.

• 行级锁的优点：

  • 访问多个线程中的不同行时，锁冲突更少。
  • 回滚的更改较少。
  • 可以长时间锁定单行。

• 行级锁的缺点：

  • 比页级或表级锁占用更多内存。
  • 当用于表的大部分时，比页级或表级锁慢，因为您必须获得更多的锁。
  • 如果您经常对大部分数据进行 GROUP BY 操作或者您经常必须扫描整个表，这肯定比其他锁差得多。
  • 使用更高级别的锁，您还可以更轻松地支持不同类型的锁来调整应用程序，因为锁开销小于行级锁。

• 在以下情况下，表锁优于页级或行级锁：

  • 该表的大多数语句都是读取。
  • 读取和更新严格键，您可以在其中更新或删除可以通过单个键读取获取的行： UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value;
  • SELECT 结合并发 INSERT 语句，很少有 UPDATE 和 DELETE 语句。
  • 在没有任何作者的情况下对整个表进行许多扫描或 GROUP BY 操作。

回答by Raj More

A rowTable level lock is better for a large table where major data modifications are taking place. This lets the system contend with a single lock on the table rather than having to deal with a gazillion locks (one for each row).

一排表级锁是其中的主要数据修改正在发生一个大表更好。这让系统可以处理表上的单个锁，而不必处理无数个锁（每行一个）。

The RDBMS automatically escalates locking levels internally.

RDBMS 在内部自动升级锁定级别。

回答by S.Lott

Table locking enables many sessions to read from a table at the same time

To achieve a very high lock speed, MySQL uses table locking

表锁定使多个会话可以同时从一个表中读取

为了实现非常高的锁定速度，MySQL 使用表锁定

"I would presume that row-level locking is better because" [you lock less data].

“我认为行级锁定更好，因为”[你锁定的数据更少]。

First "better" is poorly defined in this page. It appears that better means "faster".

第一个“更好”在此页面中定义不明确。似乎更好意味着“更快”。

Row-level locking cannot (in general) be faster because of contention for locks. Locking each row of a large result set means the very real possibility of a conflict with another large result set query and a rollback.

由于争用锁，行级锁定不能（通常）更快。锁定大结果集的每一行意味着与另一个大结果集查询和回滚发生冲突的真实可能性。

回答by SQLMenace

In general if you need to lock a lot of data then 1 lock on a big table is cheaper than a whole bunch of row level or page locks

一般来说，如果您需要锁定大量数据，那么在大表上的 1 个锁比一大堆行级或页锁便宜