Mysql事务原理

什么是事务

事务将数据库从一种一致性状态转换为另一种一致性状态，事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。学习一个数据库，对该数据库进行ACID分析是很重要的

事务控制语句

原子性：mysql的事务原子性是通过 undolog 的回滚操作实现的。undolog 记录的是事务每 步具体操作，当回滚时，回放事务具体操作的逆运算，存储在共享表空间当中

隔离性：事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，并发事务之间不会相互影响，设定了不同程度的隔离级别（级别越高，它的并发性能越低），通过适度破环一致性，得以提高性能；通过 MVCC 和 锁来实 现；MVCC 时多版本并发控制，主要解决一致性非锁定读，通过记录和获取行版本，而不是使用 锁来限制读操作，从而实现高效并发读性能。锁用来处理并发 DML 操作；数据库中提供粒度锁的 策略，针对表（聚集索引B+树）、页（聚集索引B+树叶子节点）、行（叶子节点当中某一段记录 行）三种粒度加锁

持久性：事务提交后，事务DML操作将会持久化（写入 redolog 磁盘文件 哪一个页 页偏移值 具体数 据）；即使发生宕机等故障，数据库也能将数据恢复。redolog 记录的是物理日志

一致性：一致性指事务将数据库从一种一致性状态转变为下一种一致性的状态，在事务执行前后，数据库完 整性约束没有被破坏；一个事务单元需要提交之后才会被其他事务可见。例如：一个表的姓名是唯 一键，如果一个事务对姓名进行修改，但是在事务提交或事务回滚后，表中的姓名变得不唯一了， 这样就破坏了一致性；一致性由原子性、隔离性以及持久性共同来维护的

隔离级别：

READ UNCOMMITTED：读未提交，读不加锁，写加写锁，写不加锁会造成数据错乱，写锁在事务提交或者回滚后释放，但会造成很多读异常

READ COMMITTED：读提交，读的时加入多版本并发控制，该隔离级别下读取历史版本的最新数据，所以读取的是已提交的数据

REPEATABLE READ（mysql默认隔离级别）：可重复读，该版本下也支持MVCC，读取的是事务最开始的数据

SERIALIZABLE（割离级别最高）：可串行化，安全性能最高

MVCC多版本并发控制

通俗解释：读取的时候，你写你的，我去读取你的快照（每次事务提交都会产生数据的快照），从而提高并发性能。

在 read committed 和 repeatable read下，innodb使用MVCC；然后对于快照数据的定义不同； 在 read committed 隔离级别下，对于快照数据总是读取被锁定行的最新一份快照数据；而在 repeatable read 隔离级别下，对于快照数据总是读取事务开始时的行数据版本。

为什么读取快照数据不需要上锁？因为没有事务需要对历史的数据进行修改操作

锁

并发读异常，并发死锁通常是由行锁造成的，类比myisam，myisam只支持表锁，因此不会出现这些问题.

锁类型

（1）共享锁（S）

事务读操作加的锁；对某一行加锁；

在 SERIALIZABLE 隔离级别下，默认帮读操作加共享锁；

在 REPEATABLE READ 隔离级别下，需手动加共享锁，可解决幻读问题；

在 READ COMMITTED 隔离级别下，没必要加共享锁，采用的是 MVCC，读取的是最新的数据；

在 READ UNCOMMITTED 隔离级别下，既没有加锁也没有使用 MVCC；

（2）排他锁（X)

事务删除或更新加的锁；对某一行加锁；在4种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁； 

（3）意向共享锁（IS)

对一张表中的某几行加的共享锁

（4）意向排他锁（IX)

对一张表中的某几行加的排他锁，告诉别的事务这张表正在被一个事务访问，不要其他事务对整个表的行数据进行遍历，排除表锁读写锁

三中写操作：删除，更新，插入是如何加锁的？

删除和更新自动加排他锁；而插入操作会现在几行插入意向锁（表锁），再加排他锁；当我们实现自增的时候，我们也会加入auto-inc lock表锁.

锁的兼容性

由于innodb支持的是行级别的锁，意向锁并不会阻塞除了全表扫描以外的任何请求；意向锁之间是互相兼容的；

IS 只对排他锁不兼容；
当想为某一行添加 S 锁，先自动为所在的页和表添加意向锁 IS，再为该行添加 S 锁； 当想为某一行添加 X 锁，先自动为所在的页和表添加意向锁 IX，再为该行添加 X 锁；
当事务试图读或写某一条记录时，会先在表上加上意向锁，然后才在要操作的记录上加上读锁或写 锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。意向锁之间是 不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁 也不会和行锁冲突，行锁只会和行锁冲突。

锁算法

（1）Record Lock：

记录锁，单个行记录上的锁

（2）Gap Lock：

间隙锁，锁定一个范围，但不包含记录本身；全开区间；REPEATABLE READ级别及以上支持间隙 锁

（3）Next-Key Lock：

记录锁+间隙锁，锁定一个范围，并且锁住记录本身；左开右闭区间； 

Gap Lock和Next-Key Lock是可重复读下特有的锁

（4）Insert Intention lock

插入意向锁，insert操作的时候产生；在多事务同时写入不同数据至同一索引间隙的时候，并不需 要等待其他事务完成，不会发生锁等待

插入意向锁用来提升间隙插入的性能，如果没有插入意向锁，需要用间隙锁，举例：假设有一个记录索引包含键值4和7，两个不同的事务分别插入5和6，每个事务都会产生一个加在 4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

插入意向锁和间隙锁冲突，会造成死锁

（5）Auto_inc lock

自增锁，是一种特殊的表级锁，发生在 AUTO_INCREMENT 约束下的插入操作；采用的一种特殊 的表锁机制（较低概率造成B+树分裂）；完成对自增长值插入的SQL语句后立即释放；在大数据 量的插入会影响插入性能，因为另一个事务中的插入会被阻塞；从MySQL 5.1.22开始提供一种轻 量级互斥量的自增长实现机制，该机制提高了自增长值插入的性能.

一个事务已经获取了插入意向锁，对其他事务是没有任何影响的；
一个事务想要获取插入意向锁，如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞； 这个是重点，死锁之源；

锁的对象

行级锁是对表的索引加锁；索引包括聚集索引和辅助索引；

表级锁是针对页或表进行加锁；

重点考虑 InnoDB 在 read committed 和 repeatable read 级别下锁的情况；

举例：

现在有一张表student，其中 id 为主键，no（学号）为辅助唯一索引，name（姓名）和 age（年龄）为二级非唯一索引，score（学分）无索引。

讨论：

（1）聚集索引 查询命中：UPDATE students SET score = 100 WHERE id = 15

（2）聚集索引 查询未命中：UPDATE students SET score = 100 WHERE id = 16;

更新操作，可重复读会加gap锁

（3）辅助唯一索引，查询命中： UPDATE students SET score = 100 WHERE no = 'S0003'

score不在辅助索引B+树当中，会产生回表查询，所以该语句既会走辅助索引b+树，也会走聚集索引b+树

辅助索引B+树和聚集索引B+树都会加上X锁

（4）辅助唯一索引，查询未能命中： UPDATE students SET score = 100 WHERE no = 'S0008'

（5）辅助非唯一索引，查询命中：UPDATE students SET score = 100 WHERE name = 'Tom'；

可重复读的主键中不加间隙锁

（6）辅助非唯一索引，查询未命中： UPDATE students SET score = 100 WHERE name = 'John';

（7）无索引：UPDATE students SET score = 100 WHERE score = 22;

全表扫描，整个表加锁，并发性能很低

（8）聚集索引，范围查询： UPDATE students SET score = 100 WHERE id <= 20;

不需要回表查询，因为用的是聚集索引B+树

（9）辅助索引，范围查询：UPDATE students SET score = 100 WHERE age <= 23;

总结：加锁，主要看where的字段加了什么索引，如果是辅助索引，只会给辅助索引加间隙锁，不走索引则全表扫描，走索引得看explain走了什么索引

（10）修改索引值： UPDATE students SET name = 'John' WHERE id = 15;

并发的异常问题

隔离级别下的并发读异常：

脏读：事务（A）可以读到另外一个事务（B）中未提交的数据；也就是事务A读到脏数据；在读写分离的 场景下，可以将slave节点设置为 READ UNCOMMITTED；此时脏读不影响，在slave上查询并不 需要特别精准的返回值。（看到修改的数据，读到中间的数据很危险）

为什么会出现脏读，因为读未提交没有对读进行任何操作，升级隔离级别即可解决，

不可重复读：事务（A) 可以读到另外一个事务（B）中提交的数据；通常发生在一个事务中两次读到的数据是不 一样的情况；不可重复读在隔离级别 READ COMMITTED 存在。一般而言，不可重复读的问题是 可以接受的，因为读到已经提交的数据，一般不会带来很大的问题，所以很多厂商（如Oracle、 SQL Server）默认隔离级别就是READ COMMITTED；

脏读和不可重复读的区别：脏读读到的是一个事务内部修改但没有提交的数据，不可重复度读到的是一个事务已经提交过后（update）的数据

幻读： 两次读取同一个范围内的记录得到的结果集不一样；例如：以 name 为唯一键的表，一个事务中 查询 select * from t where name = '*** '; 不存在，接下来 insert into t(name) values ('mark'); 出现错误，此时另外一个事务也执行了 insert 操作；幻读在隔离级别 REPEATABLE READ 及以下存在；但是可以在 REPEATABLE READ 级别下通过读加锁（使用nextkey locking：lock in share mode）解决

不可重复读和幻读的区别：不可重复读是两次读取同一条记录，得到不一样的结果；而幻读是两 次读取同一个范围内的记录得到的结果集不一样（可能不同个数，也可能相同个数内容不一样，比 如删除一行后又添加新行）；不可重复读是因为其他事务进行了 update 操作，幻读是因为其他 事务进行了 insert 或者 delete 操作

丢失更新

脏读、不可重复读、幻读都是一个事务写，一个事务读，由于一个事务的写导致另一个事务读到了 不该读的数据；丢失更新是两个事务都是写；丢失更新分为提交覆盖和回滚覆盖；回滚覆盖数据库 拒绝不可能产生，重点关注提交覆盖，加锁即可解决

并发死锁

死锁：两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象； MySQL 中采用 wait-for graph （等待图-采用非递归深度优先的图算法实现）的方式来进行死锁检 测；
异常报错：deadlock found when trying to get lock;

相反加锁顺序死锁

这可以类比多线程中的死锁问题，线程中的死锁是每个线程循环等待别的线程释放锁。

不同表的加锁顺序相反或者相同表不同行加锁顺序相反造成死锁；其中相同表不同行加锁顺序相反 造成死锁有很多变种，其中容易忽略的是给辅助索引行加锁的时候，同时会给聚集索引行加锁；同 时还可能出现在外键索引时，给父表加锁，同时隐含给子表加锁；触发器同样如此，这些都需要视 情况分析；

解决：调整加锁顺序

锁冲突死锁

innodb 在 RR 隔离级别下，最常见的是插入意向锁与 gap 锁冲突造成死锁；主要原理为：一个事 务想要获取插入意向锁，如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞

解决：更换语句或降低隔离级别

如何避免死锁

尽可能以相同顺序来访问索引记录和表；

如果能确定幻读和不可重复读对应用影响不大，考虑将隔离级别降低为RC；

添加合理的索引，不走索引将会为每一行记录加锁，死锁概率非常大；

尽量在一个事务中锁定所需要的所有资源，减小死锁概率；

避免大事务，将大事务分拆成多个小事务；

大事务占用资源多，耗时长，冲突概率变高；

避免同一时间点运行多个对同一表进行读写的概率；