为什么对于大表来说表级锁定比行级锁定更好？
According to the MySQL manual:
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
来自（预编辑）链接
如果您只命中一两行，则必须获取更多锁，使用行级锁。如果您的代码命中许多行或未知行，请坚持使用表锁。
from the (pre-edit) link
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.
行锁定比表或页级锁定需要更多的内存。
必须使用行锁定获取更多的锁，这会消耗更多的资源
来自 http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/
行级锁定的优点：
行级锁定的缺点：
在以下情况下，表锁优于页级锁或行级锁：
更新 tbl_name SET column=value WHERE unique_key_col=key_value;从 tbl_name WHERE unique_key_col=key_value 删除；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/
Advantages of row-level locking:
Disadvantages of row-level locking:
Table locks are superior to page-level or row-level locks in the following cases:
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value;row表级锁更适合发生重大数据修改的大型表。这使得系统可以处理表上的单个锁，而不必处理无数的锁（每一行一个锁）。RDBMS 在内部自动升级锁定级别。
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.
“我认为行级锁定更好，因为”[您锁定的数据较少]。
第一个“更好”在此页面中定义不明确。看来更好意味着“更快”。
由于锁的争用，行级锁定（通常）不会更快。锁定大型结果集的每一行意味着与另一个大型结果集查询发生冲突并回滚的可能性非常大。
"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.
一般来说，如果您需要锁定大量数据，那么大表上的 1 个锁比一大堆行级锁或页锁便宜
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