MySQL综合

前言

读者知悉

在阅读本文之前，请各位读者知悉：

1. 本文讲解的MySQL以InnoDB为存储引擎，各种机制的讲述也默认以InnoDB为存储引擎下MySQL实现的机制为准。
2. 本文使用的可视化工具是SQLyog
3. 本文为博主在日常学习过程中阅读优质的博文并结合自己的理解写下的文章，多有谬误，敬请指出
4. 本文章还在持续更新中，敬请期待

官方文档指路：dev.mysql.com/doc/refman/5.7/en/

官方源码指路：github.com/facebook/mysql-5.6/tree...

---

理论：

常规操作

​ 常规操作并非本文重点内容，因此只会稍作讲述，具体更多的常规操作，请在互联网中寻找优质资源自行学习。

登录

MySQL -u root -p  --登录

数据库

--查看 
show databases;
--创建
create database gundam;
--切换
use gundam;
--删除
drop database gundam;
--修改编码
alter database gundam character set utf8;

表

--创建数据表
create table undertale(
    ->id int primary key auto_increment,
    ->name varchar(30) not null,
    ->room double(5,2),
    ->kills double(5,2),
    ->love double(5,2)
    ->);

primary key    --定义为主键 
auto_increment --自增的属性 用于主键 数值加1
engine 设置存储引擎 
charset 设置编码

--显示数据表名
show tables;

--查看表的结构
desc undertale;


--查看指定表的创建语句
show create table undertale;

--删除数据表
drop table undertale;

--truncate关键字
与drop的区别：truncate只删除数据不删除表的结构(定义)，释放空间
truncate table undertale;

删除表
truncate table 表名称;

--修改表名
alter table love rename to LOVE;

--修改表 添加列
alter table undertale
->add column fun double(5, 2) first;
--------
alter table undertale
->add column fun double(5, 2) after name;

--修改表 修改数据类型
alter table undertale
->modify id float;

--修改表 修改列名
alter table undertale
->change love LOVE int;

sql操作(增删改查)

增加(插入)

INSERT INTO account_info (account_id, account_email, account_nickname, acounttypr_id) VALUES (3, '123@qq.com', '观星先生', 2);

删除

DELETE FROM account_info WHERE acount_id = 2;

修改

UPDATE account_info SET account_nickname='Tieria', account_email='Watch@qq.com' WHERE id=3;

查询

SELECT tfa.`fill_index`, tco.object_number AS fill_number,tco.`object_number_fraction` AS fill_fraction
    FROM `qtest_topic` qtt
    INNER JOIN `test_change_object` tco ON qtt.`qtest_topicid` = tco.`qtest_topicid`
    INNER JOIN topic_fill_answer tfa ON qtt.`topic_id` = tfa.topic_id
    WHERE qtt.`qtest_topicid` = 577
    AND tco.`object_number` = tfa.`fill_number`
ORDER BY fill_index

连接查询

子查询

多表查询

…

级联操作

​ 一般来说，mysql是不允许有依赖关系的表数据删除的，如果真的要删，那就只能修改外键依赖然后删

• 如果要修改外键的值，修改时十分麻烦：需要先将引用的外键删除
  • 级联更新：【例】我将部门的id更新后，员工表中部门外键也一同更新
• 如果要研发部门和其部下的员工，需要先删除部门下的所有员工，再将该部门删除
  • 级联删除：【例】属于研发部门和其部下的员工全删除，只需要在外键表中删除外键数据即可，关联的表中的数据一并删除

当出现多张有依赖关系的表时设置级联删除 mysql可以帮你删掉所有关联的数据

删除逻辑是 当父表被删除时，子表（外键设置级联操作）数据也会被级联删除

但是子表删除的时候父表数据不会被删除 而且当子表出现两个外键时，单独删除一个外键的父表 也会被InnoDB拒绝删除（有待考证）

具体命令是
级联操作
-- 1. 添加级联操作
    语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
        FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) 
        ON UPDATE CASCADE 
        ON DELETE CASCADE;
-- 2. 分类：
    1. 级联更新：ON UPDATE CASCADE
    2. 级联删除：ON DELETE CASCAD

具体操作请见实践部分

截断表

​ 截断表可以用于删除表中的所有数据。截断表命令还会回收所有索引的分配页。截断表的执行速度与不带where子句的delete（删除）命令相同，甚至比它还要快。delete（删除）一次删除一行数据，并且将每一行被删除的数据都作为一个事务记录日志；而truncate （截断）表则回收整个数据页，只记录很少的日志项。delete（删除）和truncate（截断）都会回收被数据占用的空间，以及相关的索引。只有表的拥有者可以截断表。

​ 另外，truncate表之后，如果有自动主键的话，会恢复成默认值。

命令是 truncate table users；

常用函数

SUM() 
SUM一般与 GROUP BY 连用 用来分组求和 不需要连很多表

IFNULL(a, 0)
如果a为null 则用0替换

CONCAT(A, B)
将A与B拼接

GROUP_CONCAT(col) 
返回由属于一组的列值连接组合而成的结果

...

小结

​ 本节讲述的是MySQL的基本操作，包括登录、对数据库的系列操作、对数据表的系列操作与对数据的系列操作（包括增删改查）。

​ 其中最为重要的是数据查询，数据查询是数据库中最常用的操作。此外为了实现高性能，优秀的查询语句、合理的库表结构、合适的索引，三者缺一不可。因此作为刚开始接触MySQL的读者来说，有必要重点学习查询操作。

---

事务

​ 实际的SQL执行过程中，有时候我们生产过程需要保证某些SQL要么一起顺序执行成功，要么一起失败回滚，不能结束在中间某个环节。为实现这种需求，那么就有了事务的概念。事务是数据库中保证交易可靠的机制。

​ 比如说，在人员管理系统中，删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！在删除的过程中，我们会希望所有的删除操作全部都执行成功，不能有停在某一步的情况，否则，数据库中就会留下脏数据，影响到其他各种查询操作。

想要获知哪些存储引擎支持事务，请在SQLyog中键入SHOW ENGINES;得到以下结果：

其中Transactions 字段标明存储引擎是否支持事务。

事务特性

原子性

​ 一个事务中所有操作要不全部完成，要不全部不完成，不会结束在中间某个环节

持久性

​ 事务结束后 对事物的所有操作就是永久的，除非有其他事务对数据进行修改，否则即使系统故障也不会丢失

一致性

​ 一致性是指数据处于一种语义上的有意义且正确的状态。一致性是对数据可见性的约束，保证在一个事务中的多次操作的数据中间状态对其他事务不可见。

隔离性

​ 隔离性（isolation）指的是不同事务先后提交并执行后，最终呈现出来的效果是串行的，也就是说，对于事务来说，它在执行过程中，感知到的数据变化应该只有自己操作引起的，不存在其他事务引发的数据变化。
​ 隔离性解决的是并发事务出现的问题

此特性与事务隔离级别连接紧密，具体相关请参见‘事务隔离级别’

原子性和一致性辨析:

​ 原子性和一致性的的侧重点不同：原子性关注状态，要么全部成功，要么全部失败，不存在部分成功的状态。而一致性关注数据的可见性，中间状态的数据对外部不可见，只有最初状态和最终状态的数据对外可见

既然说到了隔离性， 那就有必要深刻讲解一下数据库的事务隔离级别。

事务隔离级别

​ Q：一个概念的出现势必是要用来解决某些问题或者描述某些东西，那么‘事务隔离级别’这个概念是用来干什么的？

​ A：首先我们需要知道，MySQL 是支持并发执行的。有的事务写、有的事务读，如果是读写冲突有可能发生脏写、脏读、不可重复读和幻读问题。而如果是写写冲突则就有可能会发生两类更新丢失问题。

​ 并发是指同一时间支持多个用户对系统进行操作，不管是从效率还是收益上都是有绝对好处的

​ 但是数据库中并发也会带来一些问题，多个用户同时对同一个数据进行操作，会破坏隔离性，让用户以为系统出了问题。

​ 因此为解决并发带来的问题，实现不同程度的并发控制，SQL的标准制定者提出了4种隔离级别：read uncommitted（读未提交），read committed（读已提交），repeatable read（可重复读），serializable（串行化）这几个隔离级别。

PS：在MySQL中，如果使用InnoDB，默认隔离等级是可重复读（Repeatable read）

​ 在详细讲事务隔离级别之前需要先讲清楚数据库的并发场景和并发问题。

​ 因为并发场景下两个人在同时对同一个数据进行操作的时候就会产生冲突，这种冲突就会导致用户发现最终读写出来的结果并不是预期结果 这样就出现了并发问题

数据库并发场景

读-读

​ 不会冲突，不存在任何问题，不需要并发控制

读-写

​ A线程读，B线程更新 B的更新在很多情况下都会给A的查询造成很大困扰 使得很多时候A没法得到自己预想的结果

​ 有线程安全问题, 可能会遇上并发问题：脏读、不可重复读、幻读

写-写

​ 两个线程同时对同一个数据进行更新

​ 有线程安全问题，可能会遇到更新丢失问题 比如第一类更新丢失 第二类更新丢失

并发问题

注意：接下来讲述的并发问题的示例中，事务A和事务B请读者想象成两个用户在不同的终端上进行的操作。

脏读

​ 脏读就是读到了不应该存在的数据，像是下面这样：
​ 事务A更新数据 事务B查。 事务A在Time-5时 事务A撤销了对id = 1 的修改 导致B在两次查询出现数据不一致的情况

​ 这就意味着事务B可以看到事务A在执行过程中任意时刻的修改，那B在执行的过程中有可能会发现需要查询的数值在乱窜（不止一次）

示例：

不可重复读

​ 不可重复读意思是 “我事务B对数据只做查询操作，我连着查了几次。但是突然发现我几次查出来的值都不一样。不应该啊，我只查了数据，并没修改啊，怎么会前后查出来的数据有变化呢？”
​ 实际上是因为我事务B在查的时候 事务A就已经暗中在对数据进行修改了

示例：

幻读

​ 事务A的两次读之间有其他事务写操作，比如事务B统计年龄 > 30，当A两次读数据之间其他事务新添加了记录，所以事务A第二次读取到的数据突然多了一个，仿佛出现了幻觉一般，这就是一种幻读

示例：

不可重复读和幻读区别:

精炼解释：

• 不可重复读的重点是修改:
  同样的条件, 你读取过的数据, 再次读取出来发现值不一样了
• 幻读的重点在于新增或者删除：
  同样的条件, 第1次和第2次读出来的记录数不一样

当然, 从总的结果来看, 似乎两者都表现为两次读取的结果不一致.
但如果你从控制的角度来看, 两者的区别就比较大

对于前者, 只需要锁住满足条件的记录
对于后者, 要锁住满足条件及其相近的记录

参考资料：不可重复读和幻读的区别是什么-MySQL教程-PHP中文网

前三者是读写冲突造成的问题，刚开始看可能稍微有点看不懂
但是如果稍微揣摩一下“我在读书 但是你（幽灵）在旁边乱改动我看到的字的话”
这三种就是读写冲突必然会发生的情况 理解起来也就容易很多了

接下来这种是写写冲突造成的问题：

更新丢失

​ 指两个事务在并发下同时进行更新，后一个事务的更新覆盖了前一个事务更新的情况，丢失更新是数据没有保证一致性导致的。比如，事务A修改了一条记录，事务B在事务A提交的同时也进行了一次修改并且提交。当事务A查询的时候，会发现刚才修改的内容没有被修改，好像丢失了更新。

​ 更新丢失有两类：回滚丢失（Lost update） 和 覆盖丢失（又称两次更新问题， Second lost update）

回滚丢失

​ 在事务A期间，事务B对数据进行了更新；在事务A撤销之后，覆盖了事务B已经提交的数据。

覆盖丢失

​ 在事务A期间，事务B对数据进行了更新；在事务A提交之后，覆盖了事务B已经提交的数据。
​ 第二类丢失更新，实际上和不可重复读是同一种问题。

隔离等级

​ 在’事务隔离级别’ 中简单介绍了四种隔离等级

​ 在这一讲中，将重点讲述四种隔离等级的概念并指明这些概念将解决哪些并发问题（本文中，事务隔离级别、事务隔离等级是同义词）

​ 为让读者有个大致概念，先行放出图如下：

​ 此外，以上四种事务隔离等级都可以防止更新丢失问题。

读未提交（Read uncommitted）

​ 此隔离等级下，事务可以看到其他事务“尚未提交”的修改
​ 会出现脏读问题。

读已提交（Read committed）

​ 此隔离等级下可以避免脏读。

​ 此隔离级别要求事务A提交之后，A修改后的值才能被事务B读到，所以脏读是不可能会发生的，从根本上杜绝了。但read commited会发生不可重复读的情况。
​ 有时候，即使读到的值是一样的，也不能证明没问题。比如有财务挪用了2亿去炒股，然后在月底把2亿还了回来，虽然最终的金额都是一致的，但由于你的对账周期长，就发现不了这种差异。

此时有些读者可能会出现这样的疑问：脏读和不可重复读有什么区别？

此二者区别在于 脏读时，事务A可以看到事务B“尚未提交”的修改，事务B在操作过程中可以乱修改值，这样A的查询结果可能每次都不一样；而在 不可重复读时，事务A只能看到其他事务提交后的结果，事务B在操作中修改数据并提交，导致A在查询时会发现数据有一次变化。

可重复读（Repeatable read）

​ 此隔离等级是MySQL默认隔离级别。

​ 可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。
​ 可重复是对比不可重复而言的，上面说不可重复读是指同一事物不同时刻读到的数据值可能不一致。而可重复读是指，事务不会读到其他事务对已有数据的修改，即使其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这就会遇到幻读问题。

PS: 数据库不同，其支持的事务隔离级别亦不相同：MySQL数据库支持上面四种事务隔离级别，默认为Repeatable read；Oracle 数据库支持Read committed和Serializable两种事务隔离级别，默认为Read committed。

可串行化（Serializable）

​ Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

​ 虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

因为在介绍以上四种隔离等级的时候提到了他们分别可以解决不同的冲突问题，接下来咱们来具体讲解一下这些问题到底是怎么解决的。

隔离等级实现原理

在理解实现原理之前，你需要先知道两个先导概念 — 锁和MVCC

锁

InnoDB Locking 官方文档指路：MySQL :: MySQL 5.7 Reference Manual :: 14.7.1 InnoDB Locking

​ 首先锁和下面的MVCC都是实现用以协调多个进程间并发访问同一共享资源的一种机制。
​ 锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。它是MySQL在服务器层和存储引擎层的的并发控制。

​ 在MySQL中，锁被分成了两类：锁类型（lock_type）和锁模式（lock_mode）。锁类型描述的锁的粒度，也就是把锁具体加在什么地方，有行锁、表锁之分，而行锁还细分为记录锁、间隙锁、插入意向锁、Next-Key等更细的子类型；而锁模式描述的是到底加的是什么锁，是读锁还是写锁。锁模式通常和锁类型结合使用。

接下来分别介绍锁类型和锁模式。

锁类型

粒度锁

粒度锁有三种：页面锁、表级锁、行级锁
MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

• MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
• BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
• InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。
但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

行锁

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.

上文出自MySQL的官方文档，从这里我们可以看出行锁是作用在索引上的，哪怕你在建表的时候没有定义一个索引，InnoDB也会创建一个聚簇索引并将其作为锁作用的索引。

这里还是讲一下InnoDB中的聚簇索引。每一个InnoDB表都需要一个聚簇索引，有且只有一个。如果你为该表定义一个主键，那么MySQL将使用主键作为聚簇索引；如果你不定义一个主键，那么MySQL将会把第一个唯一索引（而且要求NOT NULL）作为聚簇索引；如果上诉两种情况都GG，那么MySQL将自动创建一个名字为GEN_CLUST_INDEX的隐藏聚簇索引。

因为是聚簇索引，所以B+树上的叶子节点都存储了数据行，那么如果现在是二级索引呢？InnoDB中的二级索引的叶节点存储的是主键值（或者说聚簇索引的值），所以通过二级索引查询数据时，还需要将对应的主键去聚簇索引中再次进行查询。

需要注意的是，行锁作用的条件是查询语句的条件字段上是有建立索引，否则InnoDB会加表锁而非行锁。

• 查询条件为非主键索引，会在索引记录上加锁后，在去主键索引上加锁

  这一点很好理解 用户如果条件中选择的列是 二级索引的话 InnoDB会先在二级索引加锁，然后还会在主键索引上加锁

• 如果查询时字段没有索引，进行全表记录加锁

此部分的讲解涉及到索引部分知识，这里读者可以先存疑，待阅读到“索引”小节后再回头来理解。

单行数据加锁原理

接下来以两条SQL的执行为例，讲解一下InnoDB对于单行数据的加锁原理：

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';

第一条SQL使用主键查询，只需要在 id = 49 这个主键索引上加上锁。第二条 SQL 使用二级索引来查询，那么首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加锁。

也就是说使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁。

多行数据加锁原理

根据索引对单行数据进行更新的加锁原理了解了，那如果更新操作涉及多个行呢，比如下面 SQL 的执行场景。

update user set age = 10 where id > 49;

上述 SQL 的执行过程如下图所示。MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁，接着 MySQL Server 发起更新改行记录的 UPDATE 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止。

表锁

​ 上面我们讲解行锁的时候，操作语句中的条件判断列都是有建立索引的，那么如果现在的判断列不存在索引呢？InnoDB既支持行锁，也支持表锁，当没有查询列没有索引时，InnoDB就不会去搞什么行锁了，毕竟行锁一定要有索引，所以它现在搞表锁，把整张表给锁住了。那么具体啥是表锁？还有其他什么情况下也会进行锁表呢？

​ 表锁使用的是一次性锁技术，也就是说，在会话开始的地方使用 lock 命令将后续需要用到的表都加上锁，在表释放前，只能访问这些加锁的表，不能访问其他表，直到最后通过 unlock tables 释放所有表锁。

​ 除了使用 unlock tables 显示释放锁之外，会话持有其他表锁时执行lock table 语句会释放会话之前持有的锁；会话持有其他表锁时执行 start transaction 或者 begin 开启事务时，也会释放之前持有的锁。

​ 表锁由MySQL服务层实现，行锁则是存储引擎实现，不同的引擎实现的不同。在 MySQL 的常用引擎中InnoDB实现了行锁，因此支持行锁和表锁；而 MyISAM 未实现行锁，因此只能使用 MySQL服务层提供的表锁。

页锁

​ 因为InnoDB不支持页锁，因此这里不做讲解。

不同粒度锁的比较

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 一般在执行DDL语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作；
• 存储引擎总是通过一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
• 如果对InnoDB的表使用行锁，被锁定字段不是主键，也没有针对它建立索引的话，那么将会锁整张表；
• 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 最大程度的支持并发，同时也带来了最大的锁开销。
• 在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
• 行级锁只在存储引擎层实现，而MySQL服务器层没有实现。
• 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

锁模式

InnoDB中的行锁

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

• 共享锁(读锁/S锁)：允许事务读取一行数据，兼容读锁但不兼容写锁 — 其他事务可以读取数据但不能修改
• 排他锁(写锁/X锁)：允许获取该排他锁的事务更新或删除一行数据，不兼容读锁和写锁，其它锁均需要等待释放后再获取

​ 所以，当一个事务在更新一行数据时，其它事务就不能对这行数据进行操作，只有等当前事务提交并释放锁之后其它事务才能读取或修改，也就避免了数据库层面的更新丢失问题。基于此机制，即使是读未提交级别也能避免更新丢失问题。通过显式加锁（FOR UPDATE、LOCK IN SHARE MODE）也可以避免脏读问题。

InnoDB表锁——意向锁

​ 由于表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，势必要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突。

意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）:

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先加该表的 IS 锁。

• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先加该表的 IX 锁。

  当事务要在记录上加上行锁时，要首先在表上加上意向锁。这样判断表中是否有记录正在加锁就很简单了，只要看下表上是否有意向锁就行了，从而就能提高效率。

  意向锁之间是不会产生冲突的，它只会阻塞表级读锁或写锁。意向锁不与行级锁发生冲突。

锁模式的兼容情况

（注意:上面的X与S是说表级的X锁和S锁，意向锁不和行级锁发生冲突。）

（如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放。）

InnoDB加锁方法

行锁

1. 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；

2. 对于普通 SELECT 语句，InnoDB 不会加任何锁；
   事务可以通过以下语句显式给记录集加共享锁或排他锁：

   • 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
     其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
   • 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。
     其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

第一点中具体的作用机制请参见 “各种隔离等级的执行机制”小节

• 隐式锁定：

  InnoDB在事务执行过程中，使用两阶段锁协议，即2PL(Two-phase locking)：

  InnoDB会根据隔离级别在需要的时候自动加锁；
  锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

实际上，InnoDB在实际使用的是 S2PL(Strict-2PL)，2PL和S2PL有些区别。对此在意的读者可以在附录中的‘2PL和S2PL的区别’小结中阅读。

严格的两阶段锁协议：

当一个事务获取到了某一个数据库对象的锁之后，并不是当前事务不需要操作它了之后，这个锁就会马上释放掉，这个锁会一直被这个事务持有，直到这个事务被提交或回滚后，这个锁才会被释放掉。所以，在当前事务还没有结束的时候，任何其他事务尝试获取这个锁的时候，都会被阻塞。知道当前事务提交或回滚后，前提事务才可以获取到这把锁。

• 显式锁定 ：

  在sql语句中添上
  select … lock in share mode //共享锁
  select … for update //排他锁

select … for update

在执行这个 select 查询语句的时候，会将对应的索引访问条目加上排他锁（X锁），也就是说这个语句对应的锁就相当于update带来的效果；

​ 使用场景：为了让确保自己查找到的数据一定是最新数据，并且查找到后的数据值允许自己来修改，此时就需要用到select for update语句；

​ 性能分析：select for update语句相当于一个update语句。在业务繁忙的情况下，如果事务没有及时地commit或者rollback可能会造成事务长时间的等待，从而影响数据库的并发使用效率。

select … lock in share mode

in share mode 子句的作用就是将查找的数据加上一个共享锁（S锁），这个就是表示其他的事务只能对这些数据进行简单的 select 操作，而不能进行 DML 操作。

​ 使用场景：为了确保自己查询的数据不会被其他事务正在修改，也就是确保自己查询到的数据是最新的数据，并且不允许其他事务来修改数据。与select for update不同的是，本事务在查找完之后不一定能去更新数据，因为有可能其他事务也对同数据集使用了 in share mode 的方式加上了S锁；

​ 性能分析：select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

表锁

• 意向锁是 InnoDB 自动加的， 不需用户干预。

• 显式锁定

  LOCK TABLES account_info READ;

InnoDB的锁争用情况

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

MySQL> show status like 'InnoDB_row_lock%'; 
+-------------------------------+-------+ 
| Variable_name | Value | 
+-------------------------------+-------+ 
| InnoDB_row_lock_current_waits | 0 | 
| InnoDB_row_lock_time | 0 | 
| InnoDB_row_lock_time_avg | 0 | 
| InnoDB_row_lock_time_max | 0 | 
| InnoDB_row_lock_waits | 0 | 
+-------------------------------+-------+ 
5 rows in set (0.01 sec)

解释：

show status like ‘InnoDB_row_lock%’; 从系统启动到现在的数据

InnoDB_row_lock_current_waits：当前正在等待锁的数量；

InnoDB_row_lock_time ：锁定的总时间长度，单位ms；

InnoDB_row_lock_time_avg ：每次等待所花平均时间；

InnoDB_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；

InnoDB_row_lock_waits ：从系统启动到现在总共等待的次数。

InnoDB行锁的类型

上面我们根据了锁的粒度将锁分为了行锁与表锁，接下来根据使用场景的不同，又可以将行锁进行进一步的划分：’Next-Key Lock’、’Gap Lock’、’Record Lock’以及’插入意向GAP锁’。

不同的锁锁定的位置是不同的，比如说记录锁只锁定对应的记录，而间隙锁锁住记录和记录之间的间隙，Next-key Lock则锁住所属记录之间的间隙。不同的锁类型锁定的范围大致如图所示：

记录锁（Record Lock）

​ 记录锁是最简单的一种行锁形式，上面在讲解InnoDB行锁的时候实际上就是讲解的记录锁。这里补充下的点就是：行锁是加在索引上的，如果当你的查询语句不走索引的话，那么它就会升级到表锁，最终造成效率低下，所以在写SQL语句时需要特别注意。

间隙锁（Gap Lock）

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。

​ 当我们使用范围条件而不是相等条件去检索，并请求锁时，InnoDB就会给符合条件的记录的索引项加上锁；而对于键值在条件范围内但并不存在（空闲块,即由于删除或更新导致的空行）的记录，就叫做间隙，InnoDB在此时也会对间隙加锁，这种记录锁+间隙锁的机制叫Next-Key Lock。额，扯的有点快。

​ 从上面这句话可以表明间隙锁是所在两个存在的索引之间，是一个开区间，像最开始的那张索引图，15和18之间，是有（16，17）这个间隙存在的。

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.

​ 上面这段话表明间隙锁是可以共存的，共享间隙锁与独占间隙锁之间是没有区别的，两者之间并不冲突。其存在的目的都是防止其他事务往间隙中插入新的纪录，故而一个事务所采取的间隙锁是不会去阻止另外一个事务在同一个间隙中加锁的。

当然也不是在什么时候都会去加间隙锁的：

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 隔离级别的话，就会在间隙上加上间隙锁。

临键锁（Next-key Lock）

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.

​ 临键锁是记录锁与与间隙锁的结合，所以临键锁与间隙锁是一个同时存在的概念，并且临键锁是个左开有闭的却比如(16, 18]。

关于临键锁与幻读，官方文档有这么一条说明：

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.

​ 就是说 MySQL 默认隔离级别是RR，在这种级别下，如果你使用 select in share mode 或者 select for update 语句，那么InnoDB会使用临键锁（记录锁 + 间隙锁），因而可以防止幻读；

​ 不过实际上，RR级别下，普通读使用的是MVCC机制，即普通读是快照读，快照读不存在幻读问题。

插入意向锁（Insert Intention Lock）

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.

官方文档已经解释得很清楚了，这里我做个翻译机：

​ 插入意图锁是一种间隙锁，在行执行 INSERT 之前的插入操作设置。如果多个事务 INSERT 到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突。假设有值为4和7的索引记录，现在有两事务分别尝试插入值为 5 和 6 的记录，在获得插入行的排他锁之前，都使用插入意向锁锁住 4 和 7 之间的间隙，但两者之间并不会相互阻塞，因为这两行并不冲突。

​ 插入意向锁只会和 间隙或者 Next-key 锁冲突，正如上面所说，间隙锁作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

不同类型锁之间的兼容

不同类型的锁之间的兼容如下表所示：

（其中行表示已有的锁，列表示意图加上的锁）

其中，第一行表示已有的锁，第一列表示要加的锁。插入意向锁较为特殊，所以我们先对插入意向锁做个总结，如下：

• 插入意向锁不影响其他事务加其他任何锁。也就是说，一个事务已经获取了插入意向锁，对其他事务是没有任何影响的；
• 插入意向锁与间隙锁和 Next-key 锁冲突。也就是说，一个事务想要获取插入意向锁，如果有其他事务已经加了间隙锁或 Next-key 锁，则会阻塞。

其他类型的锁的规则较为简单：

• 间隙锁不和其他锁（不包括插入意向锁）冲突；
• 记录锁和记录锁冲突，Next-key 锁和 Next-key 锁冲突，记录锁和 Next-key 锁冲突；

上文提到的那些锁实际上是InnoDB支持的锁。而MySQL存储引擎并不是只有InnoDB，还有MyISAM等。而MyISAM是不支持行锁的，它使用的是 表独占写锁、表共享读锁。

下面简单讲一下MyISAM的锁，因为日常开发并不是使用的MyISAM，所以不会讲得很详细，而更多的是描述其与InnoDB的区别点。

MyISAM — 表级锁

• 表共享读锁（Table Read Lock）：不会阻塞其他线程对同一个表的读操作请求，但会阻塞其他线程的写操作请求；
• 表独占写锁（Table Write Lock）：一旦表被加上独占写锁，那么无论其他线程是读操作还是写操作，都会被阻塞；

默认情况下，写锁比读锁具有更高的优先级；当一个锁释放后，那么它会优先相应写锁等待队列中的锁请求，然后再是读锁中等待的获取锁的请求。

This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.

​ 这种设定也是MyISAM表不适合于有大量更新操作和查询操作的原因。大量更新操作可能会造成查询操作很难以获取读锁，从而过长的阻塞。同时一些需要长时间运行的查询操作，也会使得线程“饿死”，应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短的时间内完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。）

参考资料：【MySQL】MySQL中的锁机制 - 周二鸭 - 博客园 (cnblogs.com)

讲完了具体的锁，我们就上升一个层级来玩玩高级东西

接下来需要讲述的两种锁不是具体的锁，而是一种锁的思想

乐观锁和悲观锁

乐观锁，乐观并发控制，Optimistic Concurrency Control，缩写“OCC”

悲观锁，悲观并发控制，Pessimistic Concurrency Control，缩写“PCC”

悲观锁需要使用数据库的锁机制来实现，乐观锁是通过程序的手段来实现

乐观锁和悲观锁的澄清

1. 无论是乐观锁（OCC）还是悲观锁（PCC）都不是数据库中具体的锁，他俩只是锁的一种思想。如此我们有了设计的分类，我们就可以按照这种分类对数据库的锁进行划分。
2. 数据库中的乐观锁更倾向于叫 “乐观并发控制”，悲观锁更倾向于叫“悲观并发控制”，还有一种区别于悲观并发控制和乐观并发控制的一种控制叫做 MVCC，“多版本并发控制”
3. 乐观锁和悲观锁是一种思想，而共享锁、排他锁、行锁、表锁等是数据库的具体的锁的实现
4. 乐观锁和悲观锁不仅仅存在于数据库中，任何存在线程存在并发的场景下几乎都有乐观锁和悲观锁的应用场景。比如在java中也有乐观锁和悲观锁的具体实现。只是不同领域的实现不尽相同，解决的问题也可能不一样

所以要是以后有人问你乐观锁和悲观锁是啥，你千万别说它是两种锁，他俩只是一种锁的设计思想，他俩在不同的技术中会有不同的实现方式

悲观锁（Pessimistic Concurrency Control）

​ 悲观锁—即PCC 是指在读取数据的时候总是认为别人会修改它，于是在取数据的时候会对当前数据加一个锁，在结束事务前（提交事务前），不允许其他事务对当前数据进行更改。这样就保证数据的排他性，保证同一时间，只有一个线程能访问到该数据。 通常，悲观锁是利用数据库本身的锁机制去实现的。

​ 数据库中的行锁，表锁，读锁（共享锁），写锁（排他锁），以及 syncronized实现的锁均为悲观锁

​ 悲观锁是可以解决读-写冲突和写-写冲突，通过给数据加锁的方式

工作原理

1. 一个事务要是想访问某条数据，就需要向数据库申请该数据的锁
2. 申请成功了就能操作数据，在操作的过程中其他事务无法访问操作该数据
3. 要是失败了就代表同一时间下，有其他事务正在操作该数据，那就必须等到事务释放锁

优点与缺点

​ 悲观并发控制机制，是一种“先取锁，再操作”的保守策略，为数据操作提供安全保障。但是从效率考虑，处理加锁的机制会让数据库产生额外的开销，还有增加死锁的机会；另外要是是只读型事务的话不会产生冲突，也没必要加锁，加了悲观锁反而会增大系统负载；还有就是降低了并行性，要是一个事务锁住了一行数据，其他事务就只能等着该事务处理完才能进行处理

优点：
适合在写多读少的场景下使用，虽然无法做到很好的并发性，但是在乐观锁无法实现更好的性能的情况下，可以很好地保证数据操作的安全性

缺点：
不适合在读多写少的场景下使用，并发性不好，数据吞吐量低，而且增大系统开销

​

乐观锁（Optimistic Concurrency Control）

​ 乐观锁 — 即OCC 是指在读取数据的时候总会天真的认为没有人会去修改它，在更改操作的时候再去检查冲突。如果冲突了就返回冲突信息并由用户决定下一步如何去做，比如说重试，直到成功为止。数据库的乐观锁并不是由锁机制实现的 而是由某种逻辑实现的

​ 数据库的乐观并发控制是要解决数据库并发场景下的写写冲突，用不加锁的方式解决

工作原理：

1.CAS思想

​ CAS思想是java中的一个思想，数据库的乐观锁实现逻辑原理和CAS思想类似

​ CAS指令全称Computer and swap，是一个系统的指令集，整个CAS指令操作是原子性的，不可分割.具体来说的话就是：

​ CAS会有三个操作数：内存位置V、旧的预期值A、新的修改值B。

​ 当CAS执行操作的时候，先会去内存地址V中比较A的值。要是一致那就用B的值更新内存地址V的值，要是不一致的话就不执行更新，但无论是否更新，都会返回V的旧值

放到代码中理解的话就是：

i = 2;i++;

1. 首先线程1从内存中读到了V地址上的旧值，并存储起来（a = 2）
2. 然后进行i++操作的时候，系统会比较内存地址V的值和旧预期值，即 v值 = ? 2
3. 要是相等，B = i++ = 3，新值就会对内存地址V进行修改，所以内存地址V的值现在变成了3
4. 要是不相等，这就代表已经有其他线程修改过V的值了，比如线程2在线程1之前就更新了i值，所以线程1更新V值就会失败。但线程不会挂起，而是返回失败状态，等待调用线程决定是否重试或者其他操作（重试—重新读取最新数据，再过更新）

数据库的乐观锁实现也类似上面代码层面的实现

当然CAS还只是一种思想，在数据库中由具体的实现方式

2.具体实现

方式一：使用数据版本（version）实现

1. 数据版本就是在表中添加一个字段作为该数据的版本标识，每次对这个数据进行的写操作都会让version值加1

2. 当我们修改数据的时候会 就用CAS思想读取到version值 然后与旧预期值对应，要是一致就进行修改，同时version+1；要是不一致这就意味着在之前就有事务对这个数据进行了修改，就返回冲突信息，让用户自己决定是否重试或者进行其他操作（一般都是重试—重新读取最新数据，然后在执行更新）

   update table set num = num + 1 , version = version + 1 where version = #{version} and id = #{id}

方式二：使用时间戳（timestamp）实现

使用时间戳的原理基本和version一样

1. 在表中添加一个时间戳，在更新提交的时候检查数据的时间戳和自己更新前取得的驱动是否一致，如果一致就代表之前没有事务对这个数据进行修改，于是就可以提交更新，同时更新时间戳致当前时间；不然就代表之前有其他事务在之前就对这个数据进行了修改，就返回冲突信息，等待用户进一步操作

   update table set num = num + 1 ,update_time = unix_timestamp(now()) where id = #{id} and update_time = #{updateTime}

​ 实现CAS要保证CAS多个操作的原子性，即拿到数据库数据的版本，那数据库的数据版本和预期版本比较，以及更新数据这几个操作是必须是连贯执行的，具有符合操作的原子性；要是是sql的话就需要多个sql操作处于同一个事务中

优点和缺点

优点：

1. 在读多写少的情况下，可以避免数据库加锁的开销，提高dao层的响应速度
2. 很多时候，orm（对象关系映射）工具都带有乐观锁的实现，很多时候不需要开发者自己实现

缺点：

1. 在写多读少的情况，在写操作竞争激烈的情况下，会导致CAS指令重试很多次，冲突频率过高，会极大增加开销

乐观锁和悲观锁的抉择

1. 响应速度： 如果Dao层需要很高的并发量，尤其是读多写少的场景下的话就用乐观锁，提高响应速度，降低数据库开销
2. 冲突频率： 要是冲突频率很高，那建议用被悲观锁，保证成功率；因为要是是乐观锁的话， 冲突频率过高会导致事务会需要经常重试，开销太大了
3. 重试代价： 要是重试代价高，比如说重试过程的代码执行很耗时，那不如直接上悲观锁会来得爽快

所以：
要是CAS竞争不高的话，就使用乐观锁，降低数据库开销，还提高数据库并发响应时间
要是是写多读少的场景下，CAS竞争高，且重试代成本高，就直接上悲观锁

参考资料：

理解MySQL的乐观锁，悲观锁与MVCC

全网最全的一篇数据库MVCC详解

—————————————————————————————-施工中————————————————————————————–

死锁

####

—————————————————————————————-施工中————————————————————————————–

小结

1. InnoDB一共支持四种锁，他们分别是 共享锁、排他锁、意向共享锁、意向排他锁。 其中 共享锁和排他锁是行锁，是InnoDB最常用的锁。 而意向共享锁和意向排他锁是表锁，作用是为防止用户在手动加表锁时造成大量的数据遍历，影响性能。
2. InnoDB有两种手动加锁方式：select … for update 和 select … lock in share mode ,他们分别加的是 排他锁 和 共享锁 此两者作用不同，使用场景不同
3. 在没有使用索引的情况下InnoDB就会使用表级锁(共享锁不会有这个情况）

—————————————————————————————-施工中————————————————————————————–

在正式进入MVCC的讲述之前，需要先讲述‘快照读’和‘当前读’的概念

快照读和当前读

MySQL中按照是否使用一致性非锁定读来分为快照读和当前读两个概念：

• 快照读：读取记录数据的’可见版本’或者说’历史版本’，不加锁，普通的SELECT语句都是快照读
• 当前读：读取记录数据的’最新版本’，显式加锁（FOR UPDATE、LOCK IN SHARE MODE）的是当前读，此外，更新操作（INSERT、UPDATE、DELETE）也是当前读，会加锁

在读已提交和可重复读两种事务隔离级别下，普通的SELECT操作使用“快照读”，不会对数据加锁，也不会被事务阻塞。

在读已提交和可重复读两种事务隔离级别下，使用“当前读”的操作包括：

• SELECT LOCK IN SHARE MODE (共享锁)
• SELECT FOR UPDATE （排他锁）
• DELETE（排他锁）
• UPDATE （排他锁）
• INSERT INTO（排他锁）
• REPLACE INTO（排他锁）
• 串行化事务隔离级别

MVCC

定义

​ MVCC 即Multi-Version Concurrency Control – 基于版本的控制协议

​ 它是理想模型（和MVC一样的理想模型）它具体指的是“维持一个数据的多个版本，使得读写操作没有冲突”，通过读取历史数据而对当前数据进行写入使得读写无冲突（因为读写操作对象不一样了嘛） （这里的历史也只是一个事务执行前的那个时间点而已 就几秒钟不到）

​ 一般在数据库管理系统中实现对数据库的并发访问，在编程语言中实现事务内存

​ MVCC在MySQL中的中实现是为了提高数据库并发性能，用更好的方式解决读-写冲突，即使有读-写冲突也能做到不加锁非阻塞读

​ InnoDB中同一行数据发生读写的时候都会加上锁阻塞住（行锁），如果一张表又读又写 对20行数据同时操作就会有40把锁，对40行数据操作就会有80行锁，对80行数据操作就会有160把锁，这种方式对于不需要太过保证安全但是并发量极大的情况来说实在太过繁琐，所以有了MVCC

实现

MVCC的实现思路是乐观锁的第二种实现方式

具体来说是为每个事务加上单向增长的时间戳，为每个修改保存一个版本，版本号和时间戳关联，读操作只读取该事务开始的那个时刻的数据库快照

MVCC的好处

​ MVCC是一群大牛不满意只能用悲观锁来解决读写冲突而想出来的一种开销更小更轻的来解决读写冲突的一种解决方案或者说是一种思想

MVCC可以使得

1. 在并行数据库操作的时候，可以做到在 读操作的时候不需要阻塞写操作，在写操作的时候不需要阻塞读操作，提高并行读写性能
2. 还可以解决脏读、幻读、不可重复读的问题 但是不能解决更新丢失的问题

我们可以使用MVCC和乐观锁悲观锁组合的方式解决问题：

• MVCC + 悲观锁
  MVCC解决读写冲突，悲观锁解决写写冲突
• MVCC + 乐观锁
  MVCC解决读写冲突，乐观锁解决读写冲突

这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突和写写冲突导致的问题

当前读、快照读和MVCC的关系

​ MVCC是理想模型（和MVC一样的理想模型）它具体指的是“维持一个数据的多个版本，使得读写操作没有冲突”。这个概念需要具体功能去实现 ，在数据库中，这个具体实现就是快照读。

​ 再细致一点的话，快照读也是一个抽象概念，MVCC具体是由3个隐藏字段、undo log和read view实现的。

MVCC的具体实现：

主要通过版本链、undo日志、 Read view来实现

1.版本链

我们数据库中的每行数据，除了我们看到的每一行数据之外，还有三个隐藏字段

db_trx_id、db_roll_pointer、db_row_id

• db_trx_id:

     6byte，最近修改(修改/插入)事务ID：记录创建这条数据/最后一次修改该数据的事务ID。

• db_roll_pointer:
  7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

• db_row_id:
  6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以db_row_id产生一个聚簇索引。

实际还有一个删除flag隐藏字段, 记录被更新或删除并不代表真的删除，而是删除flag变了

​ 如上图，db_row_id是数据库默认为该行记录生成的唯一隐式主键，db_trx_id是当前操作该记录的事务ID，而db_roll_pointer是一个回滚指针，用于配合undo日志，指向上一个旧版本。

​ 每次对数据库记录进行改动，都会记录一条undo日志 ，每条undo日志也都有一个roll_pointer 属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

​ 对该记录每次更新后，都会将旧值放到一条undo日志 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer 属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id，这个信息很重要，在根据ReadView判断版本可见性的时候会用到。

2.undo log

​ Undo log主要用于记录数据被修改之前的日志，在表信息修改之前先会把数据拷贝到undo log里.
当事务进行回滚时可以通过undo log里的日志进行数据还原。

用途：

1. 保证事务在回滚时的原子性和一致性， 事务回滚的时候用的undo log的数据进行的恢复

2. 用于MVCC的快照的数据，通过读取undo log中的历史数据可以实现不同事务版本号都有自己独立的快照数据版本

分类：

• insert undo log

  ​ 代表事务在insert新纪录下产生的undo log，只供事务回滚时使用，并且在事务提交后可以被立即丢弃

• update undo log（主要）

  ​ 事务在进行update或delete时产生的undo log ; 不仅在事务回滚时需要，在快照读时也需要；

  ​ 所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

3.Read View

​ 事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照。

​ 记录并维护系统当前活跃事务的ID(没有commit，当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以越新的事务，ID值越大)，是系统中当前不应该被本事务看到的其他事务id列表。

​ Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log 里面的某个版本的数据。

​ 对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了；对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的。核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。为此，设计InnoDB的大叔提出了一个ReadView的概念。

Read View中的属性

• trx_ids：表示在生成ReadView时，当前系统中活跃的读写事务的事务版本号集合

• low_limit_id：表示生成当前read view 时，“当前系统最大事务版本号+1”，即下一个将被分配的事务ID

• up_limit_id：表示生成ReadView时，活跃事务列表trx_ids中最小的事务ID。如果trx_ids为空，则up_limit_id 为 low_limit_id

• creator_trx_id：表示生成该ReadView的事务的事务id

源码指路：github.com/facebook/mysql-5.6/blob...

注意：

• trx_ids 集合值可能不是连续的，比如是[1, 2, 4]，这时3号事务已经提交
• low_limit_id 指的是‘当前系统最大事务版本号’，比如说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，trx_ids 就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4。
• 只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。

Read View可见性判断条件

• db_trx_id< up_limit_id || db_trx_id == creator_trx_id （显示）

  ​ 如果数据事务ID小于read view中的最小活跃事务ID，则可以肯定该数据是在当前事务启之前就已经存在了的,所以可以显示。

  ​ 或者数据的事务ID等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。

• db_trx_id >= low_limit_id （不显示）

  ​ 如果数据事务ID大于read view 中的当前系统的最大事务ID，则说明该数据是在当前read view 创建之后才产生的，所以数据不显示。如果小于则进入下一个判断

• db_trx_id是否在活跃事务（trx_ids ）中

  • 不存在：则说明read view产生的时候事务已经commit了，这种情况数据则可以显示。

  • 已存在：则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的。

​ 如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到上一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的第一个版本。如果第一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

​ 在MySQL中，READ COMMITTED和REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成Read View 的时机不同:

• RC隔离级别下，是每个快照读都会生成并获取最新的Read View；
• 而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View，之后的查询就不会重复生成了，所以一个事务的查询结果每次都是一样的。

参考资料：blog.csdn.net/waves___/article/det...

实践：

常规操作

截断表

truncate table 表名；

级联操作设置

级联操作
-- 1. 添加级联操作
    语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
        FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) 
        ON UPDATE CASCADE 
        ON DELETE CASCADE;
-- 2. 分类：
    1. 级联更新：ON UPDATE CASCADE
    2. 级联删除：ON DELETE CASCAD

Mysql锁实践

在进行实操前，请先明晰一下几件事：

1. SQLyog中多个窗体（多个连接）彼此之间是互相独立的，相当于有多个用户在同时操作数据库

2. 每个窗口间的一些属性是不一样的。比如说 autocommit, 如果使用SHOW VARIABLES LIKE 'autocommit'; 只能看到本连接的自动提交事务情况。SHOW GLOBAL VARIABLES LIKE 'autocommit';可以看到全局的自动提交事务情况。

3. 部分属性在重新开启SQLyog后会自动还原成默认值。比如说隔离等级 、autocommit。

常用指令

#查看mysql现已提供的存储引擎
show engines;

# 查看当前表的构建语句  engine参数后显示存储引擎
show create table account_info;

# 查看下autocommit的值
    SHOW VARIABLES LIKE 'autocommit';
    或
    SELECT @@autocommit;
    或
    SHOW GLOBAL VARIABLES LIKE 'autocommit';   //

# 设置autocommit值
SET autocommit = 0|1|ON|OFF;  //只能修改单个连接

# 设置事务隔离等级 -- 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;   //设置 可重复读

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  //设置 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;    //设置 读已提交

# 查看当前会话的隔离级别：
select @@tx_isolation;  或  SELECT @@session.tx_isolation;

#查看全局的隔离级别
SELECT @@global.tx_isolation;

提示：在MySQL 8.0.3 中，tx_isolation 变量被 transaction_isolation 变量替换了。在 MySQL 8.0.3 版本中查询事务隔离级别，只要把上述查询语句中的 tx_isolation 变量替换成 transaction_isolation 变量即可。


#当前运行的所有事务
select * from information_schema.InnoDB_trx;  
    # 在执行begin和一条sql之后你才能在这里看到对应的事务

#当前正在等待的锁  注意 应该是正在等待而非现在出现
select * from information_schema.InnoDB_locks;     

#锁等待的对应关系
select * from information_schema.InnoDB_lock_waits 

#查看锁的情况
mysql> show status like 'InnoDB_row_lock_%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| InnoDB_row_lock_current_waits | 1      |
| InnoDB_row_lock_time          | 479764 |
| InnoDB_row_lock_time_avg      | 39980  |
| InnoDB_row_lock_time_max      | 51021  |
| InnoDB_row_lock_waits         | 12     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

解释如下：
InnoDB_row_lock_current_waits : 当前等待锁的数量
InnoDB_row_lock_time : 系统启动到现在，锁定的总时间长度
InnoDB_row_lock_time_avg : 每次平均锁定的时间
InnoDB_row_lock_time_max : 最长一次锁定时间
InnoDB_row_lock_waits : 系统启动到现在总共锁定的次数  



# 查询是否锁表
mysql> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | tx1   |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

# 查看先当前库的线程情况：
show full processlist;

# kill 进程
kill 1825;

# 查看表是什么时候更新的
SELECT * FROM information_schema.tables WHERE table_schema='test1' AND table_name='rpt_test' ;

#整个数据库备份
mysqldump -u root -p --databases wxss  > C:\Users\Administrator\Desktop\sql\wxss_database.sql

基本操作

入门

接下来会为读者演示在SQLyog中实现死锁

1. 执行SHOW VARIABLES LIKE 'autocommit';查看autocommit值是否是off。如果不是则执行SET autocommit = OFF 关闭MySQL自动提交事务

2. 双开sqlyog窗口

   session_1	session_2
   set autocommit = 0; – 关闭自动事务提交	set autocommit = 0; – 关闭自动事务提交
   正常处理： SELECT account_id, account_nickname FROM account_info WHERE account_id = 3	正常处理： select actor_id,first_name,last_name from actor where actor_id = 178;
   给account_id = 3的记录加share mode的共享锁 select account_id, account_nickname from account_info where account_id = 3 lock in share mode;
   	session_2 仍可以查询数据，也可以对该记录加share mode的共享锁 SELECT account_id, account_nickname FROM account_info WHERE account_id = 3 LOCK IN SHARE MODE;
   UPDATE account_info SET account_nickname = “观星先生” WHERE account_id = 3; 等待 — 在SQLyog的表现是 一直处于查询状态 查询编辑器上边在转圈圈 此时 SELECT * FROM information_schema.InnoDB_locks; 结果会出现X锁和S锁
   	此时 session_2 也对该记录进行更新操作，则会导致 死锁退出： UPDATE account_info SET account_nickname = “观星先生” WHERE account_id = 3; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

   PS:

   需要读者明晰以下几点：

   1. 在READ UNCOMMITTED下，本事务操作不会被自己的锁锁住。比方说 session_1 给 account_id = 3数据添加共享锁后执行update account_info set account_nickname = "观星先生" where account_id = 3; 可以正常执行，无需等待。
   2. 现在解释以上示例中死锁出现原因：
      • 
      • session_1 和 session_2 分别给同一数据添加 共享锁 和 排他锁 —— 排他锁 session_1 被共享锁 session_2阻塞；排他锁 session_2被共享锁 session_1阻塞 彼此阻塞无法执行
      • 正常死锁会自动释放，InnoDB有一个内在的死锁检测工具，当死锁超过一定时间后，会回滚其中一个事务，InnoDB_lock_wait_timeout可配置死锁等待超时时间。

基本概念

autocommit值

在 MySQL 中，可以使用 SET autocommit 语句设置事务的自动提交模式，语法格式如下：

SET autocommit = 0|1|ON|OFF;

对取值的说明：

• 值为 0 和值为 OFF：关闭事务自动提交。如果关闭自动提交，用户将会一直处于某个事务中，只有提交或回滚后才会结束当前事务，重新开始一个新事务。
• 值为 1 和值为 ON：开启事务自动提交。如果开启自动提交，则每执行一条 SQL 语句，事务都会提交一次。

其他指令

# 修改表引擎方法
alter table table_name engine=InnoDB;

# 关闭InnoDB引擎方法
关闭mysql服务： net stop mysql
找到mysql安装目录下的my.ini文件：
找到default-storage-engine=InnoDB 改为default-storage-engine=MYISAM
找到#skip-InnoDB 改为skip-InnoDB
启动mysql服务：net start mysql

InnoDB Monitor(InnoDB监视器)

参考资料：InnoDB Monitor(InnoDB监视器)

尾注

尾注I: 并发量

尾注II: Purge线程

参考资料： MySQL Innodb Purge简介

附录

2PL和S2PL区别

什么是2PL：

​ 2PL即‘两阶段加锁协议’

​ 整个事务分为两个阶段，前一个阶段加锁，称为扩展阶段，后一个阶段解锁，称为收缩阶段。在扩展阶段，事务只能加锁，也可以操作数据，但不能解锁，直到事务释放第一个锁，就进入收缩阶段，此过程中事务只能解锁，也可以操作数据，不能再加锁。两阶段锁协议使得事务具有较高的并发度，因为解锁不必发生在事务结尾。它的不足是没有解决死锁的问题，因为它在加锁阶段没有顺序要求。如两个事务分别申请了A, B锁，接着又申请对方的锁，此时进入死锁状态。

​ 一句话解释即 ‘在一个事务里面，分为加锁(lock)阶段和解锁(unlock)阶段,也即所有的lock操作都在unlock操作之前。’

如下图所示:

为什么需要两阶段加锁

引入2PL是为了保证事务的隔离性，即多个事务在并发的情况下等同于串行的执行。

工程实践中的两阶段加锁-S2PL

​ 在实际情况下，SQL是千变万化、条数不定的,数据库很难在事务中判定什么是加锁阶段，什么是解锁阶段。于是引入了S2PL(Strict-2PL)。

​ 当一个事务获取到了某一个数据库对象的锁之后，并不是当前事务不需要操作它了之后，这个锁就会马上释放掉，这个锁会一直被这个事务持有，直到这个事务被提交或回滚后，这个锁才会被释放掉。所以，在当前事务还没有结束的时候，任何其他事务尝试获取这个锁的时候，都会被阻塞。知道当前事务提交或回滚后，前提事务才可以获取到这把锁。

一句话解释，即‘ 在事务中只有提交(commit)或者回滚(rollback)时才是解锁阶段， 其余时间为加锁阶段。’

如下图所示:

这样的话，在实际的数据库中就很容易实现了。

参考资料：完整的剖析 MySQL 两阶段加锁（2PL）协议

2PC

即 二阶段提交协议（Two-phase Commit）

参考资料

【MySQL】MySQL事务一致性理解

不可重复读和幻读的区别是什么-MySQL教程-PHP中文网

【MySQL】MySQL中的锁机制 - 周二鸭 - 博客园 (cnblogs.com)

理解MySQL的乐观锁，悲观锁与MVCC

全网最全的一篇数据库MVCC详解

完整的剖析 MySQL 两阶段加锁（2PL）协议

MySQL Innodb Purge简介

本作品采用《CC 协议》，转载必须注明作者和本文链接