参考官方文档：

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

MySQL使用锁管理对表内容的争用：

内部锁定在MySQL服务器内部执行，以管理多个线程对表内容的争用。这种类型的锁定是内部的，因为它完全由服务器执行，并且不涉及其他程序。
当服务器和其他程序锁定MyISAM表文件以在它们自己之间协调哪个程序可以在哪个时间访问表时发生外部锁定。

8.11.1 内部锁定方法

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

行级别锁定

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语句会等待，直到没有更新。

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

SHOW STATUS LIKE 'Table%';

MyISAM存储引擎支持 concurrent插入以减少给定表的读，写之间的争用：如果MyISAM表在数据文件中间没有空闲块，则总是在数据文件的末尾插入行。在这种情况下，您可以自由地将MyISAM表的并发INSERT和SELECT语句混合使用，而无需锁定。也就是说，您可以在其他客户端正在读取的同时将行插入到MyISAM表中。在表中间删除或更新的行可能会产生空洞。如果存在空洞，则 concurrent插入将被禁用，但当所有孔已填充新数据时将自动再次启用。要控制这种行为，请使用concurrent_insert系统变量。

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

要在concurrent 插入不可能的情况下对表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中用于并发插入的版本），可以在多个读的同一时间拥有一个写。这意味着根据访问何时开始，数据库或表支持不同数据视图。其他常见术语是“ time travel”，“ copy on write”或“ copy on demand”。
在许多情况下，copy on demand优于行级锁定。但是，在最坏的情况下，它可以使用比使用普通锁更多的内存。
您可以使用应用程序级锁，例如MySQL中的GET_LOCK（）和RELEASE_LOCK（）提供的锁，而不是使用行级锁。这些是咨询锁，因此它们只能与相互合作的应用程序一起工作。

8.11.2 表锁定发布

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

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

（OLAP，分析库环境）

InnoDB 性能考虑因素

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

表锁定使许多会话可以同时从表中读取数据，但是如果会话要写入表中，它必须首先获得独占访问权，这意味着它可能必须先等待其他会话完成表。在更新期间，想要访问此特定表的所有其他会话必须等到更新完成。
由于磁盘已满并且在会话可继续之前需要有空闲空间，此时会话会等待，表锁定会导致问题。在这种情况下，所有希望访问问题表的会话也会处于等待状态，直到有更多磁盘空间可用。
需要很长时间运行的SELECT语句会阻止其他会话同时更新表，从而使其他会话显得缓慢或无响应。虽然会话正在等待独占访问表以进行更新，但发出SELECT语句的其他会话将在其后面排队，即使对于只读会话也会降低并发性。

绕过锁性能问题

以下各项介绍了一些避免或减少表锁定引起的争用的方法：

考虑将表切换到InnoDB存储引擎，在安装过程中使用CREATE TABLE ... ENGINE=INNODB，或对现有表使用ALTER TABLE ... ENGINE=INNODB。
优化SELECT语句以加快运行速度，以便锁定表的时间更短。您可能需要创建一些汇总表来执行此操作。
用--low-priority-updates启动mysqld。对于仅使用表级锁定的存储引擎（例如MyISAM，MEMORY和MERGE），这会为所有更新（修改）表的语句赋予低于SELECT语句的优先级。在这种情况下，前面的场景中的第二个SELECT语句将在UPDATE语句之前执行，并且不会等待第一个SELECT完成。（更新的优先级会降低，排在最后）
要指定在特定连接中发出的所有更新应该以低优先级完成，请将low_priority_updates服务器系统变量设置为1。
要为特定的INSERT，UPDATE或DELETE语句赋予较低的优先级，请使用LOW_PRIORITY属性。
要为特定的SELECT语句赋予更高的优先级，请使用HIGH_PRIORITY属性。
使用max_write_lock_count系统变量的低值启动mysqld，以强制MySQL临时提升在发生特定数量的表插入后正在等待表的所有SELECT语句的优先级。这允许一定数量的WRITE锁之后的READ锁。
如果您在INSERT与SELECT结合时遇到问题，请考虑切换到支持并发SELECT和INSERT语句的MyISAM表。
如果混合SELECT和DELETE语句有问题，DELETE的LIMIT选项可能会有所帮助。
对SELECT语句使用SQL_BUFFER_RESULT可以帮助缩短表锁的持续时间。
通过允许查询针对一个表中的列运行，而将更新限制在不同表中的列中，将表内容拆分为单独的表格可能会有所帮助。（把容易更新的列单独拆分出去）
您可以将mysys/thr_lock.c中的锁定代码更改为使用单个队列。在这种情况下，写入锁和读取锁具有相同的优先级，这可能有助于某些应用程序。

8.11.3 Concurrent inserts

MyISAM存储引擎支持并发插入以减少给定表的读写器之间的争用：如果MyISAM表在数据文件中没有空洞（中间删除行），则可以执行INSERT语句以将行添加到末尾同时SELECT语句正在读取表中的行。如果存在多个INSERT语句，则它们将与SELECT语句同时排队并执行。并发INSERT的结果可能不会立即可见。

concurrent_insert系统变量可以设置为修改并发插入处理。默认情况下，该变量设置为AUTO（或1），并按前面所述处理并发插入。如果concurrent_insert设置为NEVER（或0），则禁用并发插入。如果变量设置为ALWAYS（或2），即使对于已删除行的表，也允许在表的末尾进行并发插入。另请参阅concurrent_insert系统变量的说明。

如果您正在使用二进制日志，并发插入将转换为CREATE ... SELECT或INSERT ... SELECT语句的正常插入。这样做是为了确保您可以通过在备份操作期间应用日志来重新创建表的精确副本。另外，对于这些语句，在选定表上放置一个读锁，以便阻止插入到该表中。结果是该表的并发插入必须等待。

使用LOAD DATA INFILE，如果使用满足并发插入条件的MyISAM表（即中间不包含空闲块）指定CONCURRENT，则其他会话可在LOAD DATA执行时从表中检索数据。即使没有其他会话同时使用该表，CONCURRENT选项的使用也会影响LOAD DATA的性能。

如果您指定HIGH_PRIORITY，则它会覆盖--low-priority-updates选项的效果，如果服务器是使用该选项启动的。这也会导致并发插入不被使用。

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

8.11.4 元数据的锁定

MySQL使用元数据锁定来管理对数据库对象的并发访问并确保数据一致性。元数据锁定不仅适用于表，还适用于方案，存储的程序（过程，函数，触发器和调度事件）以及表空间（MySQL 5.7.6）。

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

元数据锁定不是表定义缓存的替代，其 mutexes 和 locks 与LOCK_open mutex不同。以下讨论提供了有关元数据锁定工作原理的一些信息。

为确保事务可序列化，服务器不得允许一个会话在另一个会话的未完成时，显式或隐式在启动事务中使用的表上执行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;

同样的行为适用于 LOCK TABLES ... READ。也就是说，更新任何表（事务或非事务）的显式或隐式启动事务将被该表的LOCK TABLES ... READ阻塞和并阻塞。

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

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

即使preparation发生在在多语句事务中，在准备语句之后，PREPARE语句期间获取的元数据锁也会被释放。

8.11.5 外部锁定

外部锁定是使用文件系统锁定来管理多个进程对MyISAM数据库表的争用。外部锁定用于单个进程（如MySQL服务器）不能被认为是唯一需要访问表的进程的情况。这里有些例子：

如果运行多个使用相同数据库目录的服务器（不推荐），则每台服务器都必须启用外部锁定。
如果使用myisamchk在MyISAM表上执行表维护操作，则必须确保服务器未运行，或者服务器启用了外部锁定，以便根据需要锁定表文件以与myisamchk协调以访问表。使用myisampack打包MyISAM表也是如此。

如果服务器在启用外部锁定的情况下运行，则可以随时使用myisamchk进行读取操作，例如检查表。在这种情况下，如果服务器尝试更新myisamchk正在使用的表，服务器将在继续之前等待myisamchk完成。

如果使用myisamchk进行写入操作（例如修复或优化表），或者如果使用myisampack打包表，则必须始终确保mysqld服务器未使用表。如果您不停止mysqld，至少在运行myisamchk之前执行mysqladmin flush-tables。如果服务器和myisamchk同时访问表，您的表可能会损坏。

在外部锁定生效的情况下，每个需要访问表的进程在继续访问表之前都会获取表文件的文件系统锁。如果无法获取所有必需的锁，则会阻止该进程访问表，直到获得锁（在当前持有锁的进程释放它们之后）。

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

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

在禁用外部锁定的情况下，要使用myisamchk，必须在执行myisamchk时停止服务器，否则在运行myisamchk之前锁定并刷新表。（请参见第8.12.1节“系统因素”。）为避免此要求，请使用CHECK TABLE和REPAIR TABLE语句来检查和修复MyISAM表。

对于mysqld，外部锁定由skip_external_locking系统变量的值控制。当这个变量被启用时，外部锁定被禁用，反之亦然。外部锁定默认是禁用的。

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

如果确实使用外部锁定选项来启用来自多个MySQL进程的MyISAM表的更新，则必须确保满足以下条件：

不要将查询缓存用于使用另一个进程更新的表的查询。
不要使用--delay-key-write = ALL选项启动服务器，或者对任何共享表使用DELAY_KEY_WRITE = 1表选项。否则，可能会发生索引损坏。

满足这些条件的最简单方法是始终使用--external-locking和--delay-key-write = OFF和--query-cache-size = 0。（这不是默认完成的，因为在许多设置中，混合使用上述选项很有用。）

ITPub博客

8.11 优化器锁操作

最新文章