MySQL高级（五）--数据库中的各种锁，主从复制

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

不可重复读的重点是修改：
比如：在事务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.当我们提交更新的时候，对比当前数据库内的Version是否与我们第一次取出的Version一致，如果一致，存入，不一致，那就重新操作。
2.使用时间戳：在数据库表中加入一个字段，字段类型是“timestamp”，也是在更新的时候检查当前数据库中数据的时间戳和自己更新前读到的时间戳一致否，一致更新，不一致GG。

2.锁按级别划分–共享锁（读锁）和排他锁（写锁）

共享锁：S锁也叫读锁，用于所有的只读数据操作，共享锁是非独占的，允许多个并发事务读取其锁定的资源。
性质：多个事务可封锁同一个共享页；但是任何事物都不能修改该页。
排他锁：X锁也叫写锁，用于对数据进行写操作，如果一个事务对对象加了排他锁，其他事务就不能再给他加任何锁。
性质：仅允许一个事务封锁此页，其他事务只能等待写锁释放才能对该页进行访问。
共享锁会阻塞写，不会阻塞读。排他锁写和读都被阻塞。

3.锁按粒度分为–表锁和行锁

1.InnoDB行锁是通过索引上的索引项加锁实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
行锁的加锁方式：自动加锁、对于update、delete、insert语句，InnoDB会自动给设计数据集加排他锁，对于普通的select语句，InnoDB不会加任何锁，也可以显示加锁。
行锁使用的注意事项：
尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定。
合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，减少锁定范围。
尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
尽量控制事物的大小，减少锁定的资源量和锁定时间长度。
2.MyISAM默认的是表锁，对MyISAM表的读操作，不会阻塞其他用户对同意表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会堵塞其他用户对同一表的读写操作。
加锁方式：自动加锁。查询操作会自动给设计的所有表加读锁，更新操作会自动给设计的表加写锁，也可以显示加锁。
行锁和表锁的优劣势：
行锁：
优势：锁的粒度小，发生锁冲突的概率低，处理并发能力强。
劣势：开销大，加锁慢，会出现死锁。
表锁：
优势：开销小，加锁快，无死锁。
劣势：锁粒度大，发生锁冲突的概率高，处理并发能力低。

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次死锁检测。事务的数量再上升的时候，检测又会上一个级别，如何解决这个问题：
1.合理规划数据表的执行顺序，避免多个事务以不同顺序更新同一个表。相同顺序访问，是不会发生死锁的。
2.控制并发量，在代码中限制同时执行事务的数量，控制在10以内，超出的排队窒息感，这样可以减少死锁检测次数。
程序员排查方法：
1.通过应用业务日志定位到问题代码，找到相应事务对应Sql。因为死锁被检测后会回滚，这些信息都是会以异常反应再应用的业务日志 中，通过这些日志我们可以定位到相应的代码，并把事务的sql给梳理出来。
2.找DBA执行下 show InnoDB STATUS看看最近死锁的日志。

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

1.合理规划表的执行顺序，避免多个事务以不同顺序更新一个表。
2.避免并发地执行涉及修改数据的语句。
3.每个事务执行的时间不可太长，在业务允许的情况下可以考虑将事务分割成几个小事务来执行。
4.要求每个事务一次就要将要使用的数据全部加锁，否则就不执行。

7.MVCC多版本并发控制机制

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

1.MVCC是为了解决什么？

在MySQL中，MyISAM使用的是表锁，InnoDB使用的是行锁。而InnoDB的事务分为四个隔离级别，其中默认的隔离级别 repeatable read需要两个不同的事务之间不能影响，而且还支持并发，这点悲观锁是达不到的，所以repeatable read采用的是乐观锁，而乐观锁的实现采用的就是MVCC。

2.MVCC原理

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

3.MVCC特征

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

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 logrecord指针，大小为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具体操作流程

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

三、MySQL主从复制原理

1.一主一从

1.主服务器上面的任何修改都会通过自己的IO线程保存在二进制文件中（binary log）。
2.从机上面也启动一个IO线程，通过配置好的用户名和密码，连接到主服务器上请求读取二进制日志，然后把读取到的二进制日志写到本地的一个中继日志（Relay log）。
3.从服务器上面通过会开启一个SQL thread 定时检查relay log，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

2.一主多从

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