MySQL 8.4 Release Notes
10.11.2 表锁定问题
InnoDB 表使用行级锁定，以便多个会话和应用程序可以同时读取和写入同一个表，而不需要等待或产生不一致的结果。对于这个存储引擎，避免使用LOCK TABLES语句，因为它不提供额外的保护，而是减少了并发性。自动的行级锁定使这些表适合于最繁忙的数据库中，同时也简化了应用程序逻辑，因为不需要锁定和解锁表。因此，InnoDB存储引擎是 MySQL 的默认存储引擎。
MySQL 使用表锁定（而不是页面、行或列锁定）对所有存储引擎进行锁定，除了InnoDB。锁定操作本身不需要太多资源。但是，因为只有一个会话可以在任何时候写入一个表，因此对于其他存储引擎，建议使用它们来存储经常查询的表，但不经常插入或更新。
在选择使用 InnoDB 或其他存储引擎时，请注意以下表锁定的缺点：
-
表锁定允许多个会话读取同一个表，但是如果一个会话想要写入一个表，它必须首先获取独占访问权限，这可能需要等待其他会话完成对表的访问。在更新期间，所有其他会话都需要等待直到更新完成。
-
表锁定会在会话等待磁盘空间变得可用时出现问题。在这种情况下，所有会话都将被阻塞直到更多的磁盘空间变得可用。
-
一个
SELECT语句如果运行时间太长将阻止其他会话更新该表，使得其他会话出现延迟或不响应。一个会话等待获取独占访问权限更新表时，其他会话将队列起来，降低了读取会话的并发性。
以下是避免或减少表锁定竞争的方法：
-
考虑将表切换到
InnoDB存储引擎，使用CREATE TABLE ... ENGINE=INNODB在设置时，或者使用ALTER TABLE ... ENGINE=INNODB对现有表。见第 17 章，《InnoDB 存储引擎》了解更多关于这个存储引擎的信息。 -
优化
SELECT语句，以便它们在锁定表的时间更短。你可能需要创建一些摘要表来实现这个目标。 -
使用mysqld启动
--low-priority-updates。对于使用表级锁定的存储引擎（例如SELECT），这将使所有更新（修改）表的语句具有低优先级。在这种情况下，第二个SELECT语句将在UPDATE语句执行之前执行，并且不会等待第一个SELECT语句完成。 -
要使特定连接中的所有更新语句具有低优先级，请将
low_priority_updates服务器系统变量设置为1。 -
要为特定的
INSERT、UPDATE或DELETE语句指定低优先级，请使用LOW_PRIORITY属性。 -
要为特定的
SELECT语句指定高优先级，请使用HIGH_PRIORITY属性。请参阅第15.2.13节，“SELECT 语句”。 -
使用mysqld启动
max_write_lock_count系统变量的低值，以强制MySQL在特定写锁定次数后临时提高所有等待表的SELECT语句的优先级。这允许读锁定在写锁定后执行。 -
如果您遇到混合
SELECT和DELETE语句的问题，LIMIT选项可能会有所帮助。请参阅第15.2.2节，“DELETE 语句”。 -
使用
SQL_BUFFER_RESULT与SELECT语句可以帮助使表锁定的持续时间更短。请参见 第15.2.13节，“SELECT 语句”。 -
将表内容分割到不同的表中可能会有所帮助，可以使查询运行在一个表中的列上，而更新操作则在另一个表中的列上。
-
您可以将
mysys/thr_lock.c中的锁定代码修改为使用单个队列。在这种情况下，写锁定和读锁定将具有相同的优先级，这可能会帮助一些应用程序。