【MySQL】问题大杂烩

最近在设计数据库，之前对数据库只停留在会DDL和DML写CRUD，索引啊锁啊这种东西基本上是在上完课就还给老师了，这一波整个项目都由我来负责，刚好趁此机会把MySQL好好再看一遍，遇到的问题什么的都在博客记录下来~

3月4日问题集合：

1.并发事务存在的问题有哪些?
2.四种隔离级别的理解?

1.并发事务存在以下3种问题：

(1)脏读：事务B修改了x，未提交前，事务A读了x，就形成了脏读。即，事务读取未提交的数据。

(2)不可重复度：事务B读了x，然后在执行其他逻辑的时候，事务A修改了x，那么事务B再次读取x的时候就导致了两次数据的不一致。(针对update、delete)

(3) 幻读:事务B读取某一行row1，当事务A提交后（增加了row2），事务B再次查询得到了（row1，row2)的结果集。（针对insert）

2.四种隔离级别：

（1）未提交读 Read Uncommitted

事务中的修改，即使未提交，其他事务也能看得到，会导致上述（1）（2）（3）种问题，一般很少用。

（2）提交读 Read Committed

事务只会看到已提交事务做出的修改，也就是说，一个事务从开始到提交之前，所做的修改对其他事务都是透明的。

会导致（2）（3）问题。

（3）可重复读 Repeatable Read （MySQL默认隔离级别)

一个事务多次读取同样内容得到的结果一致,也就是说，事务不会修改由另一个事物读取但未提交的数据。假如事务A多次读取x，那么x就不会在A提交之前被别的事务修改。

会导致（3），这里解释一下为什么会导致幻读，假设我们的数据表有这样一些数据：

事务A读取了profit=2001的那一条数据，那么在RR级别下，别的事务就不能对它进行修改了，但是别的事务可以添加另一条profit=2001的数据，这样在事务A再次读取profit=2001的数据时，实际上就会读到别的事务增加的那条数据，这就导致了幻读。
这里说明一下，MySQL的InnoDB通过多版本并发控制（MVCC）解决了幻读问题。

（4）可串行化Serializable

强制事务串行执行，是最高的级别，给每一行都加锁，就会导致超时或锁争用问题。虽然避免了幻读，但也很少用到这个级别。

----------------------------------------分割线-------------------------------------------

3月5日问题集合：

1.那既然说InnoDB通过MVCC解决了幻读，什么是MVCC？

MVCC 多版本并发控制（以下内容来自《高性能MySQL》，再加一点我自己的理解吧）

可以认为MVCC是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低，虽然实现机制有所不同，但大多都是实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的实现是通过保存数据在某个时间点的快照来实现的。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏列来实现，一列保存行创建的时间，一列保存行过期（or删除）的时间，当然，存储在里边的不是实际的时间，而是系统版本号，每开始一个新事务，系统版本号都会递增。

那么，在RR级别下，MVCC是怎么做的呢（怎么解决了幻读）？

SELECT 

InnoDB会根据以下两个条件检查每行记录：

a.InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始之前存在的，要么是事物自身插入或修改的。

b.行的删除版本要么未定义，要么大于当前事务版本号，这样确保事务读取到的行，在事务开始之前未被删除。

只有符合以上两个条件的记录，才能作为结果返回。

INSERT

InnoDB为新插入的每一行保存当前系统版本作为行版本号。

DELETE

InnoDB为删除的每一行保存当前系统版本作为行删除标识。

UPDATE

InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

保存这两个额外的版本号可以使大多数读操作都可以不加锁。MVCC只在RR和RC两个级别下工作。

等下，我看到你说对大多数读操作？那还有些读操作得加锁才行？

 别急我们先来试一下，看看幻读的情况什么时候会发生

前面说，MVCC在RR和RC级别下工作，那我们在RC级别下试试（为啥不选RR？看完你就知道了）

我有一个表sales，是这样的：

我们开启事务A，并做一些操作：
 

事务A：

start transaction
set session transaction_isolation="read-committed";--设置事务隔离级别
select @@transaction_isolation;--查看隔离级别

然后我们来select一下：

事务A:
select * from sales where profit=2003;

 

现在我们开启事务B，并作一些操作：

事务B：

start transaction
set session transaction_isolation="read-committed";
select @@transaction_isolation;
insert into sales values(2010,'china_test','tnt_test',2003);--插入一条profit=2003的新数据
commit;

可以看到插入成功，然后我们提交事务B，这时候再返回事务A执行：

事务A：
select * from sales where profit=2003;

 

可以看到在这种情况下，我们没有出现幻读，这也是上面说的大部分读操作在MVCC下可以解决幻读

那我们来点不一样的？

我们把刚才加的那一行删掉(其实不删也行，但我想做一个对比）

然后重新开始事务A：

事务A：
start transaction
set session transaction_isolation="read-committed";
select * from sales where profit=2003 for update;--不一样的读操作

结果和之前一样，查到一条，那我们来开始事务B（和之前的操作一毛一样，控制变量法嘛）：

事务B：

start transaction
set session transaction_isolation="read-committed";
select @@transaction_isolation;
insert into sales values(2010,'china_test','tnt_test',2003);--插入一条profit=2003的新数据
commit;

这里看到插入成功了，那我们再回到事务A来看看：

事务A：
select * from sales where profit=2003 for update;

 

看见没，出现了幻读！

欸？为啥会出现幻读，这个select有啥不一样嘛？怎么解决这个幻读？

 对于如何解决这个问题，InnoDB使用一个名为next-key locking的算法，（属于行锁的算法）。

在介绍这个算法之前，先把几个需要理解的概念在这里补充一下。

1. 快照读和当前读
2. 共享锁（读锁）和排他锁（写锁）
3. 表锁和行锁
4. 聚簇索引和辅助索引

1.快照读和当前读

快照读：简单的select操作属于快照读，不加锁（不包括select ... for update,select ... lock in share mode)

例如：select * from table where...

当前读：特殊的select，insert/update/delete操作，属于当前读，要加锁。

例如：

select ... for update

select...lock in share mode

insert...

update...

delete...

在这些操作中，都需要加锁，select ... lock in share mode加的是共享锁（S），其他都是排他锁（X）

2.共享锁和排他锁

共享锁：又叫做读锁，读锁是共享的，用户可以并发读，但不能修改，也就是说，事务A对数据加上共享锁后，别的事务也可以对数据加共享锁，但不能加排他锁。

select...lock in share mode

在select语句后面加上lock in share mode，MySQL对结果的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁：又叫写锁，写锁会阻塞其他的读锁和写锁，也就是当数据被加上排他锁后，再也无法对这个数据加任何锁，直到释放。

select ... for update

在select语句后面加上for update时，MySQL会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

欸?那你知道意向锁吗？怎么听说还有意向共享锁和意向排他锁？他们和共享锁和排他锁有什么区别？

意向锁：意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。

emmmmmmm，反正我当时看到百度这个概念我是想了很久，现在通俗易懂的来总结一下，什么是意向锁。

解释之前先说一下，意向锁是InnoDB自动加的，不需要用户干预，而共享锁和排他锁可以用户自己加（for update和lock in share mode）

假设事务A对数据表的某一行加了读锁，那么其他事务就只能读，不能写，也就是不能加排他锁。
此时有一个事务B对表申请一个表锁，如果他申请成功，那么也就意味着事务B对整个表拥有了写的权利，那事务A锁定的那一行不是矛盾了吗？

数据库要避免冲突，那么就需要检查这个表是否被其他事务加了表锁，要是没加，就还得检查这个表的每一行是不是被锁住，这样就需要遍历整个表，效率不高，所以就有了意向锁。

有了意向锁，事务A在给表的某一行加锁前，需要先申请一个意向共享锁，成功后，再对这一行加行锁。
那么事务B再加锁前就需要先判断表上是否加了意向共享锁，如果有，就说明有些行被加了共享锁，这时事务B就阻塞了。

所以这么看来，意向锁的意思就可以这么表达：告诉事务这个表上有其他事务加了共享锁或排他锁。

加了意向共享锁（IS）的表，还可以被其他事务加IS，但被共享锁锁定的那些记录是不能被修改的。

加了意向排他锁（IX）的表，就不能被加任何共享锁和排他锁了。

意向锁是个表级的锁，共享锁和排他锁可以是行级也可以是表级，IX和IS只会和表级的X和S冲突，不会和行级的冲突。

意向锁实现了表锁和行锁的共存（举的那个例子就说明行锁和表锁共存时会有冲突）

3.表锁和行锁

刚说S和X锁的时候我们提到了表锁和行锁，现在来解释一下：

表锁（table lock）：是MySQL中最基本的锁策略，且开销最小，他会锁定整张表

行锁（row lock）：给某一行加锁，行锁可以最大程度支持并发处理（同时带来最大的锁开销），行锁只在存储引擎层实现，而MySQL服务器层没有实现。

欸？有点晕了，X和S、表锁和行锁，有啥区别？

 表锁和行锁是从锁的粒度来分的，X和S是从类型来分的，所以我们之前说，可以有行级的S和X锁，也可以有表级的S和X锁。

4.聚簇索引和辅助索引

这个讲起来要涉及好多东西，另开一篇算了（明天就更！）

 

我们现在回到我们的next-key locking算法上来吧~

next-key locking是一个行锁的算法，除他以外InnoDB还有两个行锁的算法，他们分别是：

1.Record Lock：锁直接加在索引记录上，锁住的是key
2.Gap Lock:间隙锁，锁定索引记录的间隙，保证间隙不变，它是针对隔离级别为RR及以上的事务来说的。
3.Next-key locking：上面这两个家伙组合起来就是next-key locking。

之前说，InnoDB通过MVCC解决了幻读的问题，但他是在快照读的层面实现的（简单的select），next-key locking是在当前读层面来实现的，而且默认情况下是针对RR级别的（你现在知道为什么我们之前测试幻读情况要把级别设置为RC了吧）。

在使用next-key locking的时候，当InnoDB扫描索引记录时，会先对索引加上行锁（record lock）然后在索引两边的间隙加上间隙锁（gap lock），这样就不能在间隙的位置进行修改或插入，从而防止幻读的发生。