MySQL 事务和锁

1、事务

1.1、事务是什么

事务是代表一个或者一系列操作的最小逻辑单元，所有在这个逻辑单元内的操作要么全部成功，要么就全部失败，不存在任何中间状态，一旦事务失败那么所有的更改都会被撤消，一旦事务成功所有的操作结果都会被保存。

1.2、为什么要有事务

A 账户有 1000 元，B 账户有 1000 元，A 向 B 转账 500 元，流程如下：

1. 查询 A 账户余额，看金额是否有 500 元满足可以转账需求。
2. 满足需求后，然后从 A 账户扣款 500 元（此时 A 账户现在有 500元，B 账户现在有 1000 元）。
3. 最后向 B 账户增加 500 元（此时 A 账户现在有 500 元，B 账户现在有 1500 元，转账完成）。

• 在没有事务的情况下，如果在执行完第 2 步后，执行第 3 步系统发生异常失败了，那么最后的结果是 A 账户少了 500 而 B 账户也没收到转款，500 元凭空消失。还有就是如果在执行第 2 步失败了，然后继续执行第 3 步，那么最后的结果是 A 账户没有扣款而 B 账户收到 500 元转款，500 元凭空增加。
• 在有事务的情况下，在这个操作中，转账就是本次一系列操作的最小逻辑单元，只有3个操作都成功了才算转账成功，任何一个步骤失败都算整个转账操作失败，只要其中任意一个步骤执行失败都不会再往下执行，并对已经执行的数据变更进行恢复。

1.3、事物的特性

• 原子性（Atomicity）

一个事务必须是一系列操作的最小单元，这系列操作的过程中，要么整个执行，要么整个回滚，不存在只执行了其中某一个或者某几个步骤。

• 一致性（Consistency）

事务要保证数据库整体数据的完整性和业务的数据的一致性，事务成功提交整体数据修改，事务错误则回滚到数据回到原来的状态。

• 隔离性（Isolation）

隔离性是说两个事务的执行都是独立隔离开来的，事务之前不会相互影响，多个事务操作一个对象时会以串行等待的方式保证事务相互之间是隔离的。

• 持久性（Durability）

持久性是指一旦事务成功提交后，只要修改的数据都会进行持久化，不会因为异常、宕机而造成数据错误或丢失。

1.4、事务的并发问题

1. 脏读

A事务读取B事务尚未提交的更改数据，并在这个数据的基础上操作。如果恰巧B事务回滚，那么A事务读到的数据根本是不被承认的。来看取款事务和转账事务并发时引发的脏读场景：
在这个场景中，B希望取款500元而后又撤销了动作，而A往相同的账户中转账100元，就因为A事务读取了B事务尚未提交的数据，因而造成账户白白丢失了500元。在Oracle数据库中，不会发生脏读的情况。

2. 不可重复读

不可重复读是指 A事务读取了B事务已经提交的更改数据。假设A在取款事务的过程中，B往该账户转账100元，A两次读取账户的余额发生不一致：

3. 幻读

A事务读取B事务提交的新增数据，这时A事务将出现幻象读的问题。幻象读一般发生在计算统计数据的事务中，举一个例子，假设银行系统在同一个事务中，两次统计存款账户的总金额，在两次统计过程中，刚好新增了一个存款账户，并存入100元，这时，两次统计的总金额将不一致：

如果新增数据刚好满足事务的查询条件，这个新数据就进入了事务的视野，因而产生了两个统计不一致的情况。 不可重复读和幻读这两者确实非常相似。不可重复读 主要是说多次读取一条记录, 发现该记录中某些列值被修改过。而幻读主要是说多次读取一个范围内的记录，发现结果不一致。

解决幻读的方法

解决幻读的方式有很多，但是它们的核心思想就是一个事务在操作某张表数据的时候，另外一个事务不允许新增或者删除这张表中的数据了。解决幻读的方式主要有以下几种：

1. 将事务隔离级别调整为 SERIALIZABLE 。
2. 在可重复读的事务级别下，给事务操作的这张表添加表锁。
3. 在可重复读的事务级别下，给事务操作的这张表添加 Next-key Lock（Record Lock+Gap Lock）。

1.5、事物的四种隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

1. 读未提交
   读未提交是隔离级别最低的一种事务级别，在这种隔离级别下任何情况下都可能出问题。事务读不阻塞其他事务读和写，事务写阻塞其他事务写但不阻塞读。
2. 读已提交
   只能读取已经提交的数据，这是大多数数据库系统默认的隔离级别，但不是MySQL默认的。事务读不会阻塞其他事务读和写，事务写会阻塞其他事务读和写。
3. 可重复读
   同一个事务先后查询结果一样，Mysql InnoDB默认实现可重复读级别，此级别可能出现幻读。事务读会阻塞其他事务事务写但不阻塞读，事务写会阻塞其他事务读和写。
4. 可串行化
   最严格的隔离级别。在隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。这解决了一切并发问题，但会造成大量的等待、阻塞甚至死锁，使系统性能降低，一般采用Repeat Read和数据库锁相结合方式来替代它。

1.6、事务运行的三种模式

1. 自动提交事务

每一条单独的SQL语句都在其执行完成后进行自动提交事务，即执行 SQL 语句后就会马上自动隐式执行 COMMIT 操作。如果出现错误，则进行事务回滚至之前状态。SHOW VARIABLES LIKE 'autocommit' 查看状态。

2. 显式事务

通过指定事务开始语句来显式开启事务来作为开始，并由以提交命令或者回滚命令来提交或者回滚事务作为结束的一段代码就是一个用户定义的显式事务。以 begin transaction 开始，以 commit 或 rollback 结束。

3. 隐性事务

在隐式事务中，无需使用 begin transaction 来开启事务，每个SQL语句第一次执行就会开启一个事务，直到用 commit 来提交或者 rollback 来回滚结束事务。

2、锁

2.1、为什么要加锁

加锁是为了解决事务的隔离性问题，让事务之间相互不影响，每个事务进行操作的时候都必须先对数据加上一把锁，防止其他事务同时操作数据时候造成读取和存储的不正确，来保持数据库数据的一致性。

2.2、锁是基于什么实现的

数据库里面的锁是基于索引实现的，在Innodb中我们的锁都是作用在索引上面的，当我们的SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁）。

2.2、锁的分类

• 基于程序员角度分类：乐观锁、悲观锁。
• 基于锁的属性分类：共享锁、排他锁。
• 基于锁的粒度分类：表锁、行锁、记录锁、间隙锁、临键锁。
• 基于锁的状态分类：意向共享锁、意向排它锁。

2.2.1、基于程序员角度分类（乐观锁与悲观锁）

2.2.1.1、乐观锁

乐观锁不是数据库自带的，需要自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段，如果要对那条记录进行操作(更新)，则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1，如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

2.2.1.2、悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

2.2.2、基于锁的属性分类（共享锁与排它锁）

2.2.2.1、共享锁

又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

用法

在查询语句后面增加 LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁。

SELECT ... LOCK IN SHARE MODE

验证

打开一个查询窗口，进行共享锁测试，给ID等于100的记录添加共享锁，SQL如下：

SELECT * FROM yang WHERE id = 100 LOCK IN SHARE MODE

在加了共享锁后，打开一个新的查询窗口，进行共享锁测试

SELECT * FROM yang WHERE id = 100 LOCK IN SHARE MODE;-- 使用共享锁 查询到数据
SELECT * FROM yang WHERE id = 100 FOR UPDATE; -- 1205 - 使用排它锁 Lock wait timeout exceeded; try restarting transaction
SELECT * FROM yang WHERE id = 100;-- 不加锁 查询到数据

2.2.2.2、排它锁

又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

用法

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁

SELECT ... FOR UPDATE

验证

打开一个查询窗口，进行共享锁测试，给ID等于100的记录添加排它锁，SQL如下：

SELECT * FROM yang WHERE id = 100 FOR UPDATE

在加了排它锁后，打开一个新的查询窗口，进行排它锁测试

SELECT * FROM yang WHERE id = 100 LOCK IN SHARE MODE;-- 使用共享锁 Lock wait timeout exceeded; try restarting transaction
SELECT * FROM yang WHERE id = 100 FOR UPDATE; -- 1205 - 使用排它锁 Lock wait timeout exceeded; try restarting transaction
SELECT * FROM yang WHERE id = 100;-- 不加锁 查询到数据

2.2.2.3、小总结

• 共享锁就是多个事务只能读数据不能改数据。
• 对于排他锁的理解可能就有些差别，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。
• mysql InnoDB引擎默认的修改数据语句，update，delete，insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。
• 加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

2.2.3、基于锁的粒度分类（表锁、行锁、记录锁、间隙锁、临键锁）

2.2.3.1、表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问。

特点： 粒度大，加锁简单，容易冲突

2.2.3.2、行锁

行锁是对所有行级别锁的一个统称，比如下面说的记录锁、间隙锁、临键锁都是属于行锁， 行锁是指加锁的时候锁住的是表的某一行或多行记录，多个事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问。

特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高。

2.2.3.2.1、临键锁

临键锁也属于行锁的一种，并且它是 INNODB 的行锁默认算法，临键锁记录锁与间隙锁的并集，是mysql加锁的基本单位。

• 记录锁 + 间隙锁锁定的区间，左开右闭。

2.2.3.2.2、记录锁

记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。 锁定的是某一行一级，比如

SELECT * FROM yang WHERE id = 1 FOR UPDATE

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

• 锁住一条记录。
• id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。
• 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会变成成临键锁。

2.2.3.2.3、间隙锁

它是行锁中的一种，它锁定的是一个范围区间的索引。锁定的是记录与记录之间的空隙，间隙锁只阻塞插入操作，解决幻读问题。

• 锁定一个区间，左开右开。

案例说明

id	yang	c
0	0	0
5	5	5
10	10	10
15	15	15

以上表，id为主键，yang为普通索引，c为普通列。表名为ep

接下来我们将场景分为唯一索引等值查询、唯一索引范围查询、普通索引等值查询以及普通索引范围查询来分析下mysql如何加锁（数据库默认隔离级别下）

① 唯一索引等值查询（如果记录存在加临建锁，然后会退化为记录锁，该记录不存在加临建锁，然后会退化为间隙锁）

update ep set c = 1 where id = 5 唯一索引等值查询记录存在加临建锁(0,5]然后退化为记录锁5
update ep set c = 1 where id = 7 唯一索引等值查询记录不存在加临建锁(5,10]然后退化为间隙锁(5,10) 

② 唯一索引范围查询（范围内的查询语句，会产生间隙锁）

update ep set c = 1 where id >= 5 and id < 7

1.先来看语句查询条件的前半部分 id >= 5，因此，这条语句最开始要找的第一行是 id = 5，结合加锁的两个核心，需要加上
临建锁(0,5]。又由于 id 是唯一索引，且 id = 5 的这行记录是存在的，因此会退化成记录锁，也就是只会对 id = 5 这一行加锁。

2.再来看语句查询条件的后半部分id < 7，由于是范围查找，就会继续往后找第一个不满足条件的记录，也就是会找到 id = 10 这一行停下来，然后加临建锁(5, 10]，重点来了，但由于 id = 10不满足 id < 7，因此会退化成间隙锁，加锁范围变为(5, 10)。

所以，上述语句在主键 id 上的最终的加锁范围是 Record Lockid = 5 以及 Gap Lock (5, 10)。

③ 普通索引等值查询（如果记录存在，除了会加临建锁外，还额外加间隙锁，也就是会加两把锁，如果记录不存在，只会加临建锁，然后会退化为间隙锁，也就是只会加一把锁）

update ep set c = 1 where c = 5 普通索引等值查询记录存在加临建锁 (0,5]，又因为是非唯一索引等值查询，且查询的
记录 a= 5 是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是 (5,10)。
update ep set c = 1 where c = 7 普通索引等值查询记录不存在加临建锁(5,10]，但是由于查询的记录 a = 7 是不存在的，因此会退化为间隙锁，然后退化为间隙锁(5,10) 

④ 普通索引范围查询（普通索引范围查询， 不会退化为间隙锁和记录锁）

update ep set c = 1 where c < 11 普通索引上的 (0,15] 临键锁
update ep set c = 1 where c >= 10 普通索引上的 (5,10] 临键锁 (10,~] 临键锁
update ep set c = 1 where c >= 10 and c < 11 普通索引上的 (5,15] 临键锁

2.2.4、基于锁的状态分类（意向共享锁与意向排它锁）

2.2.4.1、意向共享锁

意向共享(IS)锁：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
SELECT column FROM table ... LOCK IN SHARE MODE;

2.2.4.2、意向排它锁

意向排他(IX)锁：事务有意向对表中的某些行加排他锁（X锁）

 -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
 SELECT column FROM table ... FOR UPDATE;

• 意向共享锁（IS）和 意向排他锁（IX）都是表锁。
• 意向锁是一种 不与行级锁冲突的表级锁。
• 意向锁是 InnoDB自动加的， 不需用户干预。

2.2.4.3、应用场景

假设，一个事务 A 给表的第 R 行加了写锁，另一个事务 B 想调整表结构，对整个表操作，给表加一个写锁，需要执行两个步骤：

1. 检查表是否被上了读锁或者写锁。
2. 遍历所有行，查看每一行是否被上了读锁或写锁。

步骤1可以很快执行，但是步骤2，则需要非常耗时，效率低下。
意向锁的作用，相当于就是在低层次资源是否使用，加了一个标识，提速步骤2的检查。
有了意向锁之后，事务 A 给表的第 R 行加了写锁时候，会先给表上意向锁。

事务B想对表上写锁，操作步骤如下：

1. 检查表是否被上了读锁或写锁。
2. 检查表是否被上了意向锁（因为所有行的读写之前，会先上意向锁），即可判断表中是否有任何一行被上了读锁或写锁。

步骤2在检查逻辑的时间复杂度，由于增加了意向锁之后，时间复杂度由O(n)，提升到了O(1)，效率大幅提升。