MySQL事务

事务ACID特性

原子性（Atomicity）：事务是原子操作的最小单元。要么全部执行，要么全部不执行。

一致性（Consistency）：是指事务在执行前后，数据库的完整性约束未被破坏，都是合法的数据状态。

隔离性（Isolation）：事务与事务之间是透明的，互不干扰的。

持久性（Durability）：对数据的增删改操作，只要事务提交成功，那么结果就是永久性的。

事务竞争存在的问题

脏读：在第二个事务还未提交之前，第一个事务执行的两次查询操作得到的数据结果不一致（即读到未提交的数据导致的）

不可重复读：一个事务读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况。（即读到了已提交的数据导致的）

 幻读：一个事务前后两次读取数据数据数量不一致，是由于其他事务插入数据造成的。

 MySQL中的事务隔离级别

Mysql通过不同的事务隔离级别来解决多事务并发导致的问题，如图：

MySQL如何实现事务隔离

通过MVCC和LBCC实现。

MVCC（Mutil Versio Concurrency Control）

MVCC，多版本并发控制，是一种并发控制的方法，在某个时间点保存数据快照。

InnoDB 为每行记录都实现了两个隐藏字段：

DB_TRX_ID，6 字节：插入或更新行的最后一个事务的事务 ID，事务编号是自动递增的（我们把它理解为 创建版本号 ，在数据新增或者修改为新数据的时候，记录当前事务 ID）。

DB_ROLL_PTR，7 字节：回滚指针（我们把它理解为 删除版本号 ，数据被删除或记录为旧数据的时候，记录当前事务 ID）。

模拟实现流程：

1.第一个事务，初始化数据（检查初始数据）

 此时的数据，创建版本是当前事务 ID，删除版本为空：

2.第二个事务，执行第 1 次查询，读取到两条原始数据，这个时候事务 ID 是 2：

 3.第三个事务，插入数据：

 

此时的数据，多了一条 tom，它的创建版本号是当前事务编号，3：

 

 4.第二个事务，执行第 2 次查询：

 MVCC 的查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。

也就是不能查到在我的事务开始之后插入的数据，tom 的创建 ID 大于 2，所以还是只能查到两条数据。

5.第四个事务，删除数据，删除了 id=2 jack 这条记录：

此时的数据，jack 的删除版本被记录为当前事务 ID，4，其他数据不变：

6. 在第二个事务中，执行第 3 次查询：

 查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。

也就是，在我事务开始之后删除的数据，所以 jack 依然可以查出来。所以还是这两条数据

7. 第五个事务，执行更新操作，这个事务事务 ID 是 5：

此时的数据，更新数据的时候，旧数据的删除版本被记录为当前事务 ID 5（undo），产生了一条新数据，创建 ID 为当前事务 ID 5：

 

 8.第二个事务，执行第 4 次查询：

 查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。

因为更新后的数据 penyuyan 创建版本大于 2，代表是在事务之后增加的，查不出来。而旧数据 qingshan 的删除版本大于 2，代表是在事务之后删除的，可以查出来。

通过以上演示我们能看到，通过版本号的控制，无论其他事务是插入、修改、删除，

第一个事务查询到的数据都没有变化。

Read View

用来实现一个事务对另外一个事务的可见性。每次开启一个事务，都会创建一个Read View。

ReadView的判断规则： （事务隔离性的规则）

1.如果 trx_id 等于 creator_trx_id ，说明当前事务在访问它自己修改过的记录（本事务修改），所以这个版本可以被当前事务访问。

2.如果 trx_id 小于 min_trx_id ，说明在Undo版本链中的这个事务在当前事务生成 ReadView 前已经提交，所以这个版本可以被当前事务访问。【当前事务在执行的时候， 这个快照已经生成了】

3.如果 trx_id 大于或等于 max_trx_id ，说明在Undo版本链中的这个事务在当前事务生成 ReadView 后才开启，所以这个版本不可以被当前事务访问。

4.如果 trx_id 在 min_trx_id 和 max_trx_id 之间，此时再判断一下 trx_id 是不是在 m_ids 列表中。

        4.1 如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；

        4.2 如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

LBCC（Lock Based Concurrency Control）

LBCC，基于锁的并发控制。指一个事务在进行数据查询时，不允许其他事务修改。也就是说，基于锁的机制使得数据库无法支持并发事务的读写操作，在一定程度上影响操作数据的效率。

锁的粒度

MyISAM：只支持表锁，用 lock table 语法加锁。

InnoDB：支持行锁 和 表锁。锁的粒度越小，并发性能越高。

锁的类型

在Mysql中，锁分为8类，共享锁/排他锁 、 意向锁、 记录锁、 间隙锁、 插入意向锁、自增锁、空间索引谓词锁、next-key lock。官网参考地址： https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

1. 锁的基本模式： （Shared And Exclusive Locks）行级别锁 和（Intention Locks）表级别锁

2. 后面的三个：Record Locks、Gap Locks、Next-Key Locs 我们称为锁的算法，也就是说在什么情况下锁定什么范围。

3. 插入意向锁（ Insert Intention Locks ）：是一个特殊的间隙锁。

4. 自增锁( AUTO-INC Locks )：是一种特殊的表锁，用来防止自增字段重复，数据插入以后就会释放，不需要等到事务提交才释放。

5. 空间索引谓词锁（Predicate Locks for Spatial Indexes）是5.7版本里面新增的空间索引的谓

词锁。

共享锁

获取一行数据的读锁后，可以用来读取数据，所以也叫读锁。多个事务可以共享一把读锁。注意不要在加上读锁的数据上进行写操作，可能会出现死锁的情况。

通过 select .... lock in share mode；加上读锁。

释放锁的方式：事务结束（包括回滚或者提交），锁自动释放 

排它锁

用来操作数据的，也叫写锁。只要一个事务获取了一行数据的排它锁，那么其他事务就不能获取到该行数据的共享锁和排它锁。

加锁方式：

1.自动加上排它锁。当我们在操作数据的时候，包括增删改，都会默认加上排它锁。

2.手动加锁。通过FOR UPDATE 语句手动加锁。

意向共享锁 / 意向排他锁

是由数据库自己维护的。

当我们给一行数据加上共享锁 / 排他锁之前，数据库会自动在这张表上面加一个意向共享锁 / 意向排他锁。

如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁。

作用：1.使得InnoDB能够支持更多粒度的锁。2.提示加锁效率。可以作为一个标志，能够通过判断该标志，快速判断该表中是否存在其他更小粒度的锁，从而提高加锁效率。

行锁的原理

1.没有索引的表。通过任一字段的等值查询，锁住的是整张表。因为没有索引进行的是全表扫描。

2.有主键的表。通过主键的等值查询，锁住的是行数据，即行锁。

3.有唯一索引的表。通过唯一索引列的等值查询，锁住的也是行数据，即行锁。（此处涉及到聚簇索引和非聚簇索引的知识，即非聚簇索引存储的是指向聚簇索引的地址，而获取数据需要通过聚簇索引）

锁的算法（行锁中的锁的算法）

• Record Lock (行锁) [锁定的是索引]
• Gap Lock（锁定索引区间，不包括record lock，左右开区间）
• next Key Lock（锁定索引区间，包括record lock，左开右闭区间）

记录锁（Record Lock）

记录锁就是为 某行 记录加锁，它 封住的是该行的索引记录，并不是真正的数据记录。

当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。

Gap Lock（间隙锁）

间隙锁是基于非唯一索引，它锁定一段范围内的索引记录。

当我们查询的记录不存在，没有命中任何一个 record，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁。

Next-Key Lock（临键锁）

Next-Key 可以理解为一种特殊的 间隙锁 ，也可以理解为一种特殊的 算法

当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap间隙，在这种情况下我们使用的就是临键锁

Mysql中事务隔离级别总结

Read Uncommited： 不加锁。

Read Commited： 普通的select都是快照读，使用MVCC实现。

加锁的select都使用记录锁，因为没有Gap Lock。

Repeatable Read： 普通的select使用快照读(snapshot read)，底层使用MVCC 来实现。

加锁的select(select ... in share mode / select ... for update)以及更新操作 update, delete等语句使用当前读（current read），底层使用记录锁、或者间隙锁、临键锁。

Serializable：所有的select语句都会被隐式的转化为select ... in share mode，会和update、delete互斥。

死锁

死锁的产生条件： 因为锁本身是互斥的

1. 同一时刻只能有一个事务持有这把锁，
2. 其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺，
3. 当多个事务形成等待环路的时候，即发生死锁。

锁什么时候释放？

1.事务结束（commit，rollback）；2.客户端连接断开。

默认锁的释放时间：50s  （ show VARIABLES like 'innodb_lock_wait_timeout' ; ）

如何查看锁的日志

show status like 'innodb_row_lock_%' ;（概要信息）：

Innodb_row_lock_current_waits：当前正在等待锁定的数量；

Innodb_row_lock_time ：从系统启动到现在锁定的总时间长度，单位 ms ；

Innodb_row_lock_time_avg ：每次等待所花平均时间；

Innodb_row_lock_time_max ：从系统启动到现在等待最长的一次所花的时间；

Innodb_row_lock_waits ：从系统启动到现在总共等待的次数。

InnoDB 还提供了三张表来分析事务与锁的情况：

select * from information_schema .INNODB_TRX ; -- 当前运行的所有事务 ，还有具体的语句

select * from information_schema .INNODB_LOCKS ; -- 当前出现的锁

select * from information_schema .INNODB_LOCK_WAITS ; -- 锁等待的对应关系

死锁的避免

1. 在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路）；
2. 批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路）；
3. 申请足够级别的锁，如果要操作数据，就申请排它锁；
4. 尽量使用索引访问数据，避免没有 where 条件的操作，避免锁表；
5. 如果可以，大事务化成小事务；
6. 使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响。