MYSQL事务和锁

锁

表级锁和行级锁的区别

InnoDB的几种表级锁

表锁

表级读锁：阻止其他的对表的写

表级写锁：阻止其他对表的读写

元数据锁MDL

作用：防止执行CRUD的时候表结构变更

数据库本身会加MDL，不用我们来操作

写锁获取优先级高于读锁

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

那它在什么时候释放？-->在事务提交之后

意向锁

作用：为了快速判断表里是否有行锁

为什么要判断？因为读锁和行锁是读写互斥，写写互斥的

在加上行共享锁之前，先在表级别加上一个“意向共享锁”

在加上行独占锁之前，先在表级别加上一个“意向独占锁”

这里面谈到普通的select语句是不算里面的，这类是通过MVCC实现的一致性，无锁

InnoDB的几种行级锁

锁的兼容性分类

共享锁S：读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取锁

排它锁X：写锁，事务在修改记录的时候获取排它锁，不允许多个事务.....。

锁的范围分类

记录锁：锁定单个记录。

间隙锁：锁定一个id数字集合（范围），不包括记录本身。

前开后开

临键锁：记录锁+间隙锁，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题。

前开后闭

什么时候加行级锁？

普通的 select 语句是不会对记录加锁的，MVCC实现一致性

首先记住，加锁的对象都是索引！！！，基本单位就是默认的next-key lock

唯一索引等值查询情况

• 查询的记录 存在，定位到记录之后，退化成 X记录锁
• 查询的记录 不存在，定位到第一个大于查询记录的记录之后，退化成 间隙锁，锁住这个记录和上一条记录的范围
• 思考：不沿用next-key lock的原因是因为这个场景下幻读只需要间隙锁就能解决了

• 为什么 id = 5 记录上的主键索引的锁不可以是 next-key lock？如果是 next-keylock，就意味着其他事务无法删除 id = 5 这条记录，但是这次的案例是查询 id = 2 的记录，只要保证前后两次查询 id = 2 的结果集相同，就能避免幻读的问题了。

唯一索引范围查询情况

先说明范围查询的整体机制：

会对每一个扫描到的索引加next-key lock，下面细分情况

实际在 Innodb 存储引擎中，会用一个特殊的记录来标识最后一条记录，该特殊的记录的名字叫supremum pseudo-record

1. 大于情况：往后推，前加一个包含自己的，后面以此递归最后扫到supremum加 (xxxx, +∞]
2. 大于等于情况：等值的地方变成记录锁
3. 小于或小于等于情况：条件值不在表里，终止扫描记录的Next-Key锁退化成间隙锁
4. 小于情况：条件值在表里，终止扫描记录的Next-Key锁退化成间隙锁
5. 小于等于情况：条件值在表里，终止扫描记录的Next-Key锁不退化成间隙锁

举例：有个表有4个数据项

情况5：10满足加(Previous, 10]，20满足加(10, 20]，30满足加(20, 30]，到边界了，终止扫描

SELECT * FROM products WHERE id <= 30;

情况4：10满足加(Previous, 10]，20满足加(10, 20]，30不满足，退化，加(20, 30)

SELECT * FROM products WHERE id < 30;

情况1：定位到30，加(20, 30]，40满足加(30, 40]，接着扫描，加锁(40, +∞]

SELECT * FROM products WHERE id > 20;

情况2：20，临键锁退化，变成记录锁锁20，接着同情况1

SELECT * FROM products WHERE id >= 20;

情况3：10满足加(Previous, 10]，20满足加(10, 20]，然后到30，不满足，退化，加(20, 30)

SELECT * FROM products WHERE id <(<=) 25;

什么时候加表级锁？

当执行UPDATE、DELETE的语句的时候，WHERE没有命中索引或者索引失效的时候，就会对全表扫描加锁。

事务

四大特性

原子性：事务是最小的执行单位，不能分割 --- undo log保证的

隔离性：并发访问数据库的时候，事务彼此之间是不可见的（取决于事务隔离级别）

比如先后扣款，先扣款事务还没做完，后扣款事务来了看到的也是原数量

持久性：一个事务被提交之后，他对数 Q`据库的改变是永久的，即使数据库发生故障。 --- redo log保证的

一致性：执行事务前后，数据保持一致（指变化总和）

并发事务带来的问题（问题）

脏读

一个事务读取数据并且修改了这个数据，这个时候第二个事务读取了这个未提交的数据，但是第一个事务突然回滚，导致改后的数据没有到数据库里面，最后第二个事务读到了脏数据

丢失修改

一个事务在读取一个数据的时候，另一个事务读取这个数据，第一个事务修改这个数据，第二个数据也修改这个数据，但是数据最后相当于只修改了一次，丢失了一次修改

不可重复读

指一个事务在事务内多次读一个数据，但是在中间间隙中来了第二个事务，修改了数据，导致第一个事务在事务内前后两次读到的数据不一样

幻读

意思就是一个事务读了几条数据，在他第二次读之前中间来了第二个事务插入了几条数据，导致第一个事务再读的时候像出现了幻觉一样多了几条数据

不可重复读、幻读的区别

实际上幻读就是不可重复读特殊情况，不可重复读强调某一条数据被修改了，幻读强调对数据行数的修改上（变多或者变少）

之所以区分是因为解决他们的方案不一样

标准事务隔离级别（解决）

是用来平衡隔离性和并发性能的东西
级别越高，数据一致性越好，但并发性能可能越低。

1. 读取未提交：允许读取未提交的数据变更，啥都防不住
2. 读取已提交：允许读取并发事务已经提交的数据，可以阻止脏读
3. 可重复读：对同一个字段的多次读取都是一样的，除非是自身修改，阻止了脏读和不可重复读，至于幻读，仍有可能（InnoDB大幅减少）
4. 可串行化：最高隔离，依次执行，全都防住

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

Mysql的默认隔离级别

Mysql InnoDB引擎 的默认隔离级别是可重复读。

InnoDB怎么解决大部分幻读的

快照读：普通的SELECT的语句，通过MVCC实现，事务启动时创建一个数据快照，后续的快照读都读取这个版本的数据，这样避免了看到其他事务插进来的行（幻读）和修改的行（不可重复读）

当前读：除了普通查询之外的操作读取到的都是最新数据而不是快照数据，InnoDB用临键锁next-key lock防止其他事物在这个范围内插入新的记录（幻读）

（像 SELECT ... LOCK IN SHARE MODE（加S锁），SELECT ... FOR UPDATE，INSERT， UPDATE， DELETE（加X锁） 这些操作）

正因为这样的机制，InnoDB在可重复读（RR）级别的性能，在只读或者读多写少的情况下，和读取已提交（RC）的性能差不多。在写密集的时候，RR的间隙锁可能会导致它比RC出现更多的锁等待

Mysql的隔离级别怎么实现的

由锁和 MVCC 共同实现的

读取未提交：啥也不用干（实际上InnoDB根本不支持，设置也会自动变成读取已提交）

读取已提交：通过Read View实现的，实际上是高级的版本号

可重复读：通过Read View实现的，实际上是高级的版本号

可串行化：完全依赖于锁

Read View是如何工作的？

是什么东西？

他有四个字段：

1. m_ids是一个活跃事务id列表
2. min_trx_id活跃事务中id最小的事务
3. max_trx_id是活跃事务最大id的下一个事务的id值，也就是最大事务id+1
4. creator_trx_id是创建这个Read View的事务id

活跃事务：运行中且未提交的事务

怎么解决并发的

RR可重复读实现

这时候一个事务来进行增删改查等操作，会先创建一个Read VIew来判断在做后续的操作的时候，其他事务自己能不能可见，这里有个先后顺序，也就是说先创建快照，再执行查询。

有几种情况：（trx _id是其他事务的id）

• 如果记录的trx_id小于Read View中的min_trx_id，说明这个事务是在这view版本创建的时候就已经结束了。可见
• 如果记录的trx_id大于等于Read View中的max_trx_id，说明了这个事务是在View 快照之后才开始的事务。不可见
• 如果记录的trx_id在min_trx_id和max_trx_id之间，先判断trx_id在不在m_ids中
• 在：表示生成该版本的记录的活跃事务还没提交，所以不可见（还在忙活，可能回滚）
• 不在：表示生成该版本的记录的活跃事务已经提交，所以可见（忙活完了，间隙进来的） 在创建快照和执行查询的间隙进来的

RC读以提交实现

创建快照是每次执行SELECT都创建一个read view快照，有不可重复读的情况，因为没有解决那个间隙问题。

InnoDB 对 MVCC 的实现

undolog和readview的配合：当 Read View 判断当前数据不可见时，它必须通过 Undo Log 提供的版本链，去找到一个它认为可见的历史版本。

可重复读：启动事务时生成一个 Read View，整个事务期间都用这个，记录相应的undo log用链表连起来，读只能读到启动之前的记录

读提交：事务读取的时候创建Read View

MVCC是什么

上面这个版本链条控制并发事务同时访问一个记录的解决方案就是MVCC，下面就是思想，readview+undolog是实现

读操作的时候

使用快照读取。快照读取基于事务开始的时候数据库的状态来创建，因此事务不会读取其他事务未提交的修改，具体情况：

读取的时候，事务查找符合条件的数据行，选择符合事务开始时间的版本读取，如果有多个版本，不晚于开始时间的最新版本。

写操作的时候

生成一个新的数据版本，并且把修改后的数据写入数据库，具体情况：

写的时候，事务会为要修改的数据行创建一个新的版本，并且把修改后的数据写入新版本，新版本的数据有当前事务的版本号，以便其他事务能读取相应版本的数据；旧版本的数据也留着，以便其他事务快照读。

事务提交和回滚的时候

事务提交的时候，做的修改成为数据库的最新版本，并且其他事务可见。

事务回滚的时候，做的修改撤销，并且其他事务不可见