mysqlshell 关闭mysql服务

转载

mob64ca13f9a97c 2024-12-28 19:35:46

一、并发控制

1.1 读写锁

读写锁，即共享锁(shared lock)和排他锁(exclusive lock)，读锁是共享的，相互之间不阻塞，多个用户在同一时刻可以同时读取同一个资源，而互不干扰；写锁则是排他的，一个写锁会阻塞其他的写锁和读锁，在同一给定时间里，只有持有锁的用户能执行写入，并防止其他用户读取正在写入的同一资源。

整个锁的内容非常多，详情：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

1.2 锁粒度

1.2.1 表锁

表锁是MySQL中最基本的锁策略，它会锁定整张表，但却是开销最小的策略。写锁比读锁有更高的优先级，因此一个写锁请求可能被插入到读锁队列的前面，但读锁不能插到写锁的前面。

存储引擎可以管理自己的锁，但MySQL本身还是会使用各种有效的表锁来实现不同的目的，例如服务器会为诸如ALTER TABLE之类的语句来使用表锁，而忽略存储引擎的锁机制。

1.2.2 行级锁

行级锁可以最大程度地支持并发处理(同时也带来了最大的锁开销)。行级锁只会在存储引擎层实现，而MySQL服务层没有实现，服务层完全不了解存储引擎中的锁实现。

二、事务

1 示例1：
  2 set transaction;
  3 update a set money = money-100 where id = 111;
  4 update b set money = money+100 where id = 111;
  5 commit;
  6

事务是一个原子性的执行单元，由一组SQL语句组成。事务内的语句要么全部执行成功，要么全部执行失败。事务有着严格的定义，必须同时满足ACID特性。事务的目的是为了保证数据最终的一致性。

原子性(atomicity)：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功要么全部失败回滚。
一致性(consistency)：数据库总是从一个一致性状态转换到另一个一致性状态，这也是引入事务的主要目的。在示例1中，即使执行到二、三条语句时系统崩溃，a和b中money也不会增加或减少100，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。
隔离性(isolation)：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。示例1中，在事务未提交以前，即使已经执行完第二条语句，此时另一个客户去访问a时，看到的money并没有被减去100。
持久性(durability)：一旦事务提交，则其所作的修改就会永久保存到数据库中。

事务处理过程会大大增加系统开销，因此用户可以根据业务是否需要事务处理，来选择合适的存储引擎。对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎，可以获得更高的性能，即使存储引擎不支持事务，也可以通过lock tables语句为应用提供一定程度的保护。

2.1 事务日志

事务日志可以帮助提高事务到的效率。使用事务日志，存储引擎可以在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志到的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以此种方式更快。事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回磁盘。大多数存储引擎都是这么实现的，修改数据需要写两次磁盘。

2.2 事务实现

事务隔离性是由锁来实现的，原子性、一致性和持久性是通过InnoDB提供的两种事务日志redo log和undo log来完成的。redo log称为重做日志，用来保证事务的持久性。undo log是回滚日志，用来保证事务的一致性。

rodo和undo的作用都可以视作为是一种恢复操作，但undo log不是redo log的逆向过程。redo恢复提交事务修改的页操作，通常是物理日志，记录的是页的物理修改操作(例，事务执行一个update操作，则记录的是磁盘数据页的变化，不是逻辑上的从某个值修改到某个值)；而undo回滚行记录到某个特定版本，是逻辑日志，根据每行记录进行记录。

2.2.1 rodo log

rodo log包括两部分：一是内存中的重做日志缓冲(rodo log buffer)，该部分是易失的；二是磁盘上的重做日志文件(redo log file)，该部分是持久的。重做日志记录了事务的行为，可以通过其对页进行“重做”操作。

InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久化，即当事务提交时，必须先将该事务的所有日志写入到磁盘的redo log file和undo log file中进行持久化。为确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync( )操作。要写入到磁盘上到的log file中，中间还要经过操作系统内核空间的os buffer，调用fsync( )的作用就是将os buffer中的日志刷到磁盘上的log file中。

图2.1 事务日志从内存写入磁盘

MySQL提供innodb_flush_log_at_trx_commit变量控制刷新策略，共有3个值。

1 示例2：
  2 update test set name='kong' where id=1;

该SQL经过MySQL服务器执行过上层的一系列操作后，该语句内容到达存储引擎，存储引擎将该要更改数据的原数据所在的磁盘页读取到内存，执行update操作，这时就会产生rodo log，将update造成的数据页变化记录下来，在commit之后，将redo log buffer区域的日志刷到磁盘的redo log file中。更新后的数据也在某个时刻(可能是立刻也许是未来某个服务器空闲的时刻)刷回磁盘，这也就是redo log的作用，假如这时服务器宕机，内存中已更新的数据还没有刷回磁盘，但事务已经提交，这时磁盘内数据和执行完事务后的数据不一致，也就是数据没能持久化，这时就需要redo log进行恢复操作。

图2.2 redo log的生成过程

2.2.2 undo log

undo log有两个作用：提供回滚和多版本并发控制(Mutiversion Concurrency Control,MVCC)。undo log是逻辑日志，记录了行数据的历史版本信息。

对于回滚操作，里面记录了回滚需要的信息，可以理解为里面记录了与当前执行操作相反的操作，当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。例如当前insert一条数据时，则undo log中会记录一条对应的delete记录，在回滚时执行这个delete。但这种恢复是逻辑上的，例如你执行完insert后，这个事务会导致分配一个新的段，表空间会增大，回滚时只是将insert的数据删除，表空间是不会变化的，否则代价太大了。

除了回滚操作，InnoDB中的MVCC也是通过undo完成的。当用户读取一行记录时，若该记录已经被其他事务占用时，当前事务可以通过undo信息读取之前行版本信息，以此实现非锁定读取。此外，undo log也会产生rodo log，因为它也需要进行持久化保护。

(1) undo log格式

undo log分为insert undo log和update undo log两种。

insert undo log是指在insert操作中产生的undo log，它不用提供MVCC机制，会在事务提交之后直接删除。因为insert操作只对事务本身可见，即insert是新插入的一行，不可能之前的事务会对它有依赖。

update undo log记录的是对delete和update操作产生的undo log。该undo log需要提供MVCC机制，因此不能在事务提交时就删除。提交时放入undo log链表，等待purge线程进行最后删除。

这里可能理解需要先了解MVCC机制，后面有详细解释。

示例3：

图2.3 insert undo log在事务提交后立即删除的原因

在图2.3中，在REPEATABLE READ级别下有三个事务：

1 tx_1：update test set name='zhao' where id=1;
  2 tx_2：select * from test;
  3 tx_3：insert test values(2,'qian');

当tx_1执行update操作，这时会产生一个undo log，即p1_undo1。同时tx_2执行，发现该行已经被加锁，然后它不等待锁释放，而是去读取一个历史版本，假设p1_undo1符合其读取规则，因此就会查询到p1_undo1版本的数据。接着tx_1提交了，如果在tx_1提交后它产生的undo log立即删除，那么如果此时tx_2又执行一个查询，因为undo log已删除，那么它的查询结果就可能与第一次的不同。
tx_3是一个插入语句，它是插入一个新的记录，锁定的是新生成的行，对查询之前的数据没有影响。而update/delete锁定的是原来的数据，因此查询时要么等待锁被释放，要么读取历史版本。

这个例子可能放在这里不合适，但是继续向下看，就会逐渐理解。

2.3 隔离级别

1 select @@tx_isolation; select @@transaction_isolation;
  2 --查看事务隔离级别
  3 set session/global transaction isolation level ***;
  4 --设置事务隔离级别(或修改配置文件)

SQL标准中定义了四种隔离级别：

READ UNCOMMITTED(未提交读)：在事务中的修改即使没有提交，对其他事务也都是可见的。一个事务可以读取到其他事务未提交的数据，这也被称为脏读(Dirty Read)。
READ COMMITED(提交读)：提交读满足隔离性的简单定义，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别避免了脏读，但是满足不了不可重复读(nonrepeatable read)，一个事务多次同样的查询，可能会得到不一样的结果。
REPEATABLE READ(可重复读)：该级别解决了脏读，也保证了在同一个事务中多次读取同样记录的结果是一致的，它也是MySQL的默认隔离级别。但该级别无法解决幻读(Phantom Read)的问题。幻读指的是当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围记录时，会产生幻行(Phantom Row)。
SERIALIZABLE(可串行化)：该级别是最高隔离级别。通过强制事务串行化执行(但实际MySQL不是真的强制序列化的，见后)，避免了幻读问题。简单来说，SERIALIZABLE会在读取的每一行上都加上锁，所以可能导致大量的超时和锁争用的问题。实际中也很少用到这个隔离级别。

2.3.1 脏读、不可重复读、幻读

对于这三种问题和隔离级别的设计，根本上是为了解决事务下的并发问题。

1 show variables like '%autocommit';
  2 --查看提交方式
  3 set @@autocommit = 0;
  4 --开启手动提交(1为自动提交)

示例4： test(id int primary key, name char(4));

(1) 脏读：一个事务可以读取到其他事务未提交的数据。

1 session1：                                          session2:
  2 begin;                                              begin;
  3 insert into test(id,name) values(1,'chen');
  4                                                     select * from test;

RU级别下测试，session2读到了session1未提交的数据。

(2) 不可重复读：一个事务执行多次同样的读，每次读取都能看到其他事务提交的最新数据，得到不同的结果。

1 session1：              session2：
  2 begin;                  begin;
  3                         update test set name='qian' where id=2;
  4 select * form test;
  5                         commit;
  6 select * from test;

RC级别下测试，在session2提交之前，session2第一次查询读到(2, 'zhao')，在session2提交之后，session1第二次查询读到(2,'qian')。READ COMMITED避免了脏读，但是在session1一个事务内，第二次读取到其他事务已提交的数据，导致两次查询得到的数据不同。

(3) 幻读：当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围记录时，会产生幻行。

1 session1：                                    session2:
  2 begin;                                        begin;
  3 update test set name='kong' where id>1;
  4                                               insert test values(4,'feng');
  5                                               commit;
  6 select * from test;

session1开始执行，将id大于1的全部修改为空，然后session2这时执行一条插入，当session1执行查询时，发现id大于1的没有被全部修改，这就产生了幻行。当然这是在read commit隔离级别下进行的，示例中除了幻行，本身存在不可重复读问题。

三、 MVCC机制

MVCC是数据库常用的处理读写冲突的手段，目的在于提高数据库高并发场景下的吞吐性能，不同数据库的实现有所区别。首先要明确的是MVCC是为了解决因为写锁的阻塞而造成的读操作的并发阻塞问题，然后我们具体介绍InnoDB的MVCC实现。

3.1 MVCC实现

两个事务A和B开始执行，A要修改数据d并先开始执行，同时B要查询d，这时A因为修改操作拿到锁，B就无法访问该数据，为了处理这种冲突，InnoDB使用回滚段中的信息为B查询构建该数据的早期版本。注意：执行MVCC机制是针对普通读操作，是在一致性读下为其返回一个历史版本，而DML(update/insert/delete)和其他的一些操作是在表中原数据上进行的(可能出现阻塞)。所以这可能和我们理解的MVCC(即修改操作也在原数据副本上进行，然后与原数据比较版本进行替换)不同。

它的具体实现主要需要行的事务版本号、隐藏字段、undo log和read view。

(1) 事务版本号

每个事务在开启时都会生成一个自增长的事务ID，用于表示该事务。

(2) 行的隐藏字段

在内部，InnoDB向数据库中存储的每一行添加三个字段。一个6字节的DB_TRX_ID字段指示插入或更新该行的最后一个事务的事务标识符，如图3.4，每开始一个事务对某记录进行插入或更新操作，都会把自己的事务ID赋给DB_TRX_ID。此外，删除在内部被视为更新，在该更新中，行中的特殊位被设置为将其标记为已删除。每行还包含一个7字节的 DB_ROLL_PTR字段，称为滚动指针。回滚指针指向写入回滚段的撤消日志记录。如果行已更新，则撤消日志记录将包含在更新行之前重建行内容所必需的信息。

图3.4 每行的其中两个隐藏字段

很多书里和博客为了方便理解，把DB_TRX_ID叫做创建版本号，DB_ROLL_PTR叫做删除版本号，我觉得没必要。

还有一个6字节的DB_ROW_ID(即在未指定主键和唯一字段时，数据库自动生成到的一个隐藏索引)字段，包含一个行ID，该ID在插入新行时会单调增加。如果 InnoDB自动生成聚集索引，该索引包含行ID值。否则，该 DB_ROW_ID列不会出现在任何索引中。

(3) undo log

上面说过undo log的作用，回滚段中的撤消日志分为插入和更新撤消日志。插入撤消日志仅在事务回滚中才需要，并且在事务提交后可以立即将其丢弃。更新撤消日志也用于一致的读取，但是只有在不存在为其InnoDB分配了快照的事务(一致的读取可能需要更新撤消日志中的信息以建立数据库的早期版本)后，才可以将其删除行。

如图3.5当执行一个update/delete/insert操作时，它会产生一个undo log(当然实际的undo log结构不是这样的)，DB_ROLL_TRP会指向undo log。

图3.5 从原数据指向undo log模拟图

定期提交事务，包括仅发出一致读取的事务。否则， InnoDB将无法丢弃更新撤消日志中的数据，并且回滚段可能会变得太大而填满表空间。

3.1.1 read view

这里需要将read view单独拿出来说。上面说，在有写操作阻塞时，InnoDB使用回滚段中的信息为查询构建该数据的早期版本，但回滚段中的历史版本很多，这时就需要用到read view。

首先read view是什么？官方文档：它是InnoDB的MVCC机制使用的内部快照。某些事务（取决于它们的隔离级别）可以看到事务（或在某些情况下是语句）启动时的数据值。可重复读、提交读和未提交读隔离级别下使用read view。

(1) read view结构

图3.6 read view结构

假设当前的活跃事务链表：ct-trx —> trx7 —> trx5 —> trx3 —> trx1;

ct-trx表示当前事务id，那么对应上面数据结构如下：

1 read_view->creator_trx_id = current-trx; 当前的事务id
  2 read_view->up_limit_id = trx1; 当前活跃事务的最小id
  3 read_view->low_limit_id = trx7; 当前活跃事务的最大id
  4 read_view->trx_ids = [trx1, trx3, trx5, trx7]; 当前活跃的事务的id列表
  5 read_view->m_trx_ids = 4; 当前活跃的事务id列表长度

trx_ids是活跃事务链表，这个链表表示此时还在活跃的事务，指的是那些在当前快照中还未提交的事务，所以在该快照生产之后新建的事务和之前已经commit的事务不再该链表。

(2) read view的作用原理

read_view中不会显示所有的数据行，只会显示“可见”的记录，那些是“可见”的？由官方文档，read view在执行一致性读(Consistent Nonlocking Reads)的时候生成，一致性读即一个普通的select。也就是说只有执行普通的select查询时才会生成read view，DML操作(update/insert/delete)不会生成read view。

read view在进行“可见”判断的时候用到三个属性：DATA_TRX_ID 表示每个数据行的最新的事务ID；up_limit_id表示当前快照中的最先开始的事务；low_limit_id表示当前快照中的最慢开始的事务，即最后一个事务。

1）如果被访问版本的data_trx_id小于up_limit_id，说明生成该版本的事务在 ReadView 生成前就已经提交了，那么该版本可以被当前事务访问。

2）如果被访问版本的data_trx_id大于当前事务的最大值，说明生成该版本数据的事务在生成 ReadView 后才生成，那么该版本不可以被当前事务访问。

3）如果up_limit_id <= DATA_TRX_ID < low_limit_i，那就需要判断一下data_trx_id的值是不是在trx_ids列表中。如果在，说明创建read view时生成该版本所属事务还是活跃的，因此该版本不可以被访问此时需要从DB_ROLL_PTR指针所指向的回滚段中取出最新的undo-log的版本号, 然后用它继续重新开始整套比较算法；如果在，说明创建read view时生成该版本的事务已经被提交(因为记录DATA_TRX_ID只有在事务提交之后才会被修改，所以在trx_ids中找不到说明操作该记录的事务在查找过程中已经被提交)，该版本可以被访问。这里其实不用纠结算法，知道查询执行MVCC机制得到一个合适的历史版本即可。

(3) read view不同隔离级别的作用方式

1）read view作用于除SERIALIZABLE级别下的三个级别。READ UNCOMMITTED级别，SELECT语句以非锁定方式执行，但是可能会使用行的早期版本。因此，使用此隔离级别，此类读取不一致。

2）READ COMMITTED级别下，事务中每次执行一个普通select操作都会创建新的read view，即使在同一事务中，每个一致的读取都将设置并读取其自己的新快照。

3）REPEATABLE READ级别下，read view只在第一次select时创建，同一事务中的一致读取将读取第一次读取建立的快照，如果在同一事务中发出多个普通(非锁定)SELECT语句，则这些SELECT语句彼此之间也是一致的。

另外很重要的一点，也是很例外的一点，查询看到发布该查询的事务本身所做的改变(本质上还是与锁有关)，即在多次查询中间有相关的修改操作，那么后面的查询是可以看到这个修改操作执行后的数据的。这句话引出关于幻读的一个问题，后面具体解释。

3.2 多版本和二级索引

这部分直接从官网扒下来做了解，可以不用看，不影响后面的阅读，读了才有影响。

InnoDB多版本并发控制（MVCC）对二级索引的处理不同于聚集索引。聚集索引中的记录会就地更新，其隐藏的系统列指向撤消日志项，可以从中重建早期版本的记录。与聚集索引记录不同，二级索引记录不包含隐藏的系统列，也不会就地更新。

更新辅助索引列时，旧的辅助索引记录将被删除标记，新记录将被插入，并最终清除已删除标记的记录。当二级索引记录被删除标记或二级索引页被更新的事务更新时，InnoDB会在聚集索引中查找数据库记录。在聚集索引中，检查记录的DB_TRX_ID，如果在读取事务启动后修改了记录，则从撤消日志中检索记录的正确版本。

如果二级索引记录被标记为删除，或者二级索引页被更新的事务更新，则不使用覆盖索引技术。InnoDB没有从索引结构返回值，而是在聚集索引中查找记录。

但是，如果启用了索引条件下推（ICP）优化，并且部分WHERE条件只能使用索引中的字段进行计算，那么MySQL服务器仍然会将WHERE条件的这一部分向下推送到存储引擎，在存储引擎中使用索引对其进行评估。如果找不到匹配的记录，将避免聚集索引查找。如果找到匹配的记录，即使在删除标记的记录中，InnoDB也会在聚集索引中查找该记录。

3.3 一致性读和锁定读

上面我们谈到，MVCC机制是为了读的问题，read view是在一致性读的时候生成的。根据官方文档，一致性读即一个普通的查询。

1 When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a timepoint according to which your query sees the database.

在我们印象中“读”(如读文件)是从磁盘读取，但根据文档这里的读专指select(当然select也是从磁盘拿数据)。这里的读有两种方式，一是不加读锁，采用MVCC模式的一致性读(很多书里叫快照读)；二是加锁的锁定读(很多书里叫当前读)。

3.3.1 一致性读

一致性读即一个普通的select操作，它意味着InnoDB使用多版本控制向查询显示某个时间点的数据库快照。查询会看到在该时间点之前提交的事务所做的更改，而不会看到稍后或未提交的事务所做的更改。此规则的例外情况是，查询可以看到同一事务中先前语句所做的更改。此异常会导致以下异常：如果更新表中的某些行，SELECT会看到更新的行的最新版本，但也可能会看到任何行的旧版本。如果其他会话同时更新同一个表，则异常意味着您可能会看到该表处于数据库中从未存在的状态。

一致性读是InnoDB在READCOMMITTED和REPEATABLE READ隔离级别处理SELECT语句的默认模式。一致读取不会对其访问的表设置任何锁(因为读的内容是历史版本)，因此，在对表执行一致读取的同时，其他会话可以自由修改这些表。

1 示例5：
  2 session A             session B
  3 begin;                begin;
  4 select * from test;
  5 --empty set
  6                       insert into test values(3,'sun');
  7 select * from test;
  8 --empty set
  9                       commit;
 10 select * from test;
 11 --empty set
 12 commit;
 13 
 14 select * from t;
 15 --| 1 | 2 |

上例A执行一致性读，B在此期间可以执行插入操作，但A在开始后未提交之前都读不到B的插入，直到重新执行一个SELECT或START TRANSACTION WITH CONSISTENT SNAPSHOT，来提前时间点。但也有以下要注意的例外情况。

数据库快照适用于事务中的SELECT语句，而不一定适用于DML语句(本质上是因为普通的select不加锁，MVCC下它读取的历史版本或空闲的当前版本，并没有对数据进行修改；而DML操作会加锁，在原表中进行修改操作)。如下例：

例：如果插入或修改某些行，然后提交该事务，则另一个并发下的可重复读取事务发出的DELETE或UPDATE语句可能会影响那些刚刚提交的行，即使会话无法查询它们。如果某个事务确实更新或删除了由其他事务提交的行，则这些更改确实对当前事务可见。例如，您可能会遇到以下情况(这里演示的是REPEATABLE READ级别下的特别情况)：

1 示例6：
  2 SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
  3 -- Returns 0: no rows match.
  4 
  5 DELETE FROM t1 WHERE c1 = 'xyz';
  6 -- Deletes several rows recently committed by other transaction.

一个事务先执行了查询，并由有查询到‘xyz’，然后它又执行了一个对‘xyz’的删除操作，但是确实删除成功了。因为尽管第一句查询中没有该数据，但是另一个并发的事务同时提交了一个‘xyz’的insert操作，而delete是对原表数据更改，所以delete会在最新数据上执行。

1 示例7：
  2 SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
  3 -- Returns 0: no rows match.
  4 
  5 UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
  6 -- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
  7 
  8 SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
  9 -- Returns 10: this txn can now see the rows it just updated.

一个事务先执行查询，没有查到‘abc’，然后它执行一个update操作，再次查询时能看到‘abc’修改后的数据。同样的原因，更新操作时另一个事务进行了插入操作，更新操作是在原表上进行的，它能够感知并拿到的是最新数据，而第一次查询是通过一致性读进行的，是一个历史数据，而第二次查询由于update的影响，被更改的记录的DATA_TRX_ID改变了，再通过read view时，该事务与该记录ID相同，就拿到了最新数据。

这里也就是关于REPEATABLE READ级别下到底有没有解决幻读问题的疑问，后面详细讲。

此外一致读取对某些DDL语句无效：

一致读取不适用于DROP TABLE，因为MySQL不能使用已经删除的表，InnoDB会销毁该表。
一致读取不适用于ALTER TABLE操作，这些操作生成原始表的临时副本并在生成临时副本时删除原始表。在事务中重新发出一致读取时，新表中的行不可见，因为在获取事务快照时，这些行不存在。在这种情况下，事务返回一个错误：ER_TABLE_DEF_CHANGED，"Table definition has changed, please retry transaction"。

对于包含select子句，如 INSERT INTO ... SELECT，UPDATE ... (SELECT)和 CREATE TABLE ... SELECT，select中没有指定FOR UPDATE或FOR SHARE：

默认情况下，InnoDB对这些语句使用更强的锁，SELECT部分的行为类似于READ COMMITTED，其中每个一致的读取，即使在同一个事务中，都会设置和读取自己的新快照。
要在这种情况下执行非锁定读取，请将事务的隔离级别设置为READ UNCOMMITTED或 READ COMMITTED，以避免对从所选表读取的行设置锁。

3.3.2 锁定读

一致性读采用MVCC机制查询数据，而锁定读不采用该机制，它对读取的记录加锁，读取的原表的最新数据，但可能会阻塞。

如果查询数据，然后在同一事务中插入或更新相关数据，则常规SELECT 语句不能提供足够的保护，其他事务可以更新或删除刚查询的相同行。 InnoDB支持两种类型的锁定读取，这些读取提供了额外的安全性：

(1) SELECT ... FOR SHARE

对读取的任何行设置共享锁。其他会话可以读取行，但在事务提交之前不能修改它们。如果这些行中的任何一行正在被另一个尚未提交的事务更改(即该行正被其他事务到的写锁锁定)，则查询将等待直到该事务结束，然后再使用最新的值。

注：SELECT ... FOR SHARE与SELECT ... LOCK IN SHARE MODE是等效的，但FOR SHARE对table_name, NOWAIT, and SKIP LOCKED的支持需要具体讨论，见后。另外在MySQL8.0.22前后，SELECT ... FOR SHARE需要的资源不一样。

(2) SELECT ... FOR UPDATE

对读取到到的行设置排他锁。在查询过程中，对于搜索遇到的索引记录，SELECT ... FOR UPDATE会锁定该行和任何相关联的索引项，就像对这些行执行UPDATE语句一样。其他事务对于这些行的修改、select或从某些事务隔离级别读取这些数据，都会被阻塞。不过一致性读取忽略任何设置存在在read view的记记录的锁，即SELECT ... FOR UPDATE无法锁定记录的旧版本，此时有另一个事务执行一致性读取，仍会成功。SELECT ... FOR UPDATE需要SELECT权限以及至少一个DELETE、LOCK TABLES或UPDATE权限。

提交或回滚事务时，将释放为SHARE和FOR UPDATE查询设置的所有锁。只有在禁用自动提交时才可以锁定读取。

注：除非在子查询中也指定了锁定读取子句，否则外部语句中的锁定读取子句不会锁定嵌套子查询中表的行。如：SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE；要锁定t2表中的行，子句也要进行锁定读：SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE。

示例8：假设要在表中插入新行 child，并确保子行在表中具有父行parent。首先，使用一致的读取来查询表 PARENT并验证父行是否存在。但这样并不能保证安全性，因为某些其他会话可能会在SELECT之后删除父行，并且此行为你是不知道的，因此可以先执行SELECT … FOR SHARE进行验证并锁定，再进行添加操作。

这里一个疑惑就是SELECT … FOR SHARE已经加了读锁，insert操作要加写锁，那么还能执行吗？可以，因为是在同一个事务，在执行insert时就会把读锁升级为写锁。

3.3.3 用NOWAIT和SKIP LOCKED在锁定读下实现并发

如果某行被某个事务锁定，则对于请求同一锁定行的SELECT ... FOR UPDATE或SELECT ... FOR SHARE事务必须等待，直到阻塞事务释放该行锁定为止。此行为可以阻止事务更新或删除由其他事务查询更新的行，但是，如果希望在请求的行被锁定时立即返回查询，或者如果可以从结果集中排除锁定的行，则不必等待行锁释放。这时可以用NOWAIT和SKIP LOCKED和锁定读一起使用，SELECT … FOR SHARE/UPDATE NOWAIT/SKIP LOCKED。

(1) NOWAIT

使用NOWAIT不会等待锁定读释放锁，查询将立即执行，如果请求的行被锁定，则会失败并显示错误。

(2) SKIP LOCKED

使用SKIP LOCKED不会等待锁定读释放锁，查询立即执行，从结果集中删除锁定的行(实际是跳过那些被锁定的行)。

NOWAIT和SKIP LOCKED仅适用于行级锁。并且SKIP LOCKED的查询会返回不一致的数据视图。因此，SKIP LOCKED不适用于一般事务性工作

另外很多博客把update、delete和insert也叫做当前读，这就是我为什么之前会说这里的“读”仅指select，虽然DML操作也要加锁，和锁定读效果上一样，但我觉得概念有必要分清。我想他们会有这个结论的原因是文档上的这句话：

1 For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE statements, and DELETE statements

我想他们可能是漏掉了逗号，以及没看“locking reads”的内容。

四、隔离级别下的锁问题

MySQL锁的内容是很多的，这里简单说一下各隔离界别中的加锁内容，不深究，仅结合上面内容更好理解。

在InnoDB引擎，存在三种锁的算法Record lock、Gap lock和Next-Key lock，假设存在表a{ 1 2 3 4 }，则：

(1) Record lock(行锁)：在单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，如果该表上没有任何索引，那么就会用到前面说的一个隐藏字段。该锁在表a上表现为：{ [1] [2] [3] [4] }，它锁不住索引间隙。

(2) Gap lock(间隙锁)：锁住一个索引区间(开区间)，根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为(A,B)，并不包括该索引记录本身。该锁在表a上表现为：{ (无穷,1) 1 (1,2) 2 (2,3) 3 (3,4) 4 (4,无穷) }，它锁不住索引项。

(3) Next-Key lock：是Record lock+Gap lock，该锁在表a上大致表现为：{ (无穷, 1] (1, 2] (2,3] (3,4] (4,无穷) }，既能锁住索引，也能锁住间隙。

需要注意的是这里的索引有聚簇索引和二级索引，有所区别，这里不讲。大致了解了InnoDB中几种锁的算法之后，再看几种情况：

1）对语句(update/insert/select…for update/…)中的命中条件的行加行锁(种类可能是读锁，可能是写锁，具体情况而视)。

2）READ COMMITTED：一致性读不加锁，对于锁定读取(SELETE … FOR UPDATE/FOR SHARE)，UPDATE和DELETE语句，InnoDB只锁定索引记录，而不锁定它们前面的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。由于间隙锁定被禁用，可能会出现幻行问题，因为其他会话可以在间隙中插入新行(下面示例)。

读提交隔离级别只支持基于行的二进制日志记录。如果使用binlog_format=MIXED的READ COMMITTED，服务器将自动使用基于行的日志记录。

使用“已提交读取”其他效果：

对于UPDATE或DELETE语句，InnoDB只对更新或删除的行持有锁。非匹配行的记录锁在MySQL计算WHERE条件后释放。这大大降低了死锁的概率，但它们仍然可能发生。
对于UPDATE语句，如果某行已经被锁定，InnoDB执行“半一致”读取，将最新提交的版本返回到MySQL，以便MySQL判断该行是否符合UPDATE的WHERE条件。如果行匹配（必须更新），MySQL会再次读取该行，这次InnoDB要么锁定它，要么等待对它的锁定。

1 示例9：
  2 session A                                  session B
  3 update test set name='zhao' where id>3;
  4 --test共11条数据
  5                                            insert test values(12,'wei');

A先执行，未提交，B执行时没有阻塞，B提交后，A再执行查询，出现幻行。

3）REPEATABLE READ：一致性读不加锁，对于锁定读取(SELETE … FOR UPDATE/FOR SHARE)，UPDATE和DELETE语句，锁定取决于语句是使用具有唯一搜索条件的唯一索引，还是使用范围类型的搜索条件。

对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不锁定其前面的间隙。
对于其他搜索条件，InnoDB锁定扫描到的索引范围，使用gap锁或next key锁阻止其他会话插入到该范围覆盖的间隙中。

4）READ UNCOMMITTED：SELECT语句以非锁定方式执行。

5）SERIALIZABLE：这个级别类似于REPEATABLE READ，如果禁用自动提交，InnoDB隐式地将所有普通的SELECT语句转换为SELETE … FOR SHARE。如果启用了自动提交，则选择是它自己的事务。因此，它被认为是只读的，并且如果以一致的（非锁定）读取，则可以序列化它，并且不需要阻塞其他事务。

4.1 RR级别下的幻读问题

RR是可重复读，那么它解决了幻读吗？MySQL是怎么做的

首先幻读问题定义是什么，文档上有：

1 The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.

如果按这个定义，在上面讨论read view曾说过这个。看例子，我们在RR下执行(test表中共5行)：

1 示例10：
  2 session A                                   session B
  3 begin；                                     begin;
  4 select * from test；
  5                                             insert test values(6,'chen')；
  6 update test set name='kong' where id>1;     commit;
  7 select * from test；
  8 commit；

结果A中两次查询结果不同，是不是就没有解决幻读呢？仅从这个定义来看是的，而且还是可重复读，但在官网中的具体描述，结合我的理解。首先脏读、不可重复读和幻读是并发下产生的问题，最终我们要保证的是数据的可靠性、数据库的一致性，那么幻读问题的本质是在做范围内的修改操作时，没有对整个范围进行上锁，导致并发时，该范围内出现了其他数据，与之前修改操作应该的到的结果不符。就如上面A和B，虽然两次查询结果不同，但是A的更新会被阻塞，直到B提交，这时A的执行结果是与预期效果相符的。

1 示例11：
  2 session C                                      session D
  3 begin；                                        begin;
  4 select * from test；
  5 update test set name='chen' where id>2;
  6                                                insert test values(7,'sun')；
  7                                                commit;
  8 select * from test；
  9 commit；
 10

在RC级别执行C和D，C中第二次查询结果与update的执行预期不一样（即第二次查询时，update没能对符合条件的行进行更新)，这就是幻行，因为RC级别仅会锁定id为3、4、5、6的行，且update是先执行的。但在RR级别执行C和D，D会一直阻塞，除非C先提交，因此C中的update的执行结果会和预期相同(即在C第二次查询时，update操作对全部数据进行了更新)。

所以虽然RR是可重复读，但MySQL在该级别采用了Next-Key lock，是能够解决幻读问题的。

五、死锁

当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个资源时也会产生死锁。

1 示例12：
  2 session A                                     session B
  3 begin；                                       begin；
  4 update test set name='zhao' where id=4；      update test set name='qian' where id=5；
  5 update test set name='qian' where id=5；      update test set name='zhao' where id=4；
  6 commit；                                      commit；

如果两事务都执行了第一条语句，那么在执行第二条语句时就会发现对方持有了锁，而不得不进行等待，从而陷入死锁。

为了解决这个问题，数据库实现了各种各样的死锁检测和死锁超时机制。InnoDB就有很强检测机制，能检测到死锁的循环机制，并立即返回一个错误。还有另一种方法，当查询的时间达到锁等待超时的设定后放弃锁请求。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。事实上大多数情况下重新执行因死锁回滚的事务即可。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。