MySQL之事务与锁详解

一、数据库事务

1、什么是事务？

基本定义：事务是数据库管理系统执行过程中的一个逻辑单位，有一个有限的数据库操作序列构成。
简单来说就是：数据库最小的工作单元；包含一个或多个DML操作（包括insert、delete、update，其实单条DDL（create、drop）和DCL（grant、revoke）也有事务）。

2、哪些存储引擎支持事务

一个是InnoDB（这也是其能成为默认的存储引擎的一个重要的原因），另一个就是NDB。MyISAM和其他的存储引擎都不支持事务。

3、事务的四大特性

A：原子性，数据库的操作要么都成功要么都失败，不可能出现成功或者失败部分的情况。其中InnoDB中是通过undo log来实现异常回滚的；
C：一致性，数据库的完整性约束没有被破坏，事务执行前后都是合法的数据状态；
I：隔离性，多个事务对数据库的操作是互相不干扰的；
D：持久性，事务一旦提交，对数据库中数据的操作都是永久性的，不可能因为服务器宕机或者重启数据库而恢复到原来的状态。持久性是通过redo log和double write双写缓冲来实现的，我们操作数据库中数据的时候会先写到内存的buffer pool里面，同时写到redo log，如果刷盘之前出现了异常，在重启之后就可以可以读取redo log的内容，写入到磁盘，保证数据的完整性。前提是数据页本身并没有被破坏，是完整的，这个通过双写缓冲(double write)来保证。

4、如何开启、关闭事务

InnoDB默认是开启事务的，只有autocommit设置为false时才会关闭事务。当事物设置为关闭以后，可以手动用begin或者start transaction来手动开启。
关闭事务：commit或者rollback，还有一种就是客户端跟服务端断开连接的时候，事务也会结束。
注：事务一旦关闭，其所持有的锁就会释放。

5、事务可能产生的问题

脏读：读取到其他事务未提交的数据；
不可重复读：读取到其他事务修改以后已提交的数据导致前后两次读取的数据不一致；
幻读：第二次读取的时候发现比第一次多了数据，读取到其他事务插入的数据；
不可重复读和幻读的区别：幻读是读取到其他事务插入的数据，不可重复读是读取到其他事务修改和删除的数据。

6、事务隔离级别

事务隔离级别是用来解决读一致性的问题，不同的隔离级别其解决的问题不同。
未提交读（Read Uncommitted）：能读取到其他事务未提交的数据，因此并没有解决任何问题。
已提交读（Read Committed）：读取其他事务已提交的数据，解决了脏读，未解决不可重复读和幻读；这是Oracle默认的事物隔离机制，Oracle只支持读已提交和串行化。
可重复读（Repeatable Read）：在同一个事务里面多次读取同样的数据结果是一致的，该隔离级别解决了脏读、不可重复读，未解决幻读，但是InnoDB的不可重复读级别解决了幻读，下面会将具体原因。这是MySQL默认的事物隔离级别。
串行化（Serializable）：所有对数据库的事物操作都是串行化的，能解决脏读、不可重复读、幻读，但是几乎没有数据库会使用这个隔离级别。

二、数据库锁

事物隔离级别能解决读一致性的问题，其使用的大致有以下两种方案，MVCC（多版本并发控制）和LBCC（基于锁的并发控制）。

1、MVCC机制（多版本并发控制）

MVCC的核心思想：建立数据的快照，我可以查在我这个事务开始之前已经存在的数据，即使它后面被其他事务删除或者修改了，在我这个事务之后新增的数据是查不到的。

其实InnoDB为每行记录都实现了两个隐藏字段：
DB_TRX_ID(6字节)：事务id，插入、删除或者更新行的最后一个事务的事物id，事务id是自增的；
DB_ROLL_PTR(7字节)：回滚指针，更新或者删除操作时会记录被修改的那个行对应的事物id；

我在网上找到一个别人画的图，有具体的讲解，有兴趣的可以去看看：https://www.processon.com/view/link/5d29999ee4b07917e2e09298

MVCC的查找规则：只能查找创建时间小于等于当时事物ID的数据和删除时间大于当前事物ID的行（也就是在我之前删除的，我现在还能看到）。

在InnoDB中，MVCC是通过Undo log来实现的，且MVCC和锁是协同使用的并不是互斥的；
在其他数据库中例如Oracle中都有对MVCC的实现。

2、LBCC（基于锁的并发控制）

大致可以把锁分为行级别锁、表级别锁以及锁的算法。行级锁有共享锁、排它锁，表级锁有意向共享锁、意向排它锁，锁的算法分为记录锁、间隙锁、临键锁。

2.1、锁的粒度

根据锁的数据不同可以大致分为表锁和行锁，表锁顾名思义是锁一张表，行锁顾名思义是锁一行数据。
锁定粒度：表锁>行锁
加锁效率：表锁>行锁，行锁需要扫表检索行数据然后再加锁，效率低
冲突概率：表锁>行锁
并发性能：表锁<行锁，高并发下性能低
InnoDB既支持表锁又支持行锁，默认使用行锁；MyISAM只支持表锁

2.1、表锁

表锁分为意向排它锁和意向共享锁。意向锁并不是我们直接使用的，而是数据库自己维护的。
意向排它锁是当我们使用行锁中的排它锁时数据库会自动给这张表上加一个意向排它锁；
意向共享锁是当我们使用行锁中的共享锁时数据库会自动给这张表加上一个意向共享锁。

其实意向锁相当于是一个标识，当我们要给一个表加表锁时前提是表中的行并没有被加上锁，如果没有意向锁，我们就得扫全表看有没有行被锁住了，锁住就无法加表锁了，如果有了意向锁，当使用行锁时就会自动给表加上一个意向锁，这样就不用再扫全表找到对应的行，如果发现有意向锁就代表其中有行的数据被事务操作就直接加表锁失败。

2.2、行锁

2.2.1 共享锁

共享锁又叫读锁，可以与其他事务共享一把读锁。不要在加了读锁以后去写数据，不然可能会出现死锁的情况。
使用select… lock in share mode;的方式手工加上一把锁
事务结束就会释放锁

2.2.2 排它锁

只要一个事务获取了一行数据的排它锁，其他事务就无法再加共享锁和排它锁了。
排它锁的加锁的方式有两种，一种是自动加锁，我们在操作数据的时候包括增删改都会默认加上排它锁，第二种是手工加锁，使用select … for update。
事务结束就会释放锁

2.3、锁的算法

例如有一个表，主键为id，其中有四条数据为别为id=1，4，7，10

2.3.1、记录锁

记录锁是精确锁某个行记录。
使用场景：当我们使用等值查询能精确匹配到一条记录是就会使用行锁，例如查询id=4，锁的就是id=4这行的记录。

2.3.2、间隙锁

间隙锁锁的是一个开区间，一个左开右开的区间，间隙锁会把表根据数据分为不同的区间，每次加锁都是锁区间。
使用场景：当查询的数据不存在表中中，无论是等值查询还是范围查询，都是使用的间隙锁。
例如查找id=5的数据，那么就会锁住(4,7)这个区间。当查找数据为id>10时，(10，+∞)都会被锁住。当其他事务使用相同的间隙锁查询数据不冲突的，只有当其他事物插入该区间才会被阻塞。

注：间隙锁只存在于RR（可重复读）中。如果要关闭间隙锁，把事物隔离级别设置为RC且把innodb_locks_unsafe_for_binlog 设置为 ON即可。这种情况下除了外检约束和唯一性检查会加间隙锁，其他情况都不会使用间隙锁了。

2.3.3、临键锁

临键锁锁的也是区间，不过其锁的区间是一个左开右闭的区间且是多个区间，包含最后一个key的下一个区间。
使用场景：当我们使用了范围查找，不仅命中了Record记录还包含了Gap间隙锁，这种情况使用的就是临键锁。临键锁是MySQL中默认的行锁算法。
例如查询5<id<9，那么就会锁住住(4,7]、(7,10]、(10,+∞)，因为9在7和10的区间，临键锁也会锁住下一个区间。

InnoDB的RR能解决幻读就是基于临键锁，其能锁住下一个区间，导致其他事务无法插入。

注：
1、当唯一性索引时等值查询查到一条记录，会退化成记录锁；当没有匹配到任何记录时退化成间隙锁。
2、并不是只有主键为整型才会使用行锁的算法，字符类型也可以，字符类型使用阿斯克码的顺序来排序；

2.4、行锁的原理

上面一直说的行锁，锁的真的是一行数据吗？其实行锁锁的是索引。
1、可能有的人很疑惑，如果一张表没有加索引怎么办？
1）如果加了主键索引，InnoDB会选择主键作为聚集索引；
2）如果没有定义显式的主键，那么InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引；
3）如果也没有这样的唯一索引，那么InnoDB会选择内置一个6字节的ROWID作为隐藏的聚集索引，它会随行记录的写入而逐渐递增。
注：锁表就是没有使用索引，进行全表扫描然后把每一个隐藏的聚集索引都锁住了。
2、为什么有时通过唯一索引给数据行加锁，主键索引也会被锁住？
其实非主键索引也就是唯一索引的叶子节点存储的是其索引值以及主键的值，通过叶子节点上的主键值找到主键索引，所以主键会被锁住，这是在使用索引的那篇文章讲到过。

三、数据库事务和锁协同使用总结

1、分析一下四个事务隔离级别中多版本并发控制和锁的协同使用：
读未提交：不加锁，直接读。
读已提交：普通select使用的是快照，底层用的MVCC；加锁的select(select …for update/for share mode)会使用记录锁，因为RC没有间隙锁。
可重复读：普通select使用的是快照，底层使用的MVCC；加锁的select(select …for update/for share mode)会使用记录锁、间隙锁、临键锁。
串行化：所有的select都会隐式转换为select … for share mode，跟insert，update，delete互斥。

2、锁什么时候会释放？
事务结束（commit、rollback）、客户端断开连接

3、死锁
如果事物一直不释放锁，别的事物如果一直等待下去会导致死锁，其实MySQL有自己的超时等待时间，默认是50s，如果50s还拿不到锁那么该事务就会不再等待。

如何避免死锁呢？
1）操作多张表时尽量以相同的顺序来访问，避免形成等待环路；
2）尽量使用索引访问数据，避免没有where条件的查询，因为会扫全表锁全表；
3）尽量大事务化成小事务，免得锁太多的表；
4）尽量使用等值查而不是范围查询命中记录，避免间隙锁对并发的影响。