MySQL优化必备知识-InnoDB事务锁（上）

要基于MySQL实现大规模、繁忙或高度可靠的数据库应用程序，或者调优MySQL性能，了解InnoDB锁和InnoDB事务模型非常重要。

文中使用MySQL版本5.7.13，引擎采用InnoDB。

术语

脏读（Dirty Read）

事务 A 在操作数据（包括新增、修改或删除数据）且未提交前，事务B可以读到A操作数据结果，就是脏读；

不可重复读（Unrepeatable Read）

事务 A 读取满足条件的某些数据；期间事务B修改了满足同样条件的数据且提交了事务；事务 A 因为业务需要再次同样条件读取数据，前后两次查询结果的数据值不同，就是不可重复读;

幻读（Phantom Read）

事务 A 读取满足条件的某些数据；期间事务B新增或删除满足同样条件的数据且提交了事务；事务 A 因为业务需要再次同样条件读取数据，前后两次查询结果的数据量不同，就是幻读;

ACID

ACID是原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)的首字母缩写。这些属性在数据库系统中都是可取的，并且都与事务的概念密切相关。InnoDB的事务特性遵循ACID原则。
原子性(atomicity)：事务是可以提交或回滚的原子工作单元。当一个事务对数据库进行多次更改时，要么在提交事务时所有更改都成功，要么在回滚事务时撤消所有更改。

一致性(consistency)：在每次提交或回滚之后以及事务进行期间，数据库始终保持一致状态。如果跨多个表更新相关数据，则查询会看到所有旧值或所有新值，而不是新旧值的混合。

隔离性(isolation)：交易在进行过程中相互保护（隔离）；它们不能相互干扰，也不能看到彼此未提交的数据。这种隔离是通过锁定机构实现的。有经验的用户可以调整隔离级别，在确保事务确实不会相互干扰的情况下，减少保护以提高性能和并发性。

持久性(durability)：事务的结果是持久的：一旦提交操作成功，该事务所做的更改就不会出现电源故障、系统崩溃、竞争条件或许多非数据库应用程序容易受到的其他潜在危险。持久性通常包括写入磁盘存储，具有一定量的冗余以防止在写入操作期间发生电源故障或软件崩溃。（在InnoDB中，双写缓冲区有助于提高持久性。）

事务隔离级别

事务隔离是数据库处理的基础之一。隔离是缩写词ACID中的I；隔离级别是在多个事务同时进行更改和执行查询时，对性能与结果的可靠性、一致性和可再现性之间的平衡进行微调的设置。

InnoDB提供了SQL:1992标准所描述的所有四个事务隔离级别：READ UNCOMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。InnoDB的默认隔离级别是REPEATABLE READ。

隔离级别

未提交读(READ-UNCOMMITTED)

未提交读即脏读模式。这种情况相当于无事务隔离级别，数据库引擎关于事务的任何事都不做。

该级别最大优势是不消耗系统性能，性能高。缺点是完全不支持ACID要求。

已提交读(READ-COMMITTED)

已提交读指在一个事务期间多次读取同一数据，即使在此期间其它事务修改了数据但未提交，当前事务仍然只能看到开始读取数据的样子（即快照）。
已提交读是在同一事务中，每次一致读取都会设置和读取自己的新快照。

对于锁定读取（SELECT with For UPDATE or LOCK IN SHARE MODE）、UPDATE语句和DELETE语句，InnoDB只锁定索引记录，而不锁定它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复主键检查。由于间隙锁定被禁用，可能会出现幻读问题，因为其他会话可以将新行插入间隙中。

使用READ COMMITTED还有其他效果：

对于UPDATE或DELETE语句，InnoDB只为更新或删除的行保留锁。MySQL评估WHERE条件后，将释放不匹配行的记录锁。这大大降低了死锁的概率，但它们仍然可能发生。

对于UPDATE语句，如果一行已经被锁定，InnoDB会执行“半一致”读取，将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否符合UPDATE的WHERE条件。如果行匹配（必须更新），MySQL会再次读取该行，这一次InnoDB要么锁定它，要么等待锁定。

READ COMMITTED隔离级别仅支持基于行的二进制日志记录。如果将READ COMMITTED与binlog_format=MIXED一起使用，则服务器会自动使用基于行的日志记录。

该级别解决了脏读问题，但存在不可重复读和幻读问题。

可重复读(REPEATABLE-READ)

可重复读是同一事务中的一致读取来自第一次读取建立的快照。这意味着，如果在同一事务中发出多个纯（非锁定）SELECT语句，这些SELECT语句彼此之间也是一致的。由于快照特性，可重复读事务期间，即使其它事务新增、修改、删除了满足读取条件的数据且已提交，其事务读取数据结果都是一致的。

对于锁定读取（SELECT with For UPDATE或LOCK IN SHARE MODE）、UPDATE和DELETE语句，锁定取决于该语句是使用具有唯一搜索条件的唯一索引，还是使用范围类型的搜索条件。对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不锁定之前的间隙。对于其他搜索条件，InnoDB会锁定扫描的索引范围，使用间隙锁或临键锁来阻止其他会话插入到范围覆盖的间隙中。

该级别解决了不可重复读问题，同时也解决了幻读问题。

该级别是MySQL InnoDB默认级别。

串行(SERIALIZABLE)

在这种隔离级别下，如果禁用自动提交，InnoDB隐式地将所有纯SELECT语句转换为SELECT … LOCK IN SHARE MODE，如果其他事务已经修改了所选行，则会强制阻止纯SELECT；如果启用了自动提交，SELECT就是它自己的事务，是只读的，可以串行一致（非锁定）读取执行，不需要因为其它事务而阻塞。
串行模式强制所有事务串行执行，每个事务只能访问前面事务已提交数据或本事务内修改后的数据，同时能够解决“脏读”、“不可重复读”、“幻读”的问题。

最大缺点因为是串行执行，所以性能是最低的。

方案选择

• 未提交读：适合数据一致性要求不高，且性能要求高的系统，如主要为读的系统。当然，如果这样系统，可采用性能更高的引擎，如MyISAM。
• 串行：适合数据ACID要求非常高，且性能要求不高的系统。
• 已提交读：如果系统要求一致性高但业务不复杂，性能要求高，优先选择该模式。
• 可重复读：如果系统要求一致性高且业务复杂，可采用该模式。

最佳实践：通常情况下应该优先选择可提交读模式。

隔离级别配置

查看当前级别

SHOW VARIABLES LIKE 'transaction_isolation';

设置级别

1、设置当前会话（当前会话退出无效） or 全局隔离级别语法（mysql重启无效）格式：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ
COMMITTED | REPEATABLE READ | SERIALIZABLE}

如：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

友情提示：通常设置隔离级别后，正在使用的应用需要重启才有效。

2、永久修改：

在my.cnf文件中的”[mysqld]“部分增加：

[mysqld]
...
transaction-isolation=READ-COMMITTED

InnoDB事务锁

数据库对于高并发数据冲突的解决方案都是基于事务锁来实现的。不同的事务隔离级别，使用不同的锁机制。MySQL InnoDB引擎提供了多种锁机制，通过合理使用能够有效地管理并发事务间的资源竞争，保证数据的一致性和完整性。

锁类型

共享锁(Shared Locks)和独占锁(Exclusive Locks)

共享锁（S）和独占锁（X）是标准的行级锁定类型：

• S锁允许持有该锁的事务读取一行。
• X锁允许持有该锁的事务更新或删除行。

如果事务T1在行r上持有S锁，则来自某个不同事务T2的对行r上的锁的请求被如下处理：

• T2对S锁的请求可以被立即批准。结果，T1和T2都在r上保持S锁。
• 不能立即批准T2对X锁的请求。

如果事务T1在行r上持有X锁，则不能立即批准来自某个不同事务T2的对r上任一类型锁的请求。相反，事务T2必须等待事务T1释放其对行r的锁定。

意向锁（Intention Locks）

InnoDB支持多粒度锁定，允许行锁和表锁共存。例如，一个语句，如LOCK TABLES table WRITE;对指定的表使用X锁。为了实现多粒度级别的锁定，InnoDB使用了意向锁。意向锁是表级锁，用于指示事务稍后需要表中某行使用哪种类型的锁（S或X）。意向锁有两种类型：

• 意向共享锁（IS）表示事务打算在表中的各个行上设置共享锁。
• 意向独占锁（IX）表示事务打算对表中的各个行设置独占锁。

例如，SELECT … LOCK IN SHARE MODE设置IS锁，SELECT … FOR UPDATE设置IX锁。

意向锁定协议如下：

1. 在事务可以获取表中某行的S锁之前，它必须首先获取表上的IS锁或更强的锁。
2. 在事务可以获取表中某行的X锁之前，它必须首先获取表上的IX锁。

S、X、IS、IX的兼容关系如下：

	X	IX	S	IS
X	✗	✗	✗	✗
IX	✗	√	✗	√
S	✗	✗	√	√
IS	✗	√	√	√

如果请求事务与现有锁兼容(√)，则会向其授予锁，但如果与现有锁冲突(✗)，则不会授予锁。事务等待，直到冲突的现有锁被释放。如果锁请求与现有锁冲突，并且由于会导致死锁而无法授予，则会发生错误。

意向锁不会阻塞除完整表请求之外的任何内容（例如，LOCK TABLES…WRITE）。意图锁的主要目的是显示有人正在锁定一行，或者要锁定表中的一行。

记录锁（Record Locks）

记录锁是索引记录上的锁。例如，SELECT c1 FROM t WHERE c1=10 For UPDATE；防止任何其他事务插入、更新或删除t.c1值为10的行。

记录锁总是锁定索引记录，即使定义的表没有索引也是如此。对于这种情况，InnoDB会创建一个隐藏的聚集索引，并将该索引用于记录锁定。

间隙锁（Gap Locks）

间隙锁定是对索引记录之间间隙的锁定，或对第一个索引记录之前或最后一个索引记录之后间隙的锁定。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10和20 For UPDATE；防止其他事务将值15插入到t.c1列中，无论该列中是否已经存在任何此类值，因为该范围中所有现有值之间的间隙都被锁定。

间隙可能跨越单个索引值、多个索引值，甚至为空。

对于使用唯一索引锁定行以搜索仅一行结果的语句，不需要间隙锁定。但不包括搜索条件仅包括复合唯一索引的某些列的情况；在这种情况下，间隙锁定确实会发生。

这里还值得注意的是，不同的事务可以在间隙上持有冲突的锁。例如，事务A可以在间隙上保持共享间隙锁（间隙S-锁），而事务B在相同间隙上保持独占间隙锁（缝隙X-锁）。允许冲突的间隙锁的原因是，如果从索引中清除记录，则必须合并不同事务在记录上保留的间隙锁。

InnoDB中的Gap锁是“纯抑制性的”，这意味着它们的唯一目的是防止其他事务插入到Gap中。间隙锁可以共存。一个事务占用的间隙锁不会阻止另一个事务对同一间隙占用间隙锁。共享和独占间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

间隙锁定可以明确禁用。如果将事务隔离级别更改为READ COMMITTED或启用innodb_locks_unsaf_for_binlog系统变量（现在已弃用），就会发生这种情况。在这种情况下，间隙锁定对于搜索和索引扫描是禁用的，并且仅用于外键约束检查和重复密钥检查。

使用READ COMMITTED隔离级别或启用innodb_locks_unsaf_for_binlog也会产生其他影响。MySQL评估WHERE条件后，将释放不匹配行的记录锁。对于UPDATE语句，InnoDB进行“半一致”读取，从而将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否符合UPDATE的WHERE条件。

间隙锁是性能和并发性之间权衡的一部分，用于某些事务隔离级别。

临键锁(Next-Key Locks)

临键锁是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合。

InnoDB执行行级锁定的方式是，当它搜索或扫描表索引时，它会对遇到的索引记录设置共享或独占锁。因此，行级锁实际上是索引记录锁。索引记录上的临键锁也会影响该索引记录之前的“间隙”。也就是说，临键锁是索引记录锁加上索引记录之前间隙上的间隙锁。如果一个会话对索引中的记录R具有共享或独占锁定，则另一个会话无法在索引顺序中R之前的间隙中插入新的索引记录。

假设一个索引包含值10、11、13和20。此索引可能的临键锁覆盖区间分别对应：(-∞, 10]，(10, 11]，(11, 13]，(13, 20]，(20, +∞)。临键锁覆盖区域是上一个键值到本键值之间的间隙，如果是最小索引值，间隙则是-∞到本键值的间隙；如果是最大索引值，则是本索引到+∞的间隙。

临键锁的目的是防止幻读发生。默认情况下，InnoDB在REPEATABLE READ事务隔离级别下运行。

插入意向锁（Insert Intention Locks）

插入意图锁是在行插入之前由insert操作设置的一种间隙锁。该锁定以这样一种方式发出插入意图的信号，即如果插入到同一索引间隙中的多个事务不在间隙内的同一位置插入，则它们不需要彼此等待。假设存在值为4和7的索引记录。尝试分别插入值为5和6的单独事务，在获得插入行的独占锁之前，每个事务都使用插入意图锁锁定4和7之间的间隙，但不会相互阻止，因为这些行不冲突。

自增锁(AUTO-INC Locks)

自增锁是一种特殊的表级锁，由插入到具有AUTO_INCREMENT列的表中的事务使用。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务都必须等待向该表中进行自己的插入，以便第一个事务插入的行接收连续的主键值。

innodb_autoinc_lock_mode变量控制用于自动增量锁定的算法。它允许您选择如何在可预测的自动增量值序列和插入操作的最大并发性之间进行权衡。

空间索引的谓词锁

InnoDB支持对包含空间数据的列进行空间索引。为了处理涉及空间索引的操作的锁定，Next-Key Locks不能很好地支持REPEATABLE READ或SERIALIZABLE事务隔离级别。多维数据中没有绝对的排序概念，因此不清楚哪个是“Next-Key”。

为了支持具有SPATIAL索引的表的隔离级别，InnoDB使用谓词锁。SPATIAL索引包含最小边界矩形（MBR）值，因此InnoDB通过在用于查询的MBR值上设置谓词锁来强制对索引进行一致读取。其他事务无法插入或修改与查询条件匹配的行。

全局锁（Global Locks）

在整个MySQL实例上施加锁，阻止对所有表结构的更改（如DROP TABLE等），在某些情况下用于实现全库备份。
命令:

FLUSH TABLES WITH READ LOCK;

锁机制

如何加锁

锁定(select … for update/LOCK IN SHARE MODE)读取、UPDATE或DELETE通常会对SQL语句处理过程中扫描的每个索引记录设置记录锁定。WHERE语句中是否存在将排除该行的条件并不重要。InnoDB不记忆精确的的WHERE条件，只知道扫描了哪些索引范围。这些锁通常是临键锁，会阻止在“间隙”中插入。

如果在搜索中使用了辅助索引，并且要设置的索引记录锁是独占的，那么InnoDB也会检索相应的聚集索引记录并对其设置锁。

如果没有适合where语句的索引，并且MySQL必须扫描整个表来处理该语句，则表的每一行都会被锁定，从而阻止其他用户对表的所有插入。创建好的索引非常重要，这样查询扫描的行数就不会超过需要的行数。

SQL与锁

• SELECT … FROM 是一致读取，读取数据库的快照，并且不设置锁，除非事务隔离级别设置为SERIALIZABLE。对于串化级别，搜索将在遇到的索引记录上设置共享的临键锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。
• For SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE 将为扫描的行获取锁，并为不符合结果集中包含条件的行释放锁（例如，它们不满足WHERE子句中给出的条件）。但是，在某些情况下，行可能不会立即解锁，因为查询执行过程中结果行与其原始源之间的关系丢失。例如，在联合中，表中扫描（和锁定）的行可能会插入到临时表中，然后再评估它们是否符合结果集的条件。在这种情况下，临时表中的行与原始表中的行之间的关系将丢失，并且在查询执行结束之前，后面的行不会被解锁。
  SELECT … LOCK IN SHARE MODE设置搜索遇到的所有索引记录的共享临键锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。
  For SELECT … FOR UPDATE在搜索遇到的每个记录上设置一个独占的临键锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。对于搜索遇到的索引记录，请选择SELECT … FOR UPDATE阻止其他会话执行。
  一致读取会忽略在读取视图中存在的记录上设置的所有锁。
• UPDATE … WHERE …在搜索遇到的每个记录上设置独占的临键锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。更新修改聚集索引记录时，会对受影响的次索引记录执行隐式锁。在插入新的辅助索引记录之前执行重复检查扫描时，以及在插入新的辅助索引记录时，更新操作还对受影响的辅助索引记录使用共享锁。
• DELETE FROM … WHERE …在搜索遇到的每个记录上设置独占的临键锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。
• INSERT在插入的行上设置独占锁。此锁是索引记录锁，而不是临键锁（即没有间隙锁），并且不会阻止其他会话在插入行之前插入间隙。在插入行之前，将设置一种称为插入意向锁的间隙锁类型。此锁表示插入的意图，如果插入到同一索引间隙中的多个事务没有插入到间隙中的同一位置，则它们不需要等待对方。假设存在值为4和7的索引记录。尝试插入值5和6的单独事务在获得插入行的独占锁之前，每个事务都使用插入意向锁锁定4和7之间的间隙，但不会相互阻塞，因为这些行是非冲突的。如果出现重复主键错误，则会在重复索引记录上设置共享锁。如果有多个会话试图插入同一行（如果另一个会话已经具有排他锁），则使用共享锁可能会导致死锁。
• INSERT … ON DUPLICATE KEY UPDATE 与简单INSERT的不同之处在于，发生重复主键错误时，要更新的行上放置的是独占锁而不是共享锁。对重复的主键值采用独占索引记录锁。对于重复的唯一键值，将采用独占的临键锁。
• INSERT INTO T SELECT … FROM S WHERE … 在插入到T中的每一行上设置排他索引记录锁（不带间隙锁）。如果事务隔离级别为READ COMMITED，或innodb_locks_unsafe_for_binlog已启用，且事务隔离级别非串化，innodb将在S上搜索作为一致读取（无锁）。否则，InnoDB将对来自S的行设置共享临键锁。在后一种情况下，InnoDB必须设置锁：在使用基于语句的二进制日志进行前滚恢复期间，每个SQL语句必须以与最初完全相同的方式执行。
• CREATE TABLE … SELECT … 使用共享的临键锁执行选择，或作为一致读取执行选择，同INSERT INTO T SELECT … FROM S WHERE …。
• REPLACE INTO t SELECT … FROM s WHERE … or UPDATE t … WHERE col IN (SELECT … FROM s …) InnoDB对表s中的行设置共享临键锁。
• 如果在唯一key上没有冲突，则REPLACE将像INSERT一样完成。否则，将在要替换的行上放置独占的临键锁。
• InnoDB在初始化表上先前指定的AUTO_INCREMENT列时，在与AUTO_INCREMENT列关联的索引末尾设置排他锁。
  innodb_autoinc_lock_mode=0时，innodb使用一种特殊的AUTO-INC表锁模式，在访问自动增量计数器时获取锁并将其保留到当前SQL语句的末尾（而不是整个事务的末尾）。当AUTO-INC表锁被保持时，其他客户端无法插入到表中。
  innodb_autoinc_lock_mode=1时，“批量插入”也会出现相同的行为。
  表级AUTO-INC锁不用于innodb_autoinc_lock_mode=2。
  InnoDB获取以前初始化的AUTO_INCREMENT列的值，而不设置任何锁。
• 对于FOREIGN KEY，任何insert、update或delete需要检查约束条件，并对检查约束的记录设置共享记录级锁。且在约束失败的情况下仍然设置这些锁。
• LOCK TABLES设置表锁，但设置这些锁的是InnoDB层之上的较高的MySQL层。如果InnoDB_table_locks=1（默认值）和autocommit=0，并且InnoDB上面的MySQL层知道行级锁，那么InnoDB知道表锁。否则，InnoDB的自动死锁检测无法检测涉及此类表锁的死锁。另外，因为在这种情况下，较高的MySQL层不知道行级锁，所以可以在另一个会话当前具有行级锁的表上获取表锁。这不会危及事务完整性。
  如果innodb_table_locks=1（默认值），则LOCK TABLES在每个表上获取两个锁。除了MySQL层的表锁之外，它还获取InnoDB表锁。要避免获取InnoDB表锁，请将InnoDB_table_locks设置为0。如果未获取InnoDB表锁，则即使表的某些记录被其他事务锁定，锁表也会完成。
  在MySQL 5.7中，innodb_table_locks=0对使用LOCK TABLES … WRITE显式锁定表无效。它确实对锁定的表有影响，以便通过锁定表读取或写入LOCK TABLES … WRITE（例如，通过触发器）或通过锁表LOCK TABLES … READ。
• 不能在事务中间锁定其他表，因为锁定表执行隐式提交和解锁表。
• 事务提交或中止时，事务持有的所有InnoDB锁都将被释放。因此，在autocommit=1模式下调用InnoDB表上的锁表没有多大意义，因为获取的InnoDB表锁将立即释放。

死锁及检测

死锁

死锁是一种使不同事务无法继续的情况，因为每个事务都持有另一个事务所需的锁。因为两个事务都在等待资源变为可用，所以两个事务都不会释放它所持有的锁。

当事务锁定多个表中的行（通过UPDATE或SELECT…FOR UPDATE等语句）但顺序相反时，可能会发生死锁。当这样的语句锁定索引记录和间隙的范围时，也会发生死锁，因
为时间问题，每个事务都会获得一些锁，而不会获得其他锁。

即使在只插入或删除一行的事务中，也会出现死锁。这是因为这些操作不是真正的“原子”；它们会自动对插入或删除的行的（可能是多个）索引设置记录锁。

死锁的可能性不受隔离级别的影响，因为隔离级别改变的是读取操作的行为，而死锁是由于写入操作而发生的。

死锁检测

启用死锁检测时（默认），InnoDB自动检测事务死锁并回滚一个或多个事务以打破死锁。InnoDB尝试选取要回滚的小事务，其中事务的大小由插入、更新或删除的行数决定。

如果InnoDB_table_locks=1（默认值）和autocommit=0，则InnoDB知道表锁，并且它上面的MySQL层知道行级锁。否则，InnoDB无法检测死锁，其中涉及MySQL lock TABLES语句设置的表锁或InnoDB以外的存储引擎设置的锁。通过设置innodb_lock_wait_timeout系统变量的值来解决这些情况。

如果InnoDB最新检测到的死锁部分包含一条消息“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION”，这表示等待列表上的事务数已达到200个限制。超过200个事务的等待列表被视为死锁，尝试检查等待列表的事务被回滚。如果锁定线程查看等待列表中事务拥有超过1000000个锁，则也可能发生相同的错误。

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

避免死锁最佳实践

死锁是事务性数据库中的一个典型问题，但除非死锁太频繁以至于您根本无法运行某些事务，否则死锁并不危险。通常，应用程序应在事务因死锁而回滚时，它们始终准备重新发出事务。

• 死锁时用SHOW ENGINE INNODB STATUS来确定最近死锁的原因，以便调整应用程序以避免死锁。
• 如果事务因死锁而失败，请随时准备重新发出事务。死锁并不危险。再试一次。
• 使事务保持小而短的持续时间，以使它们不易发生冲突。
• 为了减少死锁的可能性，使用事务而不是锁表语句；
• 在进行一组相关更改后立即提交事务，以降低冲突的可能性。特别是，不要让交互式mysql会话在未提交事务的情况下长时间处于打开状态。
• 如果使用锁定读取（SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如“READ COMMITTED”。
• 当修改事务中的多个表或同一表中的不同行集时，每次都以一致的顺序执行这些操作。然后事务形成定义良好的队列，不会死锁。例如，将数据库操作组织到应用程序中的函数中，或者调用存储的例程，而不是在不同的位置编码多个类似的INSERT、UPDATE和DELETE语句序列。
• 将精心选择的索引添加到表中，以便查询扫描更少的索引记录并设置更少的锁。使用EXPLAIN SELECT确定MySQL服务器认为哪些索引最适合您的查询。
• 使用较少的锁定。如果允许SELECT从旧快照返回数据，请不要向其添加FOR UPDATE或LOCK IN SHARE MODE子句。在此处使用READ COMMITED隔离级别很好，因为同一事务中的每个一致读取都从其自己的新快照读取。
• 在“选择”的列上创建索引（SELECT … FOR UPDATE and UPDATE … WHERE )。
• 如果其它方法无效，可使用表级锁序列化事务。将锁表与事务表（如InnoDB表）一起使用的正确方法是，在事务开始时，先设置autocommit=0（不是启动事务），然后设置锁表，并且在显式提交事务之前不调用unlock tables。表级锁阻止对表的并发更新，从而避免了死锁，从而降低了繁忙系统的响应能力。

查看锁信息

MySql锁有关系的表如下：

-- 当前进行的事务
SELECT * FROM information_schema.INNODB_TRX;
-- 当前锁状况
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 当前等待锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

表字段说明：

NNODB_TRX：

TRX_ID：InnoDB内部的唯一事务ID号。只读和非锁定的事务不会创建。

TRX_WEIGHT：事务的权重，反映（但不一定是的确切计数）更改的行数和事务锁定的行数。为了解决死锁，InnoDB选择权重最小的事务作为要回滚的“牺牲品”。无论更改和锁定的行数如何，更改了非事务表的事务都被认为比其他事务重。

TRX_STATE：事务执行状态。RUNNING（运行中）、LOCK WAIT（等待锁）、ROLLING BACK（回滚中）和COMMITTING（提交中）。

TRX_STARTED：事务开始时间。

TRX_REQUESTED_LOCK_ID：如果TRX_STATE为lock WAIT，则事务当前正在等待的锁的ID（对应INNODB_LOCKS.lock_ID）；否则为NULL。

TRX_WAIT_STARTED：事务开始等待锁的时间，如果TRX_STATE为lock WAIT；否则为NULL。

TRX_MYSQL_THREAD_ID：MySQL线程ID（INFORMATION_SCHEMA.PROCESSLIST的id）。

TRX_QUERY：事务正在执行的SQL语句。

TRX_OPERATION_STATE：事务中业务状态。

TRX_TABLES_IN_USE：处理此事务的当前SQL语句时使用的InnoDB表的数量。

TRX_TABLES_LOCKED：当前SQL语句具有行锁的InnoDB表的数量。

TRX_LOCK_STRUCTS：事务保留的锁数。

TRX_LOCK_MEMORY_BYTES：内存中此事务的锁结构所占用的总大小。

TRX_ROWS_LOCKED：此事务锁定的大约行数。该值可能包括删除标记的行，这些行在物理上存在，但对事务不可见。

TRX_ROWS_MODIFIED：此事务中修改和插入的行数。

TRX_concurrenty_TICKETS：事务并TICKETS数量，由系统变量innodb_concurrency_tickets设置。

TRX_ISOLATION_LEVEL：当前事务的隔离级别。

TRX_UNIQUE_CHECKS：当前事务的唯一性检查是打开还是关闭。例如，它们可能在大容量数据加载期间被关闭。

TRX_FOREIGN_KEY_CHECKS：当前事务的外键检查是打开还是关闭。例如，它们可能在大容量数据加载期间被关闭。

TRX_LAST_foregard_KEY_ERROR：最后一个外键错误的详细错误消息（如果有）；否则为NULL。

TRX_ADAPTIVE_HASH_LATCHED：自适应哈希索引是否被当前事务锁定。当对自适应哈希索引搜索系统进行分区时，单个事务不会锁定整个自适应哈希索引。自适应哈希索引分区由innodb_Adaptive_hash_index_parts控制，默认情况下设置为8。
TRX_ADAPTIVE_HASH_TIMEOUT：是立即放弃自适应哈希索引的搜索锁存，还是在MySQL的调用中保留它。当不存在自适应哈希索引争用时，该值保持为零，语句保留锁存直到它们完成。在争用期间，它向下计数到零，并且语句在每次行查找后立即释放锁存器。当对自适应哈希索引搜索系统进行分区（由innodb_adaptive_hash_index_parts控制）时，该值保持为0。

TRX_IS_READ_ONLY：值为1表示事务是只读的。

TRX_AUTOCOMMIT_NON_LOCKING：值1表示事务是一个SELECT语句，它不使用FOR UPDATE或LOCK IN SHARED MODE子句，并且在启用自动提交的情况下执行，因此事务只包含这一条语句。当该列和TRX_IS_READ_ONLY都为1时，InnoDB会优化事务，以减少与更改表数据的事务相关的开销。

INNODB_LOCKS

LOCK_ID：InnoDB内部的唯一锁ID号。

LOCK_TRX_ID：持有锁的事务ID(即INNODB_TRX.TRX_ID)。

LOCK_MODE：锁模式，值：S、X、IS、IX、GAP、AUTO_INC和UNKNOWN。

LOCK_TYPE：锁类型。值：RECORD（行级锁），TABLE（表级锁）。

LOCK_TABLE：已锁定或包含锁定记录的表的名称。

LOCK_INDEX：如果LOCK_TYPE为RECORD，则索引的名称；否则为NULL。

LOCK_SPACE：如果LOCK_TYPE为record，则锁定记录的表空间ID；否则为NULL。

LOCK_PAGE：如果LOCK_TYPE为record，则锁定记录的页码；否则为NULL。

LOCK_REC：如果LOCK_TYPE为record，则页面内锁定记录的堆号；否则为NULL。

LOCK_DATA：与锁关联的数据（如果有的话）。如果LOCK_TYPE为RECORD，则显示一个值，否则该值为NULL。对于放置在主键索引上的锁，将显示锁定记录的主键值。对于放置在唯一索引上的锁，将显示锁定记录的索引值。如果辅助索引不是唯一的，则显示辅助索引值并附加主键值。如果没有主键，则LOCK_DATA根据InnoDB聚集索引使用规则显示所选唯一索引的键值或唯一InnoDB内部行ID号。LOCK_DATA报告对上确界伪记录执行的锁的“上确界假记录”。如果包含锁定记录的页面不在缓冲池中，因为它是在持有锁定时写入磁盘的，那么InnoDB不会从磁盘获取页面。相反，LOCK_DATA报告为NULL。

INNODB_LOCK_WAITS

REQUESTING_TRX_ID：被阻止的请求事务ID。

REQUESTED_LOCK_ID：事务正在等待的锁的ID。

BLOCKING_TRX_ID：阻止事务的ID。

BLOCKING_LOCK_ID：阻止另一事务进行的事务所持有的锁的ID。

更多锁实操见全面了解MySQL InnoDB事务锁（下）

这篇文章如果对您有所帮助或者启发的话，帮忙关注或点赞，有问题请评论，必有所复。您的支持是我写作的最大动力！