MySQL高级（五）——（各种读问题，数据库中的各种锁，MVCC，主从复制）

一、不可重复读和幻读的区别？

这两者的意思非常相近，但是又有点区别，一些刁钻的面试官就喜欢问这种问题。

• 不可重复读的重点是修改。
  例如：在事务1中，A读取了自己的工资是1000；在事务2中，财务人员修改了A的工资为2000，并提交事务；在事务1中，A再次读取到自己的工资时，变为了2000。
  在一个事务的前后两次读取的结果并不一致，导致了不可重复度。

• 幻读的重点在于新增或者删除。
  例如：事务1读取所有工资为1000的的员工共有10人；事务2向员工表中插入了一条员工记录，工资也是1000；事务1再次读取所有工资为1000的员工，共读到了11条记录，这就产生了额幻读。

二、MySQL数据库中的各种锁

1、锁按使用方式划分—$悲观锁和乐观锁$$概念$

• 悲观锁：对数据的冲突采取一种悲观的态度，无论是在数据读取，还是数据修改的过程都需要加锁。
• 乐观锁：在数据读取时不会加锁，在数据修改时才会加锁。

$适用场景$

• 悲观锁：比较适合写入操作比较频繁的场景，如果出现大量的读取操作，每次读取的时候都会进行加锁，这样会增加大量的锁的开销，降低了系统的吞吐量。
• 乐观锁：比较适合读取操作比较频繁的场景，如果出现大量的写入操作，数据发生冲突的可能性就会增大，为了保证数据的一致性，应用层需要不断的重新获取数据，这样会增加大量的查询操作，降低了系统的吞吐量。

$数据库中常见悲观锁和乐观锁$

• 悲观锁：表锁、行锁、读锁、写锁。
• 乐观锁：不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性，乐观锁的实现方式主要有两种：
  （1） 使用版本号：使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。就是给数据表增加一个数字类型的“version”字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，version加1。当我们提交更新的时候，判断当前版本的值与第一次取出来的版本值是否一致，如果一致，则更新，否则拒绝更新，让用户重新操作。
  （2）使用时间戳：和版本号差不多，同样是在数据库表中增加一个字段，字段类型使用时间戳（timestamp），也是在更新的时候检查当前数据库中数据的时间戳和自己更新前读取到的时间戳进行对比，如果一致，就更新。

version方式：
一般在数据表中加一个version版本字段,表示数据被修改的版本次数，当数据被修改时，version会被加一。当线程A读取数据时也要同时读取version值，在提交更新的时候，如果刚才读取的version值和当前数据库里的version值一致，那么才能更新，否则重新更新操作，直到更新成成功

 //查询商品的名称，状态以及该信息的版本号字段
 select (name,status,version) from t_goods where id =#{id}
 //修改商品中状态为2
 update t_goods set status =2 .version =version+1 where id =#{id} and version =#{version}

2、锁按级别划分—$共享锁（读锁）和排他锁（写锁）$

$共享锁$

共享锁（Share Lock），S锁，也叫读锁，用于所有的只读数据操作，共享锁是非独占的，允许多个并发事务读取其锁定的资源。

性质：

• 多个事务可封锁同一个共享页；
• 但是任何事务都不能修改该页；

$排他锁$

排他锁（Exclusive Lock），X锁，也叫写锁，用于对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。

性质：

• 仅允许一个事务封锁此页，其他事务只能等待写锁释放才能对该页进行访问。

共享锁会阻塞写，不会阻塞读。
排它锁写和读都被阻塞。

3、锁按粒度划分—$表锁和行锁$

（1）InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，Innodb才使用行级锁，否则，InnoDB将使用表锁。

行锁加锁方式：自动加锁，对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁，也可以显式加锁。

行锁使用的注意事项：

• 尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定。
• 合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，减小锁定范围。
• 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
• 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。

（2）MyISAM默认的就是表锁，对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyIASM表的写操作，则会堵塞其他用户对同一表的读和写操作。

加锁方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE,DELETE,INSERT）会自动给涉及的表加写锁，也可以显式加锁。

$行锁和表锁的优劣势$

行锁：

• 优势：开销大，加锁慢，会出现死锁
• 劣势：锁的粒度小，发生锁冲突的概率低，处理并发的能力强。

表锁：

• 优势：开销小，加锁快，无死锁
• 劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低。

4、数据库中的死锁问题

死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时系统处于死锁状态。

在事务中，多个事务同时对不同的行加锁，可能会导致死锁，例如如下场景：
注意：行锁某个事务中当某行数据修改后，该事务提交后，锁才能释放。

上图死锁的发生过程：

• 1、事务1先修改id=3的用户的年龄并锁住该行，然后事务2修改id=2的年龄锁住该行。
• 2、接着事务1修改id=2的用户年龄，因为这一行已经被事务2锁住了，所以这条语句会堵塞，要等到事务2释放锁才能继续。
• 3、此时事务2想修改id=3的用户的年龄，刚好这行又被事务1给锁住了。两个事务互相等待都要等待对方先释放锁，产生了死锁。

5、死锁问题的解决和排查

$MySQL中默认的方法$

• 1、等待锁超时，当更新语句等待锁一段时间后会超时退出，不会无休止等待下去，但是这个超时时间默认是50s，高并发系统中是无法接受的。
• 2、设置死锁检测：通过设置innodb_deadlock_detect = on，开启MySQL死锁检测，系统自动检测死锁的事务并回滚改动。

大部分使用的都是方法2：但是方法2的缺点是检测的时间复杂度是O(n^2)，例如有100个事务在执行的时候，每个事务执行的时候都要和另外的99个线程检测是否存在死锁，此时就需要执行10000次死锁检测。事务的数量再上升的时候，死锁的检测又会上升一个量级。如何解决这个问题呢？

• 合理规划数据表的执行顺序，尽量避免多个事务以不同顺序更新同一个表。相同顺序访问，是不会发生死锁问题的。
• 控制并发量，在代码中限制同时执行事务的数量，控制在10以内，超出的排队执行，这样就减少了死锁的检测次数。

$程序员排查办法$

• 1、通过应用业务日志定位到问题代码，找到相应的事务对应的sql。因为死锁被检测后会回滚，这些信息都会以异常反应在应用的业务日志中，通过这些日志我们可以定位到相应的代码，并把事务的sql给梳理出来。
• 2、找DBA执行下show InnoDB STATUS看看最近死锁的日志。

6、平时使用时如何避免死锁的发生？

（1）合理规划表的执行顺序，避免多个事务以不同顺序更新一个表。

（2）避免并发地执行涉及修改数据的语句。

（3）每个事务的执行时间不可太长，在业务允许的情况下可以考虑将事务分割成几个小事务来执行。

（4）要求每个事务一次就将所有要使用的数据全部加锁，否则就不执行。

三、MVCC多版本并发控制机制

MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

（1）MVCC是为了解决什么？

众所众知，在MySQL中，MyIASM使用的是表锁，InnoDB使用的是行锁。而InnoDB的事务分为四个隔离级别，其中默认的隔离级别REPEATABLE READ需要两个不同的事务相互之间不能影响，而且还支持并发，这点悲观锁是达不到的，所以REPEATABLE READ采用的就是乐观锁，而乐观锁的实现采用的就是MVCC，正是因为有了MVCC，才造就了InnoDB强大的事务处理能力。

（2）MVCC原理

MVCC的实现，通过保存在数据在某个时间点的快照来实现的。
在每行记录后面保存两个隐藏的列，一列保存了行的创建时间，另一列保存了行的过期时间（删除时间），这里存储的时间并不是实际的时间值，而是系统版本号，每开始一个新事物，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来与查询到的每行记录的版本号进行比较。

（3）MVCC特征

• 每行数据都存在一个版本，每次数据更新时都更新该版本。
• 修改时copy出当前版本进行修改，各个事务之间互不干扰。
• 保存时比较版本号，如果成功（commit），则覆盖原记录，失败则放弃copy（rollback回滚）。

（4）MVCC实现

1、redo log：重做日志，确保事务的持久性，防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性。重做日志由两部分组成，一是内存中的重做日志缓冲区，因为重做日志缓冲区在内存中，所以她是易失的；另一个就是在磁盘上的重做日志文件，它是持久的。
2、undo log：回滚日志，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读在MySQL中，恢复机制是通过回滚日志实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。
3、bin log：二进制日志，用于复制，在主从复制中，从库利用主库上的bin log进行重播，实现主从同步。

实现

在每一行数据中额外保存两个隐藏的列：

• DATA_TRX_ID ：记录最近一次修改（insert/update）本行记录的事务id，大小为6字节。
• DATA_ROLL_PTR：指向该行回滚段（rollback segment）的undo log record指针，大小为7字节。如果这一行记录被更新，则undo log record包含" 重建该行记录被更新之前内容"所必须的信息。InnoDB便是通过这个指针找到之前版本的数据，若改行记录上存储所有的旧版本，在undo中都通过链表的形式组织。

如果表没有主键，则还会有一个隐藏的主键列DB_ROW_ID。

• DB_ROW_ID:行标识（隐藏单调自增ID），大小为6字节，如果表没有主键，InnoDB会自动生成一个隐藏主键。

例如：

• 事务1执行新增一条数据insert操作：
  此时：DB_ROW_ID=1，DATA_TRX_ID=1（系统功版本号），DATA_ROLL_PTR=NULL。
• 事务2执行update操作：
  （1）对DB_ROW_ID=1这行记录加排它锁，把这行copy前的值拷贝到undo log中。
  （2）修改该行的值，这是会产生一个新版本号，更新DATA_TRX_ID为修改记录的事务ID。
  （3）将DATA_ROLL_PTR指向刚刚copy到undo log链中的旧版本记录，这样就能通过DATA_ROLL_PTR找到这条记录的历史版本；如果对同一行执行连续的UPDATE，undo log会组成一个链表，遍历这个链表就可以看到这条记录的变迁。
  （4）记录redo log，包括undo log中的修改。

（5）可重复读隔离级别下，MVCC具体的操作流程

• SELECT：InnoDB只查找版本早于当前事务版本的数据行；行的删除版本，要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。
• INSERT：InnoDB为插入的每一行保存当前系统版本号作为行版本号。
• DELETE：InnoDB为删除的每一行保存当前系统版本号作为删除标识，标记为删除、而不是实际删除。
• UPDATE： InnoDB会把原来的行复制一份到回滚段中，保存当前系统版本号作为行版本号，同时，保存当前系统版本号到原来的行作为删除标识。

四、MySQL主从复制原理

1、一主一从

（1）主服务器上面的任何修改都会通过自己的IO线程保存在二进制日志文件中（binary log）。

（2）从机上面也启动一个IO线程，通过配置好的用户名和密码，连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个中继日志里面（Relay log）。

（3）从服务器上面同时开启一个SQL thread定时检查Realy log（二进制），如果发现有更新立即把更新的内容在本机的数据库在上面执行一遍。

2、一主多从

如果一主多从的话，这时主机要负责写又要负责为几个从库机提供二进制日志，此时可以稍作调整，将二进制日志只给某一从机，这一从机再将自己的二进制日志再发给其他从机。或者是干脆这个从机不记录只负责将二进制日志转发给其他从机，这样架构起来性能可能要好得多，而且数据之间的延时应该也稍微要好一些。

五、mysql是怎么解决幻读的？

一、什么是幻读

在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做幻行。

二、为什么要解决幻读

在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。

三、MySQL 是如何解决幻读的

如果你看到了这篇文章，那么我会默认你了解了脏读 、不可重复读与可重复读。

多版本并发控制（MVCC）（快照读/一致性读）

多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。以 InnoDB 为例，每一行中都冗余了两个字断。

一个是行的创建版本，一个是行的删除（过期）版本。具体的版本号（trx_id）存在 information_schema.INNODB_TRX 表中。版本号（trx_id）随着每次事务的开启自增。

事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。

普通的 select 就是快照读。

select * from T where number = 1;

原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。

next-key 锁 （当前读）

next-key 锁包含两部分：

记录锁（行锁）

间隙锁

记录锁是加在索引上的锁，间隙锁是加在索引之间的。（思考：如果列上没有索引会发生什么？）

select * from T where number = 1 for update;

select * from T where number = 1 lock in share mode;

insert

update

delete

原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。

其他：MySQL InnoDB 引擎 RR 隔离级别是否解决了幻读

引用一个 github 上面的评论 地址：

Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。

a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。

如果这样理解的话，Mysql的RR级别确实防不住幻读

有道友回复 地址：

在快照读读情况下，mysql通过mvcc来避免幻读。
在当前读读情况下，mysql通过next-key来避免幻读。
select * from t where a=1;属于快照读
select * from t where a=1 lock in share mode;属于当前读

不能把快照读和当前读得到的结果不一样这种情况认为是幻读，这是两种不同的使用。所以我认为mysql的rr级别是解决了幻读的。

先说结论，MySQL 存储引擎 InnoDB 隔离级别 RR 解决了幻读问题。面试问烂的 MySQL 四种隔离级别，这篇文章建议大家看下。

如引用一问题所说，T1 select 之后 update，会将 T2 中 insert 的数据一起更新，那么认为多出来一行，所以防不住幻读。看着说法无懈可击，但是其实是错误的，InnoDB 中设置了快照读和当前读两种模式，如果只有快照读，那么自然没有幻读问题，但是如果将语句提升到当前读，那么 T1 在 select 的时候需要用如下语法： select * from t for update (lock in share mode) 进入当前读，那么自然没有 T2 可以插入数据这一回事儿了。

注意

next-key 固然很好的解决了幻读问题，但是还是遵循一般的定律，隔离级别越高，并发越低。