MySQL优化系列16-优化MySQL的锁

备注:测试数据库版本为MySQL 8.0

一. 内部锁定方法

本节讨论内部锁定;也就是说，在MySQL服务器内部执行锁定，以管理多个会话对表内容的争用。这种类型的锁定是内部的，因为它完全由服务器执行，不涉及其他程序。

1.1 行锁

MySQL为InnoDB表使用行级锁来支持多个会话的同时写访问，使它们适合多用户、高并发和OLTP应用程序。

为了避免在一个InnoDB表上执行多个并发写操作时发生死锁，可以在事务开始时通过发出SELECT…FOR UPDATE语句用于期望修改的每一组行，即使数据更改语句稍后出现在事务中。如果事务修改或锁定多个表，则在每个事务中以相同的顺序发出适用的语句。死锁会影响性能，而不是表示一个严重的错误，因为InnoDB默认会自动检测死锁条件，并回滚一个受影响的事务。

在高并发性系统上，当多个线程等待同一个锁时，死锁检测可能会导致速度减慢。有时，禁用死锁检测可能更有效，当死锁发生时，依赖innodb_lock_wait_timeout设置进行事务回滚。可以使用innodb_deadlock_detect配置选项来禁用死锁检测。

行级锁定的优点:

1. 不同会话访问不同行时，锁冲突更少。
2. 对回滚的更改更少。
3. 可以长时间锁定单个行。

1.2 表锁

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

这些存储引擎总是在查询开始时立即请求所有需要的锁，并且总是以相同的顺序锁定表，从而避免了死锁。这种策略的利弊在于降低了并发性;其他想要修改表的会话必须等到当前数据更改语句完成。

表级锁定的优点:

1. 所需内存相对较少(行锁定要求锁定的每行或每组行需要内存)
2. 当用于表的大部分时，速度很快，因为只涉及一个锁。
3. 如果您经常对大部分数据进行GROUP BY操作，或者必须频繁扫描整个表，那么这是快速的。

MySQL授予表写锁如下:

1. 如果表上没有锁，就给它加一个写锁。
2. 否则，将锁请求放入写锁队列中。

MySQL授予表读锁如下:

1. 如果表上没有写锁，就给它加一个读锁。
2. 否则，将锁请求放入读锁队列中。

表更新比表检索具有更高的优先级。因此，当一个锁被释放时，这个锁对写锁队列中的请求是可用的，然后对读锁队列中的请求是可用的。这确保了即使在该表有大量SELECT活动时，对表的更新也不会“饿死”。但是，如果一个表有很多更新，SELECT语句会一直等待，直到没有更新。

你可以通过检查Table_locks_immediate和table_locks_waiting状态变量来分析表锁争用，这两个状态变量分别表示可以立即授予表锁的次数和需要等待的次数:

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

Performance Schema锁表还提供锁信息。

MyISAM存储引擎支持并发插入，以减少读取器和写入器对给定表的争用:如果MyISAM表的数据文件中间没有空闲块，则始终将行插入到数据文件的末尾。在这种情况下，您可以为一个没有锁的MyISAM表自由地混合并发INSERT和SELECT语句。也就是说，在其他客户机从MyISAM表中读取数据的同时，您可以将数据行插入到MyISAM表中。漏洞可能是由于从表中删除或在表中间更新了行的结果。如果存在漏洞，则禁用并发插入，但当所有漏洞都被新数据填充时，将再次自动启用。要控制这种行为，请使用concurrent_insert系统变量。

如果您通过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;

1.3 选择锁定类型

通常情况下，表级锁优于行级锁:

1. 表的大多数语句都是读取的。
2. 表的语句是读和写的混合，其中写是对单个行的更新或删除，可以通过一个键读取来获取:

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操作，而没有任何写入器。

对于更高级别的锁，可以通过支持不同类型的锁来更轻松地调优应用程序，因为锁开销小于行级别的锁。

除行级锁定外的选项:

1. 版本控制(比如MySQL中用于并发插入的版本)，其中一个写入器可以同时拥有多个读取器。这意味着数据库或表支持数据的不同视图，这取决于访问开始的时间。其他常见的术语是“时间旅行”、“写入副本”或“随需应变副本”。

2. 按需复制在许多情况下都优于行级锁定。然而，在最坏的情况下，它可能比使用普通锁使用更多的内存。

3. 您可以使用应用程序级别的锁，例如MySQL中的GET_LOCK()和RELEASE_LOCK()提供的锁，而不是使用行级别的锁。这些都是建议性锁，因此它们只适用于相互协作的应用程序。

二.表锁问题

InnoDB表使用行级锁，因此多个会话和应用程序可以同时对同一个表进行读写操作，而不会让彼此之间等待或产生不一致的结果。对于这个存储引擎，避免使用LOCK TABLES语句，因为它没有提供任何额外的保护，而是降低了并发性。自动行级锁定使这些表适合使用最重要数据的最繁忙的数据库，同时也简化了应用程序逻辑，因为您不需要锁定和解锁表。因此，InnoDB存储引擎是MySQL的默认存储引擎。

MySQL对除了InnoDB之外的所有存储引擎使用表锁(而不是页锁、行锁或列锁)。锁定操作本身没有太多开销。但是，因为任何时候只能有一个会话写入表，为了获得其他存储引擎的最佳性能，所以主要将它们用于经常查询、很少插入或更新的表。

2.1 性能考虑优先考虑InnoDB

当选择是使用InnoDB还是其他存储引擎创建表时，请记住表锁定的以下缺点:

1. 表锁定允许多个会话同时读取一个表，但如果一个会话想要写入一个表，它必须首先获得独占访问，这意味着它可能必须等待其他会话先完成对表的访问。在更新期间，希望访问这个特定表的所有其他会话必须等待更新完成。

2. 当会话正在等待时，表锁定会导致问题，因为磁盘已满，在会话继续进行之前需要有可用的空闲空间。在这种情况下，希望访问问题表的所有会话都处于等待状态，直到有更多的磁盘空间可用为止。

3. 运行时间较长的SELECT语句会阻止其他会话同时更新表，从而使其他会话显得缓慢或无响应。当一个会话在等待对表的独占访问以进行更新时，其他发出SELECT语句的会话会排在它后面，即使是只读会话也会降低并发性。

2.2 锁定性能问题的解决方法

以下几项描述了一些避免或减少表锁定引起的争用的方法:

1. 考虑将表切换到InnoDB存储引擎，或者使用CREATE table…ENGINE=INNODB在安装过程中，或者使用ALTER TABLE…ENGINE=INNODB关于这个存储引擎的更多细节。

2. 优化SELECT语句，使其运行更快，以便锁定表的时间更短。您可能需要创建一些汇总表来完成此操作。

3. 使用——low-priority-updates启动mysqld。对于仅使用表级锁的存储引擎(如MyISAM、MEMORY和MERGE)，这将使所有更新(修改)表的语句的优先级低于SELECT语句。在本例中，前面场景中的第二个SELECT语句将在UPDATE语句之前执行，不会等待第一个SELECT语句完成。

4. 要指定在特定连接中发出的所有更新都应以低优先级执行，请将low_priority_updates服务器系统变量设置为1。

5. 要降低特定INSERT、UPDATE或DELETE语句的优先级，请使用LOW_PRIORITY属性。

6. 要让特定的SELECT语句具有更高的优先级，请使用HIGH_PRIORITY属性。

7. 使用一个较低的max_write_lock_count系统变量的值启动mysqld，以强制MySQL临时提升所有等待表的SELECT语句的优先级，这些语句发生特定数量的写锁之后(例如，插入操作)。这允许在一定数量的写锁之后使用读锁。

8. 如果使用SELECT和DELETE混合语句有问题，DELETE的LIMIT选项可能会有帮助。

9. 在SELECT语句中使用SQL_BUFFER_RESULT可以帮助缩短表锁的持续时间。

10. 将表内容拆分为单独的表可能会有所帮助，因为它允许查询针对一个表中的列运行，而更新则仅限于不同表中的列。

11. 您可以更改mysys/thr_lock.c中的锁定代码，以使用单个队列。在这种情况下，写锁和读锁具有相同的优先级，这可能有助于某些应用程序。

三. 并发插入

MyISAM存储引擎支持并发插入减少读者与作者之间的竞争对于一个给定的表:如果一个MyISAM表没有漏洞数据文件(删除行中间),可以将行添加到执行INSERT语句表在同一时间结束,SELECT语句从表中读取行。如果有多个INSERT语句，它们将排队，并与SELECT语句一起依次执行。并发INSERT的结果可能不会立即可见。

可以设置concurrent_insert系统变量来修改并发插入处理。默认情况下，该变量被设置为AUTO(或1)，并发插入按刚才描述的方式处理。如果concurrent_insert设置为NEVER(或0)，则禁用并发插入。如果将变量设置为ALWAYS(或2)，则即使对于已删除行的表，也允许在表的末尾进行并发插入。请参见concurrent_insert系统变量的描述。

如果您使用的是二进制日志，并发插入将被转换为普通的insert for CREATE…选择或插入…SELECT语句。这样做是为了确保可以通过在备份操作期间应用日志重新创建表的精确副本。参见第5.4.4节“二进制日志”。此外，对于这些语句，一个读锁被放在从选中的表上，这样插入到该表的语句就会被阻塞。结果是，对该表的并发插入也必须等待。

使用LOAD DATA，如果使用满足并发插入条件的MyISAM表指定CONCURRENT(也就是说，它中间不包含空闲块)，那么在执行LOAD DATA时，其他会话可以从表中检索数据。使用CONCURRENT选项会略微影响LOAD DATA的性能，即使在同一时间没有其他会话在使用表。

如果指定HIGH_PRIORITY，它将覆盖——low-priority-updates选项(如果服务器使用该选项启动)的效果。它还会导致不使用并发插入。

对于LOCK TABLE, READ LOCAL和READ之间的区别在于，READ LOCAL允许在锁被持有时执行无冲突的INSERT语句(并发插入)。但是，如果要在持有锁的同时使用服务器外部的进程操作数据库，则不能使用此方法。

四.元数据锁

MySQL使用元数据锁定来管理对数据库对象的并发访问，并确保数据的一致性。元数据锁定不仅适用于表，也适用于模式、存储程序(过程、函数、触发器、计划事件)、表空间、通过GET_LOCK()函数获得的用户锁定。

Performance Schema元数据锁表公开了元数据锁信息，这些信息对于查看哪些会话持有锁、正在阻塞等待锁等等非常有用。

元数据锁定确实涉及一些开销，随着查询量的增加而增加。多个查询尝试访问相同对象的次数越多，元数据争用就会增加。

元数据锁定不能替代表定义缓存，它的互斥锁和锁不同于LOCK_open互斥锁。下面的讨论提供了一些关于元数据锁定如何工作的信息。

4.1 元数据锁获取

如果给定的锁有多个等待者，那么优先级最高的锁请求将首先得到满足，并出现与max_write_lock_count系统变量相关的异常。写锁请求优先级高于读锁请求。但是，如果max_write_lock_count设置为一个较低的值(比如10)，那么如果读锁请求已经被传递给10个写锁请求，那么读锁请求可能会优先于待处理的写锁请求。通常这种行为不会发生，因为max_write_lock_count在默认情况下有一个非常大的值。

语句逐个获取元数据锁，而不是同时获取，并在进程中执行死锁检测。

DML语句通常按照在语句中提到表的顺序获取锁。

DDL语句、LOCK TABLES和其他类似语句试图通过获取按名称顺序显式命名的表上的锁来减少并发DDL语句之间可能的死锁数量。对于隐式使用的表(例如具有外键关系的表也必须被锁定)，可以以不同的顺序获得锁。

例如，RENAME TABLE是一个DDL语句，它按照名称顺序获取锁:
这个RENAME TABLE语句将tbla重命名为其他名称，并将tblc重命名为tbla:

RENAME TABLE tbla TO tbld, tblc TO tbla;

该语句按顺序获取tbla、tblc和tbld上的元数据锁(因为tbld按名称顺序跟随tblc):

这个稍微不同的语句也将tbla重命名为其他东西，并将tblc重命名为tbla:

RENAME TABLE tbla TO tblb, tblc TO tbla;

在本例中，该语句按顺序在tbla、tblb和tblc上获取元数据锁(因为tblb的名称顺序在tblc之前):

这两个语句都按此顺序获取tbla和tblc上的锁，但在其余表名上的锁是在tblc之前还是之后获取有区别。

当多个事务并发执行时，元数据锁的获取顺序可能会影响操作结果，如下面的示例所示。

从具有相同结构的两个表x和x_new开始。三个客户端发出的语句涉及这些表:

客户端1:

LOCK TABLE x WRITE, x_new WRITE;

语句以名称顺序请求并获取x和x_new上的写锁。

客户端2:

INSERT INTO x VALUES(1);

语句请求并阻塞等待x上的写锁。

客户端3:

RENAME TABLE x TO x_old, x_new TO x;

客户端1:

UNLOCK TABLES;

该语句释放x和x_new上的写锁。Client 3对x的排他锁请求比Client 2的写锁请求具有更高的优先级，所以Client 3先获取x上的锁，然后是x_new和x_old上的锁，执行重命名，然后释放自己的锁。然后客户机2获取它在x上的锁，执行插入操作，然后释放它的锁。

锁定获取顺序导致在INSERT之前执行RENAME TABLE。发生插入的x是客户机2进行插入时命名为x_new的表，客户机3将其重命名为x:

mysql> SELECT * FROM x;
+------+
| i    |
+------+
|    1 |
+------+

mysql> SELECT * FROM x_old;
Empty set (0.01 sec)

现在开始使用具有相同结构的名为x和new_x的表。同样，有三个客户端发出涉及这些表的语句:

客户端1:

LOCK TABLE x WRITE, new_x WRITE;

语句以名称顺序请求并获取new_x和x上的写锁。

客户2:

INSERT INTO x VALUES(1);

语句请求并阻塞等待x上的写锁。

客户端3:

RENAME TABLE x TO old_x, new_x TO x;

语句以名称顺序请求new_x、old_x和x上的排他锁，但是阻塞等待new_x上的锁。

客户端1:

UNLOCK TABLES;

该语句释放x和new_x上的写锁。对于x，唯一未决的请求是由客户机2发出的，因此客户机2获取它的锁，执行插入操作，然后释放锁。对于new_x，唯一挂起的请求是由客户机3发出的，它被允许获取该锁(以及old_x上的锁)。重命名操作仍然阻塞x上的锁，直到Client 2插入完成并释放它的锁。然后客户机3获取x上的锁，执行重命名，并释放它的锁。

在这种情况下，锁定获取顺序导致在RENAME TABLE之前执行INSERT。插入发生的x是原来的x，现在通过rename操作重命名为old_x:

mysql> SELECT * FROM x;
Empty set (0.01 sec)

mysql> SELECT * FROM old_x;
+------+
| i    |
+------+
|    1 |
+------+

如果在并发语句中获取锁的顺序对应用程序的操作结果有影响(如前面的示例所示)，那么可以调整表名来影响获取锁的顺序。

元数据锁在必要时扩展到外键约束相关的表，以防止冲突的DML和DDL操作在相关表上并发执行。当更新父表时，在更新外键元数据时将对子表使用元数据锁。外键元数据由子表拥有。

4.2 元数据锁释放

为了确保事务的可序列化性，服务器必须不允许一个会话在另一个会话中未完成的显式或隐式启动的事务中使用的表上执行数据定义语言(DDL)语句。服务器通过获取事务中使用的表上的元数据锁并将这些锁的释放推迟到事务结束来实现这一点。表上的元数据锁可以防止对表的结构进行更改。这种锁定方法意味着，在一个会话中被一个事务使用的表在DDL语句中不能被其他会话使用，直到事务结束。

这个原则不仅适用于事务性表，也适用于非事务性表。假设一个会话开始一个使用事务表t和非事务表nt的事务，如下所示:

START TRANSACTION;
SELECT * FROM t;
SELECT * FROM nt;

服务器在t和nt上都持有元数据锁，直到事务结束。如果另一个会话试图对任意一个表执行DDL或写锁操作，它将阻塞，直到事务结束释放元数据锁。例如，如果第二个会话尝试以下任何操作，则阻塞:

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;

同样的行为适用于The LOCK TABLES…READ。也就是说，显式或隐式启动的事务更新任何表(事务或非事务)块，并被LOCK TABLES阻塞…读取这个表。

如果服务器为一个语法有效但在执行过程中失败的语句获取元数据锁，它不会提前释放这些锁。锁释放仍然延迟到事务的结束，因为失败的语句被写入二进制日志，并且锁保护日志的一致性。

在自动提交模式下，每个语句实际上是一个完整的事务，因此为该语句获取的元数据锁只保留到语句结束。

在PREPARE语句期间获得的元数据锁一旦准备好了，就会被释放，即使准备发生在多语句事务中。

从MySQL 8.0.13开始，对于处于PREPARED状态的XA事务，元数据锁会在客户端断开连接和服务器重启之间维护，直到执行XA COMMIT或XA ROLLBACK。

五. 外部锁

外部锁定是使用文件系统锁定来管理多个进程对MyISAM数据库表的争用。当不能假定MySQL服务器等单个进程是唯一需要访问表的进程时，可以使用外部锁定。以下是一些例子:

1. 如果运行多个使用相同数据库目录的服务器(不推荐)，则每个服务器必须启用外部锁定。

2. 如果使用myisamchk对MyISAM表执行表维护操作，则必须确保服务器没有运行，或者服务器启用了外部锁定，以便在必要时锁定表文件，以便与myisamchk协调以访问表。使用myisampack来打包MyISAM表也是如此。
   如果服务器运行时启用了外部锁定，则可以在任何时候使用myisamchk进行读取操作，比如检查表。在本例中，如果服务器试图更新myisamchk正在使用的表，则服务器将等待myisamchk完成后再继续。
   如果你使用myisamchk进行写操作，比如修复或优化表，或者使用myisampack打包表，你必须始终确保mysqld服务器没有使用这个表。如果你不停止mysqld，至少在运行myisamchk之前执行mysqladmin刷新表。如果服务器和myisamchk同时访问这些表，那么这些表可能会损坏。

当外部锁定生效时，每个需要访问表的进程在继续访问表之前都需要对表文件进行文件系统锁定。如果无法获得所有必需的锁，进程将被阻塞，直到获得锁(在当前持有锁的进程释放锁之后)才能访问表。

外部锁定会影响服务器性能，因为服务器有时必须等待其他进程才能访问表。

如果运行单个服务器来访问给定的数据目录(这是通常的情况)，并且在服务器运行时没有其他程序(如myisamchk)需要修改表，那么外部锁定是不必要的。如果只使用其他程序读取表，则不需要外部锁定，尽管myisamchk在读取表时，如果服务器更改了表，则可能会报告警告。

禁用外部锁定后，要使用myisamchk，必须在执行myisamchk时停止服务器，或者在运行myisamchk之前锁定并刷新表。为了避免这种情况，可以使用CHECK TABLE和REPAIR TABLE语句来检查和修复MyISAM表。

对于mysqld，外部锁定由系统变量skip_external_locking的值控制。当启用此变量时，将禁用外部锁定，反之亦然。默认情况下，外部锁定是禁用的。

外部锁定的使用可以在服务器启动时通过使用–external-locking或–skip-external-locking选项来控制。

如果你确实使用外部锁定选项来启用从许多MySQL进程更新MyISAM表，不要在启动服务器时将delay_key_write系统变量设置为ALL，或者对任何共享表使用delay_key_write =1 table选项。否则，可能会发生索引损坏。

满足此条件的最简单方法是始终使用——external-locking和——delay-key-write=OFF。(默认情况下不会这样做，因为在许多设置中，混合使用上述选项是很有用的。)

参考:

1. https://dev.mysql.com/doc/refman/8.0/en/locking-issues.html