MySQL知识点总结（五）主从复制，MySQL中的锁 与 MVCC

4. 主从复制

• 复制的基本原理

1. master 将改变记录到二进制日志（binary log）
2. slave 将 master 的二进制日志拷贝到他的中继日志（relay log）
3. slave 重做中继日志中的时间，将改变应用到自己的数据库中。MySQL的主从复制是异步且串行化的（最大问题：存在延时）

• 主从复制的基本原则

  每个slave只能有一个master，每个master可以有多个slave

5. MySQL中的锁

5.1 从对数据操作类型分

共享锁/读锁

SELECT * FROM `test` WHERE `id` = 1 LOCK IN SHARE MODE;

1. 多个事务的查询语句可以共用一把共享锁；
2. 如果只有一个事务拿到了共享锁，则该事务可以对数据进行 UPDATE DETELE 等操作；
3. 如果有多个事务拿到了共享锁，则所有事务都不能对数据进行 UPDATE DETELE 等操作。

排他锁/写锁

SELECT * FROM `test` WHERE `id` = 1 FOR UPDATE;

1. 只有一个事务能获取该数据的排它锁；
2. 一旦有一个事务获取了该数据的排它锁之后，其余事务对于该数据的操作将会被阻塞，直至锁释放。

自增锁

如果表中存在自增字段，MySQL便会自动维护一个自增锁

为什么主键设置成自增

主键上设置自增属性，可以保证每次插入都是插入到最后面，可以有效的减少索引页的分裂和数据的移动。

自增锁会导致出现幻读的情况（某个事务中途插入后会导致在本事务中主键不连续）

5.2从操作粒度分

行锁/记录锁（Record Locks） SELECT * FROM test WHERE id=1 FOR UPDATE; 锁住一行记录

• 操作未使用索引，行锁会升级为表锁

• 间隙锁（Gap Lock）

  1. 间隙锁只有在事务隔离级别 RR 中才会产生；
  2. 唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
  3. 普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
  4. 间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现幻读现象；

表锁 偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低

5.3 数据库是如何加锁的

大体的举个例子如下：

一个sql ：select * from user where id=1;

数据库收到sql后会，判断id是不是索引：如果是索引，数据库则就行进行检索索引，对这条索引进行加锁，加锁加的是行锁；如果Id不是索引，则加的是表锁。

如果加的是行锁，会判断根据sql，添加不同的类型的行锁：如果是查询语句，所加的共享锁（S锁，读锁）；如果是增删改语句，则加的是排他锁。

如果加的表锁，则会根据加的是表锁，则会判断表是否存在其他的锁。如果存在锁且是S锁，则可以进行加表锁。如果加的是X锁，则无法加表锁。

在加表锁的时候，是如何判断有锁的？重新检索索引，来判断是加锁？那是不可能的，因为那样效率会低的令人发指，尤其是表级锁，需要检索完所有的数据才能知道是表级别锁，效率可想而知。这个时候就有了意向锁。

意向锁，只是一个意向，他的意思是：这个表，里面的数据或索引已经加锁了。其实他就是一个标志，来告诉数据库已经加锁的标志。意向锁的存在说明该结点的下层结点正在被加锁。

对任一元组加锁时，必须先对它所在的关系加意向锁。所以正确的是，加共享锁（S锁，读锁）时候，会在之前加上意向共享锁（IS锁）。告诉下一个要操作这张表的事物，这张表的数据加了共享锁。

通过锁实现的是，读的时候不能写（允许多个线程同时读，即共享锁，S锁），写的时候不能读（一次最多只能有一个线程对同一份数据进行写操作，即排它锁，X锁）。这样的加锁访问，只能实现并发的读，因为它最终实现的是读写串行化，这样就大大降低了数据库的读写性能。加锁访问其实就是和MVCC相对的LBCC，即基于锁的并发控制（Lock-Based Concurrent Control），是四种隔离级别中级别最高的Serialize隔离级别。

6. MVCC( Multi-Version Concurrency Control 多版本并发控制 )

MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存 。通俗的讲就是MVCC通过保存数据的历史版本，根据比较版本号来处理数据的是否显示，从而达到读取数据的时候不需要加锁就可以保证事务隔离性的效果。

• MVCC每次更新操作都会复制一条新的记录，新纪录的创建时间为当前事务id
• 优势为读不加锁，读写不冲突
• InnoDb存储引擎中，每行数据包含了一些隐藏字段 DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT
• DATA_TRX_ID 字段记录了数据的创建和删除时间，这个时间指的是对数据进行操作的事务的id
• DATA_ROLL_PTR 指向当前数据的undo log记录，回滚数据就是通过这个指针
• DELETE BIT位用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在mysql进行数据的GC，清理历史版本数据的时候。

具体的DML：

• INSERT：创建一条新数据，DB_TRX_ID中的创建时间为当前事务id，DB_ROLL_PT为NULL
• DELETE：将当前行的DB_TRX_ID中的删除时间设置为当前事务id，DELETE BIT设置为1
• UPDATE：复制了一行，新行的DB_TRX_ID中的创建时间为当前事务id，删除时间为空，DB_ROLL_PT指向了上一个版本的记录，事务提交后DB_ROLL_PT置为NULL

为了提高并发度，InnoDb提供了这个「非锁定读」，即不需要等待访问行上的锁释放，又解决了幻读。

6.1MVCC与隔离级别

• Read Uncommitted每次都读取记录的最新版本，会出现脏读，未实现MVCC
• Serializable对所有读操作都加锁，读写发生冲突，不会使用MVCC
• SELECT
  • (RR级别)InnoDB检查每行数据，确保它们符合两个标准：
  • 只查找创建时间早于当前事务id的记录，这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行
  • 行的DELETE BIT为1时，查找删除时间晚于当前事务id的记录，确定了当前事务开始之前，行没有被删除
  • (RC级别)每次重新计算read view，read view的范围为InnoDb中最大的事务id，为避免脏读读取的是DB_ROLL_PT指向的记录

简单的SELECT不加条件的查询在RR下肯定是读不到隔壁事务提交的数据的。但是仍然可能在执行INSERT/UPDATE时遇到幻读现象。因为SELECT 不加锁的快照读行为是无法限制其他事务对新增重合范围的数据的插入的。

所以还要引入第二个机制。

6.2 Next-Key Lock

其实更多的幻读现象是通过写操作来发现的，如SELECT了3条数据，UPDATE的时候可能返回了4个成功结果，或者INSERT某条不在的数据时忽然报错说唯一索引冲突等。

首先来了解一下InnoDB的锁机制，InnoDB有三种行锁：

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
• Next-Key Lock：前两个锁的加和，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题

如果是带排他锁操作（除了INSERT/UPDATE/DELETE这种，还包括SELECT FOR UPDATE/LOCK IN SHARE MODE等），它们默认都在操作的记录上加了Next-Key Lock。只有使用了这里的操作后才会在相应的记录周围和记录本身加锁，即Record Lock + Gap Lock，所以会导致有冲突操作的事务阻塞进而超时失败。

隔离级别越高并发度越差，性能越差，虽然MySQL默认的是RR，但是如果业务不需要严格的没有幻读现象，是可以降低为RC的或修改配置innodb_locks_unsafe_for_binlog为1 来避免Gap Lock的。 注意有的时候MySQL会自动对Next-Key Lock进行优化，退化为只加Record Lock，不加Gap Lock，如相关条件字段为主键时直接加Record Lock。

第一篇
MySQL知识点总结（一）基础语法