MySQL :: MySQL 8.4 Reference Manual :: 10.11.1 Internal Locking Methods

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Internal Locking Methods

10.11.1 内部锁定方法

本节讨论 MySQL 服务器内部的锁定机制，即管理多个会话对表内容的竞争访问。这种类型的锁定是内部的，因为它完全由服务器执行，不涉及其他程序。关于其他程序对 MySQL 文件的锁定，请参阅第 10.11.5 节，“外部锁定”。

MySQL 使用行级锁定对 InnoDB 表进行锁定，以支持多个会话同时写入访问，适合多用户、高并发和 OLTP 应用程序。

为了避免在单个InnoDB表上进行多个并发写操作时出现死锁,在事务开始时使用SELECT ... FOR UPDATE语句获取必要的锁定，每个预期修改的行组。即使数据更改语句在事务中晚些出现，也要这样做。如果事务修改或锁定多个表，需要在每个事务中按照相同顺序执行相应的语句。死锁对性能产生影响，而不是严重错误，因为InnoDB默认情况下自动检测死锁条件并回滚受影响的事务。

在高并发系统中，死锁检测可能会导致大量线程等待同一个锁定时的性能下降。在某些情况下，可能更有效地禁用死锁检测，并依靠innodb_lock_wait_timeout设置来在死锁出现时回滚事务。可以使用innodb_deadlock_detect配置选项禁用死锁检测。

行级锁定的优点：

不同会话访问不同的行时，锁定冲突较少。
回滚时需要更少的变化。
可以长时间锁定单个行。

MySQL 使用表级锁定来处理MyISAM、MEMORY和MERGE表，允许只有一个会话更新这些表。这种锁定级别使得这三个存储引擎更适合只读、读多或单用户应用程序。

这些存储引擎避免死锁，始终在查询开始时请求所有需要的锁，并且总是以相同的顺序锁定表。这种策略的tradeoff是减少了并发性；其他会话想要修改表必须等待当前数据更改语句完成。

表级锁定的优点：

需要相对较少的内存（行锁定需要为每个锁定的行或行组分配内存）
在处理大部分表时速度很快，因为只涉及到一个锁。
如果您经常执行GROUP BY操作或需要频繁地扫描整个表，可以使用表级锁定。

MySQL 按照以下方式授予表写锁：

如果没有对该表的锁，授予写锁。
否则，将锁请求队列中添加写锁请求。

MySQL 按照以下方式授予表读锁：

如果没有对该表的写锁，授予读锁。
否则，将锁请求队列中添加读锁请求。

表更新优先级高于表检索。因此，当锁释放时，锁将被分配给写锁队列中的请求，然后是读锁队列中的请求。这确保了，即使对某个表存在大量SELECT活动，表的更新仍然不会被“饥饿”。然而，如果对某个表进行了许多更新,SELECT语句将等待直到没有更多的更新。

关于调整读写优先级的信息，请见第10.11.2节，“表锁定问题”。

您可以通过检查Table_locks_immediate和Table_locks_waited状态变量来分析系统中的表锁定争用情况，这两个变量分别表示了可以立即授予表锁的请求次数和需要等待的请求次数：

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

性能chema锁定表也提供了锁定信息。请见第29.12.13节，“性能schema锁定表”。

MyISAM 存储引擎支持并发插入，以减少读者和写者的争用：如果 MyISAM 表中没有中间数据文件的空块，行总是被插入到数据文件的末尾。在这种情况下，您可以自由混合并发INSERT 和 SELECT 语句，而不需要锁定。这意味着，您可以在 MyISAM 表上同时插入行，同时其他客户端正在读取它。空洞可能来自于表中行的删除或更新。如果存在空洞，会禁用并发插入，但是一旦所有空洞被填充新的数据时，它们又将被启用。要控制这个行为，请使用concurrent_insert 系统变量。见第10.11.3节，“并发插入”。

如果您使用LOCK TABLES 显式获取表锁定，可以请求一个 READ LOCAL 锁，以便在您锁定表时其他会话可以执行并发插入。

为了在表t1上执行许多INSERT和SELECT操作，而不可能在并发插入的情况下，您可以将行插入到临时表temp_t1中，然后使用临时表中的行更新实际表：

mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
mysql> INSERT INTO t1 SELECT * FROM temp_t1;
mysql> DELETE FROM temp_t1;
mysql> UNLOCK TABLES;

通常，表锁定优于行级锁定在以下情况下：

对该表的大多数语句都是读取操作。
对该表的语句是读取和写入的混合，其中写入操作是更新或删除单个行，可以使用一个键读取-fetching：
```
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操作，而没有任何写入操作。

使用高级锁定，您可以更容易地调整应用程序，因为锁定开销小于行级锁定。

除了行级锁定外的其他选项：

版本控制（如 MySQL 中的并发插入）在可能同时存在一个写者和多个读者的情况下进行。这样意味着数据库或表支持根据访问开始时间不同的数据视图。其他常见的术语有“时间旅行”“写时复制”或“按需复制”
按需复制在许多情况下优于行级锁定。然而，在最坏的情况下，它可能使用更多内存，而不是使用正常锁定。
而不是使用行级锁定，您可以使用应用程序级别的锁定，例如 MySQL 中提供的GET_LOCK()和RELEASE_LOCK()。这些锁定是建议性的，因此它们只在合作的应用程序之间生效。请参阅第14.14节，“Locking Functions”。

PREV HOME UP NEXT