MySQL :: MySQL 9.0 参考手册 :: 10.11.1 内部锁定方法

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 内部锁定方法

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 表使用表级锁定，一次只允许一个会话更新这些表。此锁定级别使这些存储引擎更适合于只读、读为主或单用户应用程序。

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

表级锁定的优点

所需的内存相对较少（行锁定需要每个锁定的行或行组的内存）
当用于表的大部分时速度很快，因为只涉及单个锁。
如果您经常对大部分数据执行 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   |
+-----------------------+---------+

Performance Schema 锁表也提供锁定信息。请参阅第 29.12.13 节“Performance Schema 锁表”。

MyISAM 存储引擎支持并发插入，以减少对给定表的读取器和写入器之间的争用：如果 MyISAM 表的数据文件中间没有空闲块，则行始终插入到数据文件的末尾。在这种情况下，您可以自由地混合对 MyISAM 表的并发 INSERT 和 SELECT 语句，而无需锁定。也就是说，您可以在其他客户端从 MyISAM 表读取数据的同时向其中插入行。如果从表的中间删除或更新行，则可能会产生空洞。如果存在空洞，则会禁用并发插入，但在所有空洞都填充了新数据后会自动重新启用。要控制此行为，请使用 concurrent_insert 系统变量。请参阅第 10.11.3 节“并发插入”。

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

要在无法进行并发插入的情况下对表 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;

选择锁定类型

一般来说，在以下情况下，表锁优于行级锁：

对表的多数语句都是读取操作。
对表的语句是读取和写入的混合，其中写入是对单行的更新或删除，可以使用一次键读取来获取。
```
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 节“锁定函数”。