mysql 判断字段重复出现两次以上 mysql判断时间区间是否重复

InnoDB锁

官方文档: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-transaction-model.html

锁概念

InnoDB 实现了以下两种类型的行级锁：

• 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
• 排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁(Intention Locks)，这两种意向锁都是表锁：

• 意向共享锁(IS)：事务打算给数据行加行共享锁，事务再给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁(IX)：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

在 select，update 和 delete 的时候，where 条件如果不存在索引字段，那么这个事务会导致表锁(当“值重复率”低时，甚至接近主键或者唯一索引的效果，“普通索引”依然是行锁；当“值重复率”高时，MySQL 不会把这个“普通索引”当做索引，即造成了一个没有索引的 SQL，此时引发表锁。)

　　索引不是越多越好,索引每个表应控制在5个以内，索引存在一个和这个表相关的文件里，占用硬盘空间，宁缺勿滥，每个表都有主键(id)，操作能使用主键尽量使用主键。

八大锁

行锁

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

间隙锁

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。

这句话表明间隙锁一定是开区间，比如(3，5)或者。在MySQL官网上还有一段非常关键的描述：

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

这段话表明间隙锁在本质上是不区分共享间隙锁或互斥间隙锁的，而且间隙锁是不互斥的，即两个事务可以同时持有包含共同间隙的间隙锁。这里的共同间隙包括两种场景：其一是两个间隙锁的间隙区间完全一样；其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。也就是说间隙锁的应用场景包括并发读取、并发更新、并发删除和并发插入。

在MySQL官网上关于间隙锁还有一段重要描述：

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

这段话表明，在RU和RC两种隔离级别下，即使你使用select ... in share mode或select ... for update，也无法防止幻读(读后写的场景)。因为这两种隔离级别下只会有行锁，而不会有间隙锁。这也是为什么示例中要规定隔离级别为RR的原因。

临键锁

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

这句话表明临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如(3，5]。

在MySQL的官方文档中还有以下重要描述：

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows.

个人觉得这段话描述得不够好，很容易引起误解。这里更正如下：InnoDB的默认事务隔离级别是RR，在这种级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

共享锁/排他锁

InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks。

• A shared (S) lock permits the transaction that holds the lock to read a row.
• An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

这段话明确说名了共享锁/排他锁都只是行锁，与间隙锁无关，这一点很重要，后面还会强调这一点。其中共享锁是一个事务并发读取某一行记录所需要持有的锁，比如select ... in share mode；排他锁是一个事务并发更新或删除某一行记录所需要持有的锁，比如select ... for update。

不过这里需要重点说明的是，尽管共享锁/排他锁是行锁，与间隙锁无关，但一个事务在请求共享锁/排他锁时，获取到的结果却可能是行锁，也可能是间隙锁，也可能是临键锁，这取决于数据库的隔离级别以及查询的数据是否存在。关于这一点，后面分析场景一和场景二的时候还会提到。

意向共享锁/意向排他锁

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table。

The intention locking protocol is as follows:

• Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
• Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

这段话说明意向共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：

XIXSISX互斥互斥互斥互斥IX互斥兼容互斥兼容S互斥互斥兼容兼容IS互斥兼容兼容兼容

这里需要重点关注的是IX锁和IX锁是相互兼容的，这是导致上面场景一发生死锁的前置条件，后面会对死锁原因进行详细分析。

插入意向锁

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

这段话表明尽管插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。与间隙锁的另一个非常重要的差别是：尽管插入意向锁也属于间隙锁，但两个事务却不能在同一时间内一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁(当然，插入意向锁如果不在间隙锁区间内则是可以的)。这里我们再回顾一下共享锁和排他锁：共享锁用于读取操作，而排他锁是用于更新或删除操作。也就是说插入意向锁、共享锁和排他锁涵盖了常用的增删改查四个动作。

自增锁

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

这段话表明自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。为了便于介绍innodb_autoinc_lock_mode参数，我们先将需要用到自增锁的Insert语句进行分类：

1)Insert语句分类

1. “INSERT-like” statements(类INSERT语句) (这种语句实际上包含了下面的2、3、4)

所有可以向表中增加行的语句，包括INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA。包括“simple-inserts”, “bulk-inserts”, and “mixed-mode” inserts.

2. “Simple inserts”

可以预先确定要插入的行数(当语句被初始处理时)的语句。 这包括没有嵌套子查询的单行和多行INSERT和REPLACE语句，但不包括INSERT ... ON DUPLICATE KEY UPDATE。

3. “Bulk inserts”

事先不知道要插入的行数(和所需自动递增值的数量)的语句。 这包括INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句，但不包括纯INSERT。 InnoDB在处理每行时一次为AUTO_INCREMENT列分配一个新值。

4. “Mixed-mode inserts”

这些是“Simple inserts”语句但是指定一些(但不是全部)新行的自动递增值。 示例如下，其中c1是表t1的AUTO_INCREMENT列：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一种类型的“Mixed-mode inserts”是INSERT ... ON DUPLICATE KEY UPDATE，其在最坏的情况下实际上是INSERT语句随后又跟了一个UPDATE，其中AUTO_INCREMENT列的分配值不一定会在 UPDATE 阶段使用。

2)InnoDB AUTO_INCREMENT锁定模式分类

1. innodb_autoinc_lock_mode = 0 (“traditional” lock mode)这种锁定模式提供了在MySQL 5.1中引入innodb_autoinc_lock_mode配置参数之前存在的相同行为。传统的锁定模式选项用于向后兼容性，性能测试以及解决“Mixed-mode inserts”的问题，因为语义上可能存在差异。在此锁定模式下，所有“INSERT-like”语句获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_INCREMENT列的表。此锁定通常保持到语句结束(不是事务结束)，以确保为给定的INSERT语句序列以可预测和可重复的顺序分配自动递增值，并确保自动递增由任何给定语句分配的值是连续的。在**基于语句复制(statement-based replication)**的情况下，这意味着当在从服务器上复制SQL语句时，自动增量列使用与主服务器上相同的值。多个INSERT语句的执行结果是确定性的，SLAVE再现与MASTER相同的数据(反之，如果由多个INSERT语句生成的自动递增值交错，则两个并发INSERT语句的结果将是不确定的，并且不能使用基于语句的复制可靠地传播到从属服务器)。
2. innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)这是默认的锁定模式。在这个模式下,“bulk inserts”仍然使用AUTO-INC表级锁,并保持到语句结束.这适用于所有INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。而“Simple inserts”(要插入的行数事先已知)通过在mutex(轻量锁)的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁， 它只在分配过程的持续时间内保持，而不是直到语句完成。 不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。 如果另一个事务保持AUTO-INC锁，则“简单插入”等待AUTO-INC锁，如同它是一个“批量插入”。此锁定模式确保,当行数不预先知道的INSERT存在时(并且自动递增值在语句过程执行中分配)由任何“类INSERT”语句分配的所有自动递增值是连续的，并且对于基于语句的复制(statement-based replication)操作是安全的。这种锁定模式显著地提高了可扩展性,并且保证了对于基于语句的复制(statement-based replication)的安全性。此外，与“传统”锁定模式一样，由任何给定语句分配的自动递增数字是连续的。 与使用自动递增的任何语句的“传统”模式相比，语义没有变化，但有个特殊场景需要注意：The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “simple insert”. For such inserts, InnoDB allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.也就说对于混合模式的插入，可能会有部分多余自增值丢失。在连续锁定模式下，InnoDB可以避免为“Simple inserts”语句使用表级AUTO-INC锁，其中行数是预先已知的，并且仍然保留基于语句的复制的确定性执行和安全性。
3. innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)在这种锁定模式下,所有类INSERT(“INSERT-like” )语句都不会使用表级AUTO-INC lock,并且可以同时执行多个语句。这是最快和最可扩展的锁定模式，但是当使用基于语句的复制或恢复方案时，从二进制日志重播SQL语句时，这是不安全的。在此锁定模式下，自动递增值保证在所有并发执行的“类INSERT”语句中是唯一且单调递增的。但是，由于多个语句可以同时生成数字(即，跨语句交叉编号)，为任何给定语句插入的行生成的值可能不是连续的。如果执行的语句是“simple inserts”，其中要插入的行数已提前知道，则除了“混合模式插入”之外，为单个语句生成的数字不会有间隙。然而，当执行“批量插入”时，在由任何给定语句分配的自动递增值中可能存在间隙。如果不使用二进制日志作为恢复或复制的一部分来重放SQL语句，则可以使用interleaved lock模式来消除所有使用表级AUTO-INC锁，以实现更大的并发性和性能,其代价是由于并发的语句交错执行,同一语句生成的AUTO-INCREMENT值可能会产生GAP。
4. innodb_autoinc_lock_mode参数的修改编辑/etc/my.cnf，加入如下行:innodb_autoinc_lock_mode=2直接通过命令修改会报错:mysql(mdba@localhost:(none) 09:32:19)>set global innodb_autoinc_lock_mode=2; ERROR 1238 (HY000): Variable 'innodb_autoinc_lock_mode' is a read only variable

3)InnoDB AUTO_INCREMENT锁定模式含义

1. 在复制环节中使用自增列

如果你在使用基于语句的复制(statement-based replication)请将innodb_autoinc_lock_mode设置为0或1，并在主从上使用相同的值。 如果使用innodb_autoinc_lock_mode = 2(“interleaved”)或主从不使用相同的锁定模式的配置，自动递增值不能保证在从机上与主机上相同。

如果使用基于行的或混合模式的复制，则所有自动增量锁定模式都是安全的，因为基于行的复制对SQL语句的执行顺序不敏感(混合模式会在遇到不安全的语句是使用基于行的复制模式)。

2. “Lost” auto-increment values and sequence gaps

在所有锁定模式(0,1和2)中，如果生成自动递增值的事务回滚，那些自动递增值将“丢失”。 一旦为自动增量列生成了值，无论是否完成“类似INSERT”语句以及包含事务是否回滚，都不能回滚。 这种丢失的值不被重用。 因此，存储在表的AUTO_INCREMENT列中的值可能存在间隙。

3. Specifying NULL or 0 for the AUTO_INCREMENT column

在所有锁定模式(0,1和2)中，如果用户在INSERT中为AUTO_INCREMENT列指定NULL或0，InnoDB会将该行视为未指定值，并为其生成新值。

4. 为AUTO_INCREMENT列分配一个负值

在所有锁定模式(0,1和2)中，如果您为AUTO_INCREMENT列分配了一个负值，则InnoDB会将该行为视为未指定值，并为其生成新值。

5. 如果AUTO_INCREMENT值大于指定整数类型的最大整数

在所有锁定模式(0,1和2)中，如果值大于可以存储在指定整数类型中的最大整数，则InnoDB会将该值设置为指定类型所允许的最大值。

6. Gaps in auto-increment values for “bulk inserts”

当innodb_autoinc_lock_mode设置为0(“traditional”)或1(“consecutive”)时,任何给定语句生成的自动递增值是连续的，没有间隙，因为表级AUTO-INC锁会持续到 语句结束,并且一次只能执行一个这样的语句。

当innodb_autoinc_lock_mode设置为2(“interleaved”)时，在“bulk inserts”生成的自动递增值中可能存在间隙，但只有在并发执行“INSERT-Like”语句时才会产生这种情况。

对于锁定模式1或2，在连续语句之间可能出现间隙，因为对于批量插入，每个语句所需的自动递增值的确切数目可能不为人所知，并且可能进行过度估计。

7. 由“mixed-mode inserts”分配的自动递增值

考虑一下场景,在“mixed-mode insert”中,其中一个“simple insert”语句指定了一些(但不是全部)行的AUTO-INCREMENT值。 这样的语句在锁模式0,1和2中表现不同。innodb_autoinc_lock_mode=0时,auto-increment值一次只分配一个,而不是在开始时全部分配。当innodb_autoinc_lock_mode=1时，不同于innodb_autoinc_lock_mode=0时的情况，因为auto-increment值在语句一开始就分配了，但实际可能使用不完。当innodb_autoinc_lock_mode=2时，取决于并发语句的执行顺序。

8. 在INSERT语句序列的中间修改AUTO_INCREMENT列值

在所有锁定模式(0,1和2)中，在INSERT语句序列中间修改AUTO_INCREMENT列值可能会导致duplicate key错误。

4)InnoDB AUTO_INCREMENT计数器初始化

如果你为一个Innodb表创建了一个AUTO_INCREMENT列，则InnoDB数据字典中的表句柄包含一个称为自动递增计数器的特殊计数器，用于为列分配新值。 此计数器仅存在于内存中，而不存储在磁盘上。

要在服务器重新启动后初始化自动递增计数器，InnoDB将在首次插入行到包含AUTO_INCREMENT列的表时执行以下语句的等效语句。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB增加语句检索的值，并将其分配给表和表的自动递增计数器。 默认情况下，值增加1。此默认值可以由auto_increment_increment配置设置覆盖。

如果表为空，InnoDB使用值1。此默认值可以由auto_increment_offset配置设置覆盖。

如果在自动递增计数器初始化前使用SHOW TABLE STATUS语句查看表, InnoDB将初始化计数器值,但不会递增该值。这个值会储存起来以备之后的插入语句使用。这个初始化过程使用了一个普通的排它锁来读取表中自增列的最大值。InnoDB遵循相同的过程来初始化新创建的表的自动递增计数器。

在自动递增计数器初始化之后，如果您未明确指定AUTO_INCREMENT列的值，InnoDB会递增计数器并将新值分配给该列。如果插入显式指定列值的行，并且该值大于当前计数器值，则将计数器设置为指定的列值。

只要服务器运行，InnoDB就使用内存中自动递增计数器。当服务器停止并重新启动时，InnoDB会重新初始化每个表的计数器，以便对表进行第一次INSERT，如前所述。

服务器重新启动还会取消CREATE TABLE和ALTER TABLE语句中的AUTO_INCREMENT = N表选项的效果(可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值，也可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值)。

锁的发生时机

官方文档： https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

• 意向锁是由 InnoDB 自动添加的， 不需用户干预；
• SELECT ... 语句正常情况下为快照读，不加锁；
• SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁(事务隔离级别为Serializable时，默认采用方式)；
• SELECT ... FOR UPDATE 语句为当前读，加 X 锁；
• 常见的 DML 语句(如 INSERT、DELETE、UPDATE)为当前读，加 X 锁；
• 常见的 DDL 语句(如 ALTER、CREATE 等)加表级锁，且这些语句为隐式提交，不能回滚。

其中，当前读的 SQL 语句的 where 从句的不同也会影响加锁，包括是否使用索引，索引是否是唯一索引等等。

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外)

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。　　

当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁

为什么将 插入/更新/删除 操作，都归为当前读？可以看看下面这个 更新 操作，在数据库中的执行流程：

从图中，可以看到，一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

注：根据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

2PL：Two-Phase Locking

　　传统RDBMS加锁的一个原则，就是2PL (二阶段锁)：Two-Phase Locking。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。

　从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。

事务隔离级别Isolation Level

　　隔离级别：Isolation Level，也是RDBMS的一个关键特性。相信对数据库有所了解的朋友，对于4种隔离级别：Read Uncommited，Read Committed，Repeatable Read，Serializable，都有了深入的认识。本文不打算讨论数据库理论中，是如何定义这4种隔离级别的含义的，而是跟大家介绍一下MySQL/InnoDB是如何定义这4种隔离级别的。

　　MySQL/InnoDB定义的4种隔离级别：

• Read Uncommited可以读取未提交记录。此隔离级别，不会使用，忽略。
• Read Committed (RC)快照读忽略，本文不考虑。针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
• Repeatable Read (RR)快照读忽略，本文不考虑。针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
• Serializable从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

加锁过程分析

INSERT 语句加锁分析

Insert 语句在两种情况下会加锁：

• 为了防止幻读，如果记录之间加有间隙锁，此时不能 Insert；
• 如果 Insert 的记录和已有记录造成唯一键冲突，此时不能 Insert；

除了上述情况，Insert 语句的锁都是隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁的机制来减少加锁的数量。

隐式锁的特点是只有在可能发生冲突时才加锁，减少了锁的数量。另外，隐式锁是针对被修改的 B+Tree 记录，因此都是记录类型的锁，不可能是间隙锁或 Next-Key 类型。

具体 Insert 语句的加锁流程如下：

• 首先对插入的间隙加插入意向锁(Insert Intension Locks)如果该间隙已被加上了间隙锁或 Next-Key 锁，则加锁失败进入等待；如果没有，则加锁成功，表示可以插入；
• 然后判断插入记录是否有唯一键，如果有，则进行唯一性约束检查如果不存在相同键值，则完成插入如果存在相同键值，则判断该键值是否加锁如果没有锁， 判断该记录是否被标记为删除如果标记为删除，说明事务已经提交，还没来得及 purge，这时加 S 锁等待；如果没有标记删除，则报 duplicate key 错误；如果有锁，说明该记录正在处理(新增、删除或更新)，且事务还未提交，加 S 锁等待；
• 插入记录并对记录加 X 记录锁；

DELETE 语句加锁分析

一般来说，DELETE 的加锁和 SELECT FOR UPDATE 或 UPDATE 并没有太大的差异。

因为，在 MySQL 数据库中，执行 DELETE 语句其实并没有直接删除记录，而是在记录上打上一个删除标记，然后通过后台的一个叫做 purge 的线程来清理。从这一点来看，DELETE 和 UPDATE 确实是非常相像。事实上，DELETE 和 UPDATE 的加锁也几乎是一样的。

组合一：id主键+RC

这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL：delete from t1 where id = 10; 只需要将主键上，id = 10的记录加上X锁即可。如下图所示：

　结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

组合二：id唯一索引+RC

这个组合，id不是主键，而是一个Unique的二级索引键值。那么在RC隔离级别下，delete from t1 where id = 10; 需要加什么锁呢？见下图：

　此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name='d',id=10]的记录。

组合三：id非唯一索引+RC

　相对于组合一、二，组合三又发生了变化，隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete from t1 where id = 10; 语句，仍旧选择id列上的索引进行过滤where条件，那么此时会持有哪些锁？同样见下图：

根据此图，可以看到，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

组合四：id无索引+RC

　　相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，where id = 10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加X锁；有人说会将聚簇索引上，选择出来的id = 10;的记录加上X锁。那么实际情况呢？请看下图：

由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。

　　有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

组合五：id主键+RR

　　上面的四个组合，都是在Read Committed隔离级别下的加锁行为，接下来的四个组合，是在Repeatable Read隔离级别下的加锁行为。

　　组合五，id列是主键列，Repeatable Read隔离级别，针对delete from t1 where id = 10; 这条SQL，加锁与组合一：[id主键，Read Committed]一致。

组合六：id唯一索引+RR

　　与组合五类似，组合六的加锁，与组合二：[id唯一索引，Read Committed]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。

组合七：id非唯一索引+RR

　　还记得前面提到的MySQL的四种隔离级别的区别吗？RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢？问题的答案，就在组合七中揭晓。

　　组合七，Repeatable Read隔离级别，id上有一个非唯一索引，执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？同样看下面这幅图：

此图，相对于组合三：[id列上非唯一锁，Read Committed]看似相同，其实却有很大的区别。最大的区别在于，这幅图中多了一个GAP锁，而且GAP锁看起来也不是加在记录上的，倒像是加载两条记录之间的位置，GAP锁有何用？

　　其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。

　　如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP锁应运而生。

　　如图中所示，有哪些位置可以插入新的满足条件的项 (id = 10)，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。记录[6,c]之前，不会插入id=10的记录；[6,c]与[10,b]间可以插入[10, aa]；[10,b]与[10,d]间，可以插入新的[10,bb],[10,c]等；[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等；而[11,f]之后也不会插入满足条件的记录。因此，为了保证[6,c]与[10,b]间，[10,b]与[10,d]间，[10,d]与[11,f]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。

　　Insert操作，如insert [10,aa]，首先会定位到[6,c]与[10,b]间，然后在插入前，会检查这个GAP是否已经被锁上，如果被锁上，则Insert不能插入记录。因此，通过第一遍的当前读，不仅将满足条件的记录锁上 (X锁)，与组合三类似。同时还是增加3把GAP锁，将可能插入满足条件记录的3个GAP给锁上，保证后续的Insert不能插入新的id=10的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

　　有心的朋友看到这儿，可以会问：既然防止幻读，需要靠GAP锁的保护，为什么组合五、组合六，也是RR隔离级别，却不需要加GAP锁呢？

　　首先，这是一个好问题。其次，回答这个问题，也很简单。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合五，id是主键；组合六，id是unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就避免了GAP锁的使用。其实，针对此问题，还有一个更深入的问题：如果组合五、组合六下，针对SQL：select * from t1 where id = 10 for update; 第一次查询，没有找到满足查询条件的记录，那么GAP锁是否还能够省略？此问题留给大家思考。

结论：Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

组合八：id无索引+RR

　　组合八，Repeatable Read隔离级别下的最后一种情况，id列上没有索引。此时SQL：delete from t1 where id = 10; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，如下图所示：

如图，这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。这个示例表，只有6条记录，一共需要6个记录锁，7个GAP锁。试想，如果表上有1000万条记录呢？

　　在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。

　　当然，跟组合四：[id无索引, Read Committed]类似，这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。semi-consistent read如何触发：要么是read committed隔离级别；要么是Repeatable Read隔离级别，同时设置了innodb_locks_unsafe_for_binlog 参数。更详细的关于semi-consistent read的介绍，可参考我之前的一篇博客：MySQL+InnoDB semi-consitent read原理及实现分析 。

结论：在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

组合九：Serializable

　　针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL2：delete from t1 where id = 10; 来说，Serializable隔离级别与Repeatable Read隔离级别完全一致，因此不做介绍。

　　Serializable隔离级别，影响的是SQL1：select * from t1 where id = 10; 这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

一条复杂的sql语句

　写到这里，其实MySQL的加锁实现也已经介绍的八八九九。只要将本文上面的分析思路，大部分的SQL，都能分析出其会加哪些锁。而这里，再来看一个稍微复杂点的SQL，用于说明MySQL加锁的另外一个逻辑。SQL用例如下：

如图中的SQL，会加什么锁？假定在Repeatable Read隔离级别下 (Read Committed隔离级别下的加锁情况，留给读者分析。)，同时，假设SQL走的是idx_t1_pu索引。

　　　　在详细分析这条SQL的加锁情况前，还需要有一个知识储备，那就是一个SQL中的where条件如何拆分？

• Index key：pubtime > 1 and puptime < 20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围。
• Index Filter：userid = ‘hdc’ 。此条件，可以在idx_t1_pu索引上进行过滤，但不属于Index Key。
• Table Filter：comment is not NULL。此条件，在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。在分析出SQL where条件的构成之后，再来看看这条SQL的加锁情况 (RR隔离级别)，如下图所示：从图中可以看出，在Repeatable Read隔离级别下，由Index Key所确定的范围，被加上了GAP锁；Index Filter锁给定的条件 (userid = ‘hdc’)何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server层过滤，在5.6后支持了Index Condition Pushdown，则在index上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁 (图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定)；而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。结论：在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；Table Filter过滤条件，无论是否满足，都需要加X锁。

死锁原理与分析

来看看两个死锁的例子 (一个是两个Session的两条SQL产生死锁；另一个是两个Session的一条SQL，产生死锁)：

上面的两个死锁用例。第一个非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。

　　第二个用例，虽然每个Session都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。　　

结论：死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。

死锁情况说明

死锁的第一种情况

一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法：

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进 行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

死锁的第二种情况

用户A查询一条记录，然后修改该条记录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项 目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操 作，很容易就出现这种死锁的情况。

解决方法：

1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。 2、使用乐观锁进行控制。乐观锁大多是基于数据版本(Version)记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是 通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数 据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据 库加锁开销(用户A和用户B操作过程中，都没有对数据库数据加锁)，大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造 成脏数据被更新到数据库中。 3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统， 当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时(如更改用户账户余额)，如果采用悲观锁机制，也就意味着整个操作过程中(从操作员读 出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间)，数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

死锁的第三种情况

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情 况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方法：

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

死锁排查

开启死锁日志

死锁的情况打印相关信息

set global innodb_print_all_deadlocks=on;

查看当前的错误日志配置,缺省情况下位于数据目录

show variables like 'log_error';

死锁日志分析

2020-07-30 20:15:12 12329 [ERROR] /usr/sbin/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting transaction2020-08-02 11:33:46 7f8bf24ab700InnoDB: transactions deadlock detected, dumping detailed information.2020-08-02 11:33:46 7f8bf24ab700*** (1) TRANSACTION:TRANSACTION 1788888424, ACTIVE 2 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 489 lock struct(s), heap size 79400, 688 row lock(s), undo log entries 241MySQL thread id 147747, OS thread handle 0x7f8c11066700, query id 4136771289 10.8.251.167 app_ecrm updatingupdate kd_customer_today_pay set  last_pay_time=(case when last_pay_time>'2020-08-01 19:08:09'                      then last_pay_time else '2020-08-01 19:08:09' end)  where sys_customer_id=617885769 and shop_id=10016852*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 732 page no 229 n bits 624 index `AK_sys_customer_id_shop_id` of table `ecrm_eifini_v4`.`kd_customer_today_pay` trx id 1788888424 lock_mode X #表示该记录锁为排他锁locks rec but not gap waiting #表示要加的锁为记录锁、并且处于锁等待状态Record lock #表示正在等待的是记录锁,heap no 127 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000024d43049; asc     $ 0I;; 1: len 8; hex 800000000098d854; asc        T;; 2: len 8; hex 80000000000dc94e; asc        N;;*** (2) TRANSACTION:TRANSACTION 1788888433, ACTIVE 2 sec starting index readmysql tables in use 1, locked 1538 lock struct(s), heap size 79400, 681 row lock(s), undo log entries 323MySQL thread id 147743, OS thread handle 0x7f8bf24ab700, query id 4136771738 10.8.251.167 app_ecrm updatingupdate kd_customer_today_pay set  last_pay_time=(case when last_pay_time>'2020-08-02 06:48:53'                      then last_pay_time else '2020-08-02 06:48:53' end)  where sys_customer_id=617885769 and shop_id=10016852*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 732 page no 229 n bits 624 index `AK_sys_customer_id_shop_id` of table `ecrm_eifini_v4`.`kd_customer_today_pay` trx id 1788888433 lock mode SRecord lock, heap no 127 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000024d43049; asc     $ 0I;; 1: len 8; hex 800000000098d854; asc        T;; 2: len 8; hex 80000000000dc94e; asc        N;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 732 page no 229 n bits 624 index `AK_sys_customer_id_shop_id` of table `ecrm_eifini_v4`.`kd_customer_today_pay` trx id 1788888433 lock_mode X locks rec but not gap waitingRecord lock, heap no 127 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000024d43049; asc     $ 0I;; 1: len 8; hex 800000000098d854; asc        T;; 2: len 8; hex 80000000000dc94e; asc        N;;*** WE ROLL BACK TRANSACTION (1)

行锁实现方式

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
• 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
• 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。
• 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的(后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁)。

间隙锁

用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB使用间隙锁的目的

1. 防止幻读，以满足相关隔离级别的要求；
2. 满足恢复和复制的需要：

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制(复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复)有以下特点：

一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。

二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

不同隔离级别下的一致性读及锁的差异

锁竞争情况

可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：

show status like 'innodb_row_lock%'; | Innodb_row_lock_current_waits #当前等待锁的数量| Innodb_row_lock_time #系统启动到现在，锁定的总时间长度| Innodb_row_lock_time_avg #每次平均锁定的时间| Innodb_row_lock_time_max #最长一次锁定时间| Innodb_row_lock_waits #系统启动到现在总共锁定的次数

查看相关表：

mysql8.0的表位置有差异。可以通过select version();查看当前的数据库服务版本。

# 当前运行的所有事务select * from information_schema.innodb_trx;# 重点查询select trx_id, trx_state, trx_requested_lock_id, trx_query, trx_operation_state,trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks from information_schema.innodb_trx;| trx_id #事务ID| trx_state  #事务状态：| trx_started #事务开始时间；| trx_requested_lock_id  #innodb_locks.lock_id| trx_wait_started  #事务开始等待的时间| trx_mysql_thread_id  #事务线程ID| trx_query #具体SQL语句| trx_operation_state #事务当前操作状态| trx_tables_in_use #事务中有多少个表被使用| trx_tables_locked #当前执行 SQL 的行锁数量| trx_lock_structs #事务保留的锁数量| trx_lock_memory_bytes #事务锁住的内存大小(B)| trx_rows_locked #事务锁住的行数| trx_rows_modified #事务更改的行数| trx_concurrency_tickets #事务并发票数| trx_isolation_level #事务隔离级别| trx_unique_checks #是否唯一性检查| trx_foreign_key_checks #是否外键检查| trx_last_foreign_key_error #最后的外键错误

# 当前出现的锁select * from information_schema.innodb_locks;# mysql8 select * from performance_schema.data_locks;| lock_id #锁ID| lock_trx_id #拥有锁的事务ID| lock_mode #锁模式| lock_type #锁类型| lock_table #被锁的表| lock_index #被锁的索引| lock_space #被锁的表空间号| lock_page #被锁的页号| lock_rec #被锁的记录号| lock_data #被锁的数据

# 锁等待的对应关系select * from information_schema.innodb_lock_waits;# mysql8 select * from performance_schema.data_locks_wait;| requesting_trx_id #请求锁的事务ID| requested_lock_id #请求锁的锁ID| blocking_trx_id #当前拥有锁的事务ID| blocking_lock_id #当前拥有锁的锁ID

LOCK TABLES 和 UNLOCK TABLES

Mysql也支持lock tables和unlock tables，这都是在服务器层(MySQL Server层)实现的，和存储引擎无关，它们有自己的用途，并不能替代事务处理。 (除了禁用了autocommint后可以使用，其他情况不建议使用)：

• LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
• UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时， 或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁

LOCK TABLES语法

• 在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则MySQL 不会给表加锁；
• 事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES会隐含地提交事务；
• COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用UNLOCK TABLES 释放表锁。

正确的方式见如下语句： 例如，如果需要写表 t1 并从表 t 读，可以按如下做：

SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES;

使用LOCK TABLES的场景

给表显示加表级锁(InnoDB表和MyISAM都可以)，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。(与MyISAM默认的表锁行为类似)

在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，并且 MySQL 不支持锁升级。也就是说，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

其实，在MyISAM自动加锁(表锁)的情况下也大致如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。

例如，有一个订单表 orders，其中记录有各订单的总金额 total，同时还有一个 订单明细表 order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检 查这两个表的金额合计是否相符，可能就需要执行如下两条 SQL：

Select sum(total) from orders; Select sum(subtotal) from order_detail;

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中， order_detail 表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read local; Select sum(total) from orders; Select sum(subtotal) from order_detail; Unlock tables;

(在 LOCK TABLES 时加了“local”选项，其作用就是允许当你持有表的读锁时，其他用户可以在满足 MyISAM 表并发插入条件的情况下，在表尾并发插入记录(MyISAM 存储引擎支持“并发插入”))

死锁(Deadlock Free)

• 死锁产生：
• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
• **检测死锁：**数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
• **死锁恢复：**死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
• **外部锁的死锁检测：**发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
• **死锁影响性能：**死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测(使用innodb_deadlock_detect配置选项)可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

避免死锁

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖(行)使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
• 通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
• 改变事务隔离级别

如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

一些优化锁性能的建议

• 尽量使用较低的隔离级别；
• 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
• 选择合理的事务大小，小事务发生锁冲突的几率也更小
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ(读提交)和REPEATABLE READ(可重复读)两种隔离级别下工作
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能

乐观锁、悲观锁

• 乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。

乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

• 悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

附：隔离(Isolation)级别设置

#查看当前事物级别：SELECT @@tx_isolation;

# 设置mysql的隔离级别：# set session transaction isolation level# 设置read uncommitted级别：set session transaction isolation level read uncommitted;# 设置read committed级别：set session transaction isolation level read committed;# 设置repeatable read级别：set session transaction isolation level repeatable read;# 设置serializable级别：set session transaction isolation level serializable;