MySQL 8.4 Release Notes
InnoDB 表使用行级锁，这样多个会话和应用程序可以同时读取和写入同一个表，而不会互相等待或产生不一致的结果。对于这种存储引擎，应避免使用 LOCK TABLES 语句，因为它并不会提供额外的保护，反而会降低并发性。自动行级锁使这些表适用于最繁忙的数据库和最重要的数据，同时也简化了应用程序逻辑，因为你不需要对表进行锁定和解锁。因此，InnoDB 存储引擎是 MySQL 的默认存储引擎。InnoDB tables use row-level locking so that
multiple sessions and applications can read from and write to
the same table simultaneously, without making each other wait or
producing inconsistent results. For this storage engine, avoid
using the LOCK TABLES statement,
because it does not offer any extra protection, but instead
reduces concurrency. The automatic row-level locking makes these
tables suitable for your busiest databases with your most
important data, while also simplifying application logic since
you do not need to lock and unlock tables. Consequently, the
InnoDB storage engine is the default in
MySQL.
MySQL 对除 InnoDB 之外的所有存储引擎使用表锁（而不是页锁、行锁或列锁）。锁定操作本身开销不大。但由于同一时间只有一个会话可以写入表，因此为了在这些其他存储引擎下获得最佳性能，应主要将它们用于查询频繁且很少插入或更新的表。MySQL uses table locking (instead of page, row, or column
locking) for all storage engines except
InnoDB. The locking operations themselves do
not have much overhead. But because only one session can write
to a table at any one time, for best performance with these
other storage engines, use them primarily for tables that are
queried often and rarely inserted into or updated.
在选择是使用 InnoDB 还是其他存储引擎创建表时，请记住表锁定的以下缺点：When choosing whether to create a table using
InnoDB or a different storage engine, keep
in mind the following disadvantages of table locking:
表锁定允许多个会话同时从同一个表中读取数据，但如果某个会话想要向表中写入数据，它必须首先获得独占访问权限，这意味着它可能需要等待其他会话先完成对该表的操作。在更新期间，所有想要访问该特定表的其他会话必须等待，直到更新完成。Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access, meaning it might have to wait for other sessions to finish with the table first. During the update, all other sessions that want to access this particular table must wait until the update is done.
当会话因磁盘已满而需要等待以释放空间才能继续操作时，表锁定会引发问题。在这种情况下，所有想要访问该问题表的会话也会被置于等待状态，直到有更多磁盘空间可用。Table locking causes problems when a session is waiting because the disk is full and free space needs to become available before the session can proceed. In this case, all sessions that want to access the problem table are also put in a waiting state until more disk space is made available.
一个需要很长时间运行的
SELECT语句会在此期间阻止其他会话更新该表，使其他会话看起来慢或无响应。当一个会话等待获取对表的独占访问以进行更新时，其他发出SELECT语句的会话会排队等待，从而即使是只读会话也会减少并发性。ASELECTstatement that takes a long time to run prevents other sessions from updating the table in the meantime, making the other sessions appear slow or unresponsive. While a session is waiting to get exclusive access to the table for updates, other sessions that issueSELECTstatements queue up behind it, reducing concurrency even for read-only sessions.
以下项目描述了一些避免或减少因表锁引起的争用的方法：The following items describe some ways to avoid or reduce contention caused by table locking:
考虑将表切换到
InnoDB存储引擎，可以在设置时使用CREATE TABLE ... ENGINE=INNODB，或者对现有表使用ALTER TABLE ... ENGINE=INNODB。有关此存储引擎的更多详细信息，请参见 第 17 章，InnoDB 存储引擎。Consider switching the table to theInnoDBstorage engine, either usingCREATE TABLE ... ENGINE=INNODBduring setup, or usingALTER TABLE ... ENGINE=INNODBfor an existing table. See Chapter 17, The InnoDB Storage Engine for more details about this storage engine.优化
SELECT语句以更快运行，从而锁表时间更短。您可能需要创建一些汇总表来实现这一点。OptimizeSELECTstatements to run faster so that they lock tables for a shorter time. You might have to create some summary tables to do this.使用 mysqld 并加上
--low-priority-updates启动。对于仅使用表级锁的存储引擎（例如MyISAM、MEMORY和MERGE），这会使所有更新（修改）表的语句优先级低于SELECT语句。在这种情况下，上述场景中的第二个SELECT语句会在UPDATE语句之前执行，而不会等待第一个SELECT语句完成。Start mysqld with--low-priority-updates. For storage engines that use only table-level locking (such asMyISAM,MEMORY, andMERGE), this gives all statements that update (modify) a table lower priority thanSELECTstatements. In this case, the secondSELECTstatement in the preceding scenario would execute before theUPDATEstatement, and would not wait for the firstSELECTto finish.要指定在特定连接中发布的所有更新应以低优先级进行，请将
low_priority_updates服务器系统变量设置为 1。To specify that all updates issued in a specific connection should be done with low priority, set thelow_priority_updatesserver system variable equal to 1.要为特定的
INSERT、UPDATE或DELETE语句设置较低优先级，请使用LOW_PRIORITY属性。To give a specificINSERT,UPDATE, orDELETEstatement lower priority, use theLOW_PRIORITYattribute.要提高特定
SELECT语句的优先级，请使用HIGH_PRIORITY属性。请参阅 第 15.2.13 节，“SELECT 语句”。To give a specificSELECTstatement higher priority, use theHIGH_PRIORITYattribute. See Section 15.2.13, “SELECT Statement”.启动 mysqld，并为
max_write_lock_count系统变量设置一个较低的值，以强制 MySQL 在表上发生特定数量的写锁（例如，插入操作）后，暂时提高所有等待该表的SELECT语句的优先级。这允许在一定数量的写锁之后进行读锁。Start mysqld with a low value for themax_write_lock_countsystem variable to force MySQL to temporarily elevate the priority of allSELECTstatements that are waiting for a table after a specific number of write locks to the table occur (for example, for insert operations). This permits read locks after a certain number of write locks.如果你在混合使用
SELECT和DELETE语句时遇到问题，DELETE的LIMIT选项可能有所帮助。参见 第 15.2.2 节，“DELETE 语句”。If you have problems with mixedSELECTandDELETEstatements, theLIMIToption toDELETEmay help. See Section 15.2.2, “DELETE Statement”.在
SELECT语句中使用SQL_BUFFER_RESULT可以帮助缩短表锁持续的时间。参见 第 15.2.13 节，“SELECT 语句”。UsingSQL_BUFFER_RESULTwithSELECTstatements can help to make the duration of table locks shorter. See Section 15.2.13, “SELECT Statement”.将表内容拆分成多个独立的表可能会有所帮助，因为这样可以让查询针对一个表中的列运行，而更新则局限于另一个表中的列。Splitting table contents into separate tables may help, by allowing queries to run against columns in one table, while updates are confined to columns in a different table.
您可以更改
mysys/thr_lock.c中的锁定代码，以使用单个队列。在这种情况下，写锁和读锁将具有相同的优先级，这可能对某些应用程序有所帮助。You could change the locking code inmysys/thr_lock.cto use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications.