Mysql关于事物和锁

Mysql的事物和锁：

一：锁：

锁粒度：

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

MySQL各存储引擎使用了三种类型（级别）的锁粒度：行锁、表锁、页锁

1.行锁：行级锁定最大的特点就是锁定对象的颗粒度很小，由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁，使用行级锁定的主要是InnoDB存储引擎

2.表锁：表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。表锁的语法是lock tables … read/write 当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣，使用表级锁定的主要是 MyISAM、MEMORY等一些非事务性存储引擎。

3.页锁：页级锁定是MySQL中比较独特的一种锁定级别。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。使用页级锁定的主要是BerkeleyDB存储引擎。

兼容性：

1. 共享锁（读锁）

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

写法：SELECT ... LOCK IN SHARE MODE;

2. 排他锁（写锁）

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

写法：SELECT ... FOR UPDATE;

3. 意象锁

针对于innodb的多粒度锁的情况，我们来假设一个场景，如果我需要给一张表加表锁，我们需要去判断表中的每一行是否已经持有了锁，这个复杂度特别的高。我们可以在加行锁的时候，直接在表级别加上意象锁，这样加表锁时，只需要去判断是否存在意向锁即可。

比如我们需要给一行记录加上排他锁，首先会在表级别加入一个意象排他锁，然后在行级别加上一条排他锁。

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

这里指的锁都是表级的锁。

	共享锁（S）	共享锁（S）	意向共享锁 (IS)	意向排他锁 (IX)
共享锁（S）	兼容	冲突	兼容	冲突
排他锁 (X)	冲突	冲突	冲突	冲突
意向共享锁 (IS)	兼容	冲突	兼容	兼容
意向排他锁 (IX)	冲突	冲突	兼容	兼容

总结：

1、InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。

2、意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与 共享锁 / 排他锁 互斥。

3、IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

4、意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

加锁模式：

记录锁：

要知道，行锁都是锁在索引上的，如果要锁的列没有索引，会进行全表记录加锁。如果锁的是二级索引，那么一般情况下都是先锁二级索引再锁聚簇索引。特殊情况下面介绍

注意是通过Next Key Lock锁定的全表范围，而不是通过表级锁直接锁表

间隙锁：

间隙锁 是 Innodb 在RR (可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。

间隙锁就是锁定一个双开区间，比如我想要锁定id=6的记录，发现记录不存在，那么就需要锁定4-7的一个区间，避免之后区间内有新的记录插入。实际上，间隙锁也是锁在记录上的，只是锁的类型不同。该例中的间隙锁，是锁在7这条记录上的，类型为间隙锁。

临键锁：

临建锁（next-key）=记录锁+间隙锁。临键锁是innodb的最基本的锁单位，只是在某些情况下会退化成记录锁或者间隙锁。

在加锁的时候，我们都是对记录加锁，如果是间隙锁，我们也是对开区间的最后一条记录上加上一把锁。所以我们在插入记录时，我们也需要判断这个插入区间往后的第一条记录上的锁情况。

加锁原则：【两原则，两优化】

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

我们可以举几个例子实践一下。

create table `t` ( 
`id` int (11) not null ,
`c` int (11) default null ,
`d` int (11) default null ,
primary key ( `id` ),
key `c` ( `c` )
)engine = innodb;
insert into t values (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一：等值查询间隙锁

由于表 t 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：

1. 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；

2. 同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

案例二：非唯一索引等值锁

第二个例子是关于覆盖索引上的锁：

看到这个例子，你是不是有一种“该锁的不锁，不该锁的乱锁”的感觉？我们来分析一下吧。

这里 session A 要给索引 c 上 c=5 的这一行加上读锁。

1. 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5] 加上 next-key lock。

2. 要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。根据原则 2，访问到的都要加锁，因此要给 (5,10] 加 next-key lock。

3. 但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。

4. 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。

需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将 session A 的查询语句改成 select d from t where c=5 lock in share mode。你可以自己验证一下效果。

案例三：主键索引范围锁

第三个例子是关于范围查询的。

举例之前，你可以先思考一下这个问题：对于我们这个表 t，下面这两条查询语句，加锁范围相同吗？

	mysql> select * from t where id=10 for update;		mysql> select * from t where id=10 for update;
	mysql> select * from t where id>=10 and id<11 for update;		mysql> select * from t where id>=10 and id<11 for update;

你可能会想，id 定义为 int 类型，这两个语句就是等价的吧？其实，它们并不完全等价。

在逻辑上，这两条查语句肯定是等价的，但是它们的加锁规则不太一样。现在，我们就让 session A 执行第二个查询语句，来看看加锁效果。

现在我们就用前面提到的加锁规则，来分析一下 session A 会加什么锁呢？

1. 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。

2. 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。这样，session B 和 session C 的结果你就能理解了。

这里你需要注意一点，首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

案例四：非唯一索引范围锁

接下来，我们再看两个范围查询加锁的例子，你可以对照着案例三来看。

需要注意的是，与案例三不同的是，案例四中查询语句的 where 部分用的是字段 c。

这次 session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

所以从结果上来看，sesson B 要插入（8,8,8) 的这个 insert 语句时就被堵住了。

这里需要扫描到 c=15 才停止扫描，是合理的，因为 InnoDB 要扫到 c=15，才知道不需要继续往后找了。

总结

那么我们在操作一个事务的时候，如果对应多条记录，是不是要针对多条记录生成多个内存的锁结构呢？

实际上不是的，锁是可以合并的，需要遵循几个原则

1. 加锁操作时在同一个事务中

2. 需要被加锁的记录在同一个页中

3. 需要加锁的类型是一致的

4. 锁的等待状态是一致的

具体锁结构，以及加锁流程，查看ref：加锁流程

二：事物

事务的四大特性

原子性(Atomicity)：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做。 undolog 来保证

一致性(Consistency)：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性(Isolation)：一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。mvcc 和 锁 来保证

持久性(Durability)：指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。 redolog 来保证

隔离级别

首先介绍几个概念：

1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

2. 不可重复的读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复的读侧重于修改，幻读侧重于新增和删除

read-uncommitted 读未提交

在该级别，所有的事务都可以看到其他未提交事务的执行结果，本隔离级别很少用于实际应用，因为它的性能不比其他级别好多少。读取未提交的数据，也称之为脏读。

read-committed 读提交内容

这是大多数数据库系统的默认隔离级别（但不是MYSQL默认的），它满足了隔离的简单定义：一个事务只能看见已提交事务所做的改变。也支持所谓的不可重复读。

repeatable-read 可重读

是MYSQL默认的，确保统一事务的多个实例在并发读取数据时，会看到同样的数据行。

serializable 可串行化

这是最高的隔离级别，他通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简而言之，他是在每个读的数据行上加上共享锁。在这个级别可能导致大量的超时现象和锁竞争。

在读已提交和可重复读隔离级别下的快照读，都是基于MVCC实现的！

mvcc

Multi Version Concurrency Control 用来实现不加锁情况下的读一致性和隔离性。

mvcc的实现，基于undolog、版本链、readview

版本链：

每行数据都有隐式字段

1. DB_TRX_ID 最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

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

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

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

读视图（read view）

事务id是递增的只会越来越大，在开启事务的时候，第一次快照读会产生一个读视图，将目前活跃的还未提交的事务记录下来，并排序出最低事务和最高事务。

 

Read View几个属性

trx_ids: 当前系统活跃(未提交)事务版本号集合。

low_limit_id: 创建当前read view 时“当前系统最大事务版本号+1”。

up_limit_id: 创建当前read view 时“系统正处于活跃事务最小版本号”

creator_trx_id: 创建当前read view的事务版本号；

在每次查询数据的时候，会判断当前行数据最新的事务id trx_id是多少。

1. trx_id < up_limit_id || trx_id == creator_trx_id（显示）

说明该行的记录早就提交了事务，当前的事务是可见的。

或者该行的最后修改就是本事务提交的，也可见。

2. trx_id >= low_limit_id（不显示）

如果数据事务ID（trx_id）大于read view 中的当前系统的最大事务ID，则说明该数据是在当前read view 创建之后才产生的，所以数据不显示。

3. 判断trx_id是否在活跃事务（trx_ids）中

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

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

mvcc如何实现RC和RR的隔离级别

（1）RC的隔离级别下，每个快照读都会生成并获取最新的readview。

（2）RR的隔离级别下，只有在同一个事务的第一个快照读才会创建readview，之后的每次快照读都使用的同一个readview，所以每次的查询结果都是一样的。

差不多就这些了～