mysql8 翻译系列 五十二
10.11.1 内部锁定方法
本节讨论内部锁定，即MySQL服务器自身为管理多个会话对表内容的竞争而执行的锁定操作。这种锁定是内部操作，完全由服务器执行，不涉及其他程序。有关其他程序对MySQL文件执行的锁定，请参见10.11.5节 “外部锁定”。
- 行级锁定
- 表级锁定
- 选择锁定类型
行级锁定
MySQL对InnoDB表使用行级锁定，以支持多个会话同时进行写访问，这使得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 |
+-----------------------+---------+
性能模式锁表也提供锁定信息。请参见29.12.13节 “性能模式锁表”。
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节 “锁定函数”。
