MySQL事务原理分析

---

一、事务是什么？

事务是访问并更新数据库各种数据项的一个程序执行单元。
目的
事务将数据库从一种一致性状态转换为另一种一致性状态；
这里的一致性状态的意思是
组成：事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成；
mysql InnoDB单条语句本身默认是一条隐含的事务，不需要手动提交事务，多条语句需要手动提交事务与开启事务
特征
在数据库提交事务时，可以确保要么所有修改都已经保存，要么所有修改都不保存；
在 MySQL innodb 下，每一条语句都是事务；可以通过 set autocommit = 0; 设置当前会话手动提交；

事务控制语句

-- 显示开启事务 
START TRANSACTION | BEGIN 
-- 提交事务，并使得已对数据库做的所有修改持久化 
COMMIT 
-- 回滚事务，结束用户的事务，并撤销正在进行的所有未提交的修改 
ROLLBACK 
-- 创建一个保存点，一个事务可以有多个保存点 
SAVEPOINT identifier 
-- 删除一个保存点 
RELEASE SAVEPOINT identifier 
-- 事务回滚到保存点 
ROLLBACK TO [SAVEPOINT] identifier

二、ACID特性

原子性（A）

事务操作要么都做（提交），要么都不做（回滚）；事务是访问并更新数据库各种数据项的一个程序执行单元，是不可分割的工作单位；通过 undolog 来实现回滚操作。undolog 记录的是事务每步具体操作，当回滚时，回放事务具体操作的逆运算；
关于回滚，我们举个例子来说明一下，比如说我们要插入一条数据
insert into table()
undolog就记录了我们这一步操作，一旦我们执行rollback，我们就会在undolog当中去执行相反的操作，也就是delete from where 。。。就把之前插入的删掉，回到数据库最开始的状态
undolog存在我们的共享表空间当中

隔离性（I）

事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，并发事务之间不会相互影响，设定了不同程度的隔离级别，通过适度破环一致性，得以提高性能；通过 MVCC和锁来实现；MVCC时多版本并发控制，主要解决一致性非锁定读，通过记录和获取行版本，而不是使用锁来限制读操作，从而实现高效并发读性能。
锁用来处理并发 DML 操作；数据库中提供粒度锁的策略，针对表（聚集索引B+树）、页（聚集索引B+树叶子节点）、行（叶子节点当中某一段记录行）三种粒度加锁；
每条连接都会有一个线程，我们始终会有同时执行的语句，互相不影响（加锁）
关于隔离级别在后边会详细地去讲，隔离级别越高，并发性能越低，级别越低，并发性能越高
mvcc，同时并发执行访问同一个临界资源，如果不加锁，会去记录一个版本号的方式，比如说read on copy，为了避免读的时候不去加锁。

持久性（D）

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

一致性（C）

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

三、隔离级别

ISO 和 ANIS SQL 标准制定了四种事务隔离级别的标准，各数据库厂商在正确性和性能之间做了妥协，并没有严格遵循这些标准；MySQL innodb默认支持的隔离级别是 REPEATABLE READ；写就是DML操作

READ UNCOMMITTED

读未提交；该级别下读不加锁，写加排他锁，写锁在事务提交或回滚后释放锁；

READ COMMITTED

读已提交（RC）；从该级别后支持 MVCC (多版本并发控制)，也就是提供一致性非锁定读；此时读取操作读取历史快照数据；该隔离级别下读取历史版本的最新数据，所以读取的是已提交的数据；
sqlserver与orical默认的是这个隔离级别

REPEATABLE READ

可重复读（RR）；该级别下也支持 MVCC，此时读取操作读取事务开始时的版本数据；
如果在工作中，我们遇到读异常与死锁的问题，第一步我们就需要知道隔离级别是什么，默认的情况下就是用的该隔离级别。
mysql默认的是这个隔离级别

SERIALIZABLE

可串行化；该级别下给读加了共享锁；所以事务都是串行化的执行；此时隔离级别最严苛；

-- 设置隔离级别 
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
-- 或者采用下面的方式设置隔离级别 
SET @@tx_isolation = 'REPEATABLE READ'; SET @@global.tx_isolation = 'REPEATABLE READ'; 
-- 查看全局隔离级别 
SELECT @@global.tx_isolation; 
-- 查看当前会话隔离级别 
SELECT @@session.tx_isolation;
SELECT @@tx_isolation; 

-- 手动给读加 S 锁 
SELECT ... LOCK IN SHARE MODE; 
-- 手动给读加 X 锁 
SELECT ... FOR UPDATE; 
-- 查看当前锁信息 
SELECT * FROM information_schema.innodb_locks;

mvcc

我们看到这样一张图，下边的空白框框和带有x locked的框框都是每一行的数据，带有x locked的那个，我们有一个事务去修改这一行，其他的在读，当我们读到那个正在写的那一行数据时，我们发现它已经加了x锁了，那么我们就去读它的那个块照，也就是那个snapshot，每一次事务提交的时候都会产生一个快照，而不是因为这里锁住了，我就直接等待。如果我们这里在写，我们去读要等待的话，并发性就会很低。我们的mvcc就是提高我们的读的并发性能，你可以锁住，但是我可以读你的历史数据，这就是快照读。

快照读与当前读的区别就是，快照读后边的语句不加参数，当前读语句后边要加参数，也就是加相应的锁

四、锁

undolog redolog

锁

锁机制用于管理对共享资源的并发访问；用来实现事务的隔离级别 ；
锁类型
共享锁和排他锁都是行级锁；MySQL当中事务采用的是粒度锁；针对表（B+树）、页（B+树叶子节点）、行（B+树叶子节点当中某一段记录行）三种粒度加锁；
意向共享锁和意向排他锁都是表级别的锁；
myisam支持表锁，不可能出现异常，因为这是一个表锁
那么这些并发死锁与并发读异常是怎么造成的呢，是行锁造成的
我们就需要了解行锁

共享锁

事务读操作加的锁；对某一行加锁；
在 SERIALIZABLE 隔离级别下，默认帮读操作加共享锁；
在 REPEATABLE READ 隔离级别下，需手动加共享锁，可解决幻读问题；
在 READ COMMITTED 隔离级别下，没必要加共享锁，采用的是 MVCC； 在 READ UNCOMMITTED 隔离级别下，既没有加锁也没有使用 MVCC；

排他锁（X）

事务删除或更新加的锁；对某一行加锁；这里边没有插入，插入的话还需要再加其他的锁，然后再加x锁
在4种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁；

意向共享锁（IS）

对一张表中某几行加的共享锁；
这个锁的作用是告诉其他事务，这个表正在被访问，有事务正在操作

意向排他锁（IX）

对一张表中某几行加的排他锁；
目的：为了告诉其他事务，此时这条表被一个事务在访问；作用：排除表级别读写锁 （全面扫描加锁）；

锁的兼容性

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

锁的算法

Record Lock
记录锁，单个行记录上的锁；
Gap Lock（重点）
间隙锁，锁定一个范围，但不包含记录本身；全开区间；REPEATABLE READ级别及以上支持间隙锁；可重复读独有的锁
如果 REPEATABLE READ 修改 innodb_locks_unsafe_for_binlog = 0 ，那么隔离级别相当于退化为 READ COMMITTED；
– 查看是否支持间隙锁，默认支持，也就是 innodb_locks_unsafe_for_binlog = 0; SELECT @@innodb_locks_unsafe_for_binlog;
Next-Key Lock
记录锁+间隙锁，锁定一个范围，并且锁住记录本身；左开右闭区间；是可重复读独有的锁
Insert Intention Lock
插入意向锁，insert操作的时候产生；在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。提升间隙插入的并发性能
假设有一个记录索引包含键值4和7，两个不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。
AUTO-INC Lock（AI锁）
自增锁，是一种特殊的表级锁，发生在 AUTO_INCREMENT 约束下的插入操作；采用的一种特殊的表锁机制（较低概率造成B+树分裂）；完成对自增长值插入的SQL语句后立即释放；在大数据量的插入会影响插入性能，因为另一个事务中的插入会被阻塞；从MySQL 5.1.22开始提供一种轻量级互斥量的自增长实现机制，该机制提高了自增长值插入的性能；
其中在工作中有大约80%的死锁是在第二行Insert intention造成的
横向：表示已经持有的锁；纵向：表示正在请求的锁；
这两句需要背下来：一个事务已经获取了插入意向锁，对其他事务是没有任何影响的；一个事务想要获取插入意向锁，如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞；这个是重点，死锁之源；

锁的对象

行级锁是针对表的索引加锁；索引包括聚集索引和辅助索引；
表级锁是针对页或表进行加锁；
重点考虑 InnoDB 在 read committed 和 repeatable read 级别下锁的情况；
如下图 students 表作为实例，其中 id 为主键，no（学号）为辅助唯一索引，name（姓名）和age（年龄）为二级非唯一索引，score（学分）无索引。

分别讨论
聚集索引，查询命中： UPDATE students SET score = 100 WHERE id = 15;

聚集索引，查询未命中： UPDATE students SET score = 100 WHERE id = 16;我们可以看到Repeatable Read加了一个GAP锁

辅助唯一索引，查询命中： UPDATE students SET score = 100 WHERE no = ‘S0003’;这里要走回表查询，更新操作会自动加x锁

辅助唯一索引，查询未命中： UPDATE students SET score = 100 WHERE no = ‘S0008’;把后边的全锁了
辅助非唯一索引，查询命中： UPDATE students SET score = 100 WHERE name = ‘Tom’;这里的间隙锁只在我们的辅助索引当中加了

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

无索引： UPDATE students SET score = 100 WHERE score = 22;
没有索引的情况下是全表扫描，全表扫描导致并发性非常低，相当于给所有行加锁

聚集索引，范围查询： UPDATE students SET score = 100 WHERE id <= 20;
这里不需要回表查询，因为这里使用的就是聚集索引B+树
这里为什么这里会有30这一项呢，特殊情况下，不讨论，有时候加（20，30]

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

修改索引值，UPDATE students SET name = ‘John’ WHERE id = 15;
先走聚集索引

五、并发异常

读异常（面试点）

读异常的细微差异，怎么解决的，加什么锁
隔离级别机制+解决方案

脏读

事务（A）可以读到另外一个事务（B）中未提交的数据（例如事务B回滚了）；也就是事务A读到脏数据；在读写分离的场景下，可以将slave节点设置为 READ UNCOMMITTED；此时脏读不影响，在slave上查询并不需要特别精准的返回值。
我们举一个例子，先把默认的隔离级别都是可重复读，修改为读未提交
开启事务，先把A的钱减去100，再把B的钱加上100，也就是A转B100块钱。
我们查询A的账户，这时是900块钱，B是1000块钱，一共是1900块钱
我们可以看出来我们的A事务负责写，B读到了1900，但是我们知道这俩相互转，钱的和依旧是2000，就是因为在事务的执行过程当中，读到了别人修改的数据，显而易见这就是脏读的问题。在读未提交的级别下，读未做任何操作，造成我们读到了不该读的数据。
我们怎么修改呢，我们只需要把隔离级别修改一下，升级隔离级别，读已提交
我们解决脏读的问题，只需要把隔离级别升高就行了

不可重复读

事务（A) 可以读到另外一个事务（B）中提交的数据；通常发生在一个事务中两次读到的数据是不一样的情况；不可重复读在隔离级别 READ COMMITTED 存在。一般而言，不可重复读的问题是可以接受的，因为读到已经提交的数据，一般不会带来很大的问题，所以很多厂商（如Oracle、SQL Server）默认隔离级别就是READ COMMITTED；
我们说一个例子，关于两个事务的隔离级别都是读已提交，两个都开启事务，事务B查询A的钱，在A事务当中的A减去100块钱，然后提交，事务B又来查询
A的账户，两次的钱都不一样，因为B读到的数据时事务A已经提交的，隔离级别都是读已提交。这就是不可重复读。
解决办法就是，把隔离级别提升到可重复读，也就是A修改的完后，不影响B的第二次读，B第二次读都是begin之前的数据，不管A中间有多少提交。

幻读

幻读是两次读取同一个范围内的记录得到的结果集不一样（可能不同个数，也可能相同个数内容不一样，比如删除一行后又添加新行）强调结果集合
两次读取同一个范围内的记录得到的结果集不一样；例如：以 name 为唯一键的表，一个事务中查询 select * from t where name = ‘mark’; 不存在，接下来 insert into t(name) values (‘mark’); 出现错误，此时另外一个事务也执行了 insert 操作；幻读在隔离级别REPEATABLE READ 及以下存在；但是可以在 REPEATABLE READ 级别下通过读加锁（使用nextkey locking）解决；
A事务插入一条数据，导致B事务在两次查询时得出的结果集不一样
，所以我们需要锁定这个范围，在编号3那一行加上lock in share mode，左边加x锁，右边加s锁，在commit的时候，这个锁才会释放，我们就可以解决这个问题事务开始之前加锁，持续锁，事务提交或者回滚的时候，释放锁

丢失更新

脏读、不可重复读、幻读都是一个事务写，一个事务读，由于一个事务的写导致另一个事务读到了不该读的数据；
丢失更新是两个事务都是写；
丢失更新分为提交覆盖和回滚覆盖；回滚覆盖数据库拒绝不可能产生，重点关注提交覆盖；
下图都是对A账户的钱进行操作，A查询是1000，B修改成900提交了，A回滚为1000，这就是回滚覆盖，但是呢，数据库针对这种情况做了特殊处理，所有的数据库都不会出现回滚覆盖
提交覆盖，这是会出现的
例如下边这个，两个事务都对A账号的钱进行操作，两个隔离级别都是重复读，事务B先改成1100提交后，事务A再改成900然后提交，这就是提交覆盖
解决方案是加锁
对左边加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；
添加合理的索引，不走索引将会为每一行记录加锁，死锁概率非常大；
尽量在一个事务中锁定所需要的所有资源，减小死锁概率；
避免大事务，将大事务分拆成多个小事务；大事务占用资源多，耗时长，冲突概率变高；
避免同一时间点运行多个对同一表进行读写的概率；