8.11.2 表锁定问题_MySQL 5.6 参考手册

MySQL 8.0 参考手册 / 第8章优化 / 8.11 优化锁定操作 / 8.11.2 表锁定问题

8.11.2 表锁定问题

InnoDB表使用行级锁定，以便多个会话和应用程序可以同时读取和写入同一个表，而不会相互等待或产生不一致的结果。对于这种存储引擎，避免使用该LOCK TABLES语句，因为它没有提供任何额外的保护，反而降低了并发性。自动行级锁定使这些表适用于拥有最重要数据的最繁忙的数据库，同时还简化了应用程序逻辑，因为您不需要锁定和解锁表。因此， InnoDB存储引擎是 MySQL 中的默认引擎。

MySQL 对除 InnoDB. 锁定操作本身没有太多开销。但是因为在任何时候只有一个会话可以写入一个表，为了使用这些其他存储引擎获得最佳性能，主要将它们用于经常查询但很少插入或更新的表。

有利于 InnoDB 的性能考虑

在选择是否使用 InnoDB或不同的存储引擎创建表时，请记住表锁定的以下缺点：

表锁定使多个会话可以同时从一个表中读取，但如果一个会话想要写入一个表，它必须首先获得独占访问权限，这意味着它可能必须等待其他会话先完成对表的操作。在更新期间，所有其他想要访问该特定表的会话必须等到更新完成。
当会话正在等待时，表锁定会导致问题，因为磁盘已满并且在会话可以继续之前需要可用空间。在这种情况下，所有想要访问问题表的会话也将处于等待状态，直到有更多磁盘空间可用。
SELECT运行时间较长的语句会阻止其他会话同时更新表，从而使其他会话显得缓慢或无响应。当一个会话等待获得对表的独占访问权以进行更新时，其他发出 SELECT语句的会话在它后面排队，即使是只读会话也会降低并发性。

锁定性能问题的解决方法

以下各项描述了一些避免或减少由表锁定引起的争用的方法：

考虑将表切换到 InnoDB存储引擎，要么 CREATE TABLE ... ENGINE=INNODB在设置期间使用，要么ALTER TABLE ... ENGINE=INNODB用于现有表。有关此存储引擎的更多详细信息，请参阅第 14 章，InnoDB 存储引擎。
优化SELECT语句以更快地运行，以便它们锁定表的时间更短。您可能必须创建一些汇总表才能执行此操作。
启动mysqld--low-priority-updates。_ 对于仅使用表级锁定（例如、和）的存储引擎 MyISAM，MEMORY这 MERGE使所有更新（修改）表的语句的优先级低于 SELECT语句。SELECT 在这种情况下，前面场景中的第二条语句将在该UPDATE语句之前执行，并且不会等待第一条语句SELECT完成。
要指定在特定连接中发出的所有更新都应以低优先级完成，请将 low_priority_updates 服务器系统变量设置为 1。
要赋予特定INSERT的 UPDATE、或 DELETE语句较低的优先级，请使用该LOW_PRIORITY 属性。
要赋予特定SELECT 语句更高的优先级，请使用该 HIGH_PRIORITY属性。请参阅第 13.2.9 节，“SELECT 语句”。
以较低的系统变量值启动mysqldmax_write_lock_count ，以强制 MySQL 在对表发生特定数量的写锁定后临时提升所有SELECT 等待表的语句的优先级（例如，对于插入操作）。这允许在一定数量的写锁之后读锁。
如果您在同一个非事务表上混合插入和删除，INSERT DELAYED可能会有所帮助。请参阅第 13.2.5.3 节，“插入延迟语句”。

笔记

INSERT DELAYED已弃用；希望在未来的版本中将其删除。使用INSERT(without DELAYED) 代替。
如果您对混合 SELECT和 DELETE语句有疑问， LIMIT选项 DELETE可能会有所帮助。请参阅第 13.2.2 节，“DELETE 语句”。
使用SQL_BUFFER_RESULTwith SELECT语句有助于缩短表锁定的持续时间。请参阅第 13.2.9 节，“SELECT 语句”。
将表内容拆分为单独的表可能会有所帮助，因为它允许查询针对一个表中的列运行，而更新仅限于不同表中的列。
您可以更改锁定代码 mysys/thr_lock.c以使用单个队列。在这种情况下，写锁和读锁将具有相同的优先级，这可能有助于某些应用程序。