Mysql 数据库锁与事务

关于数据库事务

事务是数据库管理系统（DBMS）执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

 

事务的产生,其实是为了当应用程序访问数据库的时候,事务能够简化我们的编程模型,不需要我们去考虑各种各样的潜在错误和并发问题.

可以想一下当我们使用事务时,要么提交,要么回滚,我们不会去考虑网络异常了,服务器宕机了,同时更改一个数据怎么办对吧?

因此事务本质上是为了应用层服务的.而不是伴随着数据库系统天生就有的.

 

 

事务具备以下特性：

原子性（Atomic）：

事务所包含的所有操作，要么全部成功，要么全部失败。如果事务失败，要进行回滚（mysql的InnoDB中，以UndoLog作为实现）

回滚实际上是一个比较高层抽象的概念，大多数DB在实现事务时，是在事务操作的数据快照上进行的（比如，MVCC），并不修改实际的数据，如果有错并不会提交，所以很自然的支持回滚。

而在其他支持简单事务的系统中，不会在快照上更新，而直接操作实际数据。可以先预演一边所有要执行的操作，如果失败则这些操作不会被执行，通过这种方式很简单的实现了原子性。

 

一致性（Consistency）：

一致性指的是，事务必须是使数据库从一个一致性状态变到另一个一致性状态。（而这样的一致性，在我看来并不一定指的是业务一致性（自定义），也是指数据库自身的完整性约束）

事务的一致性决定了一个系统设计和实现的复杂度，也导致了事务的不同隔离级别。

 

事务可以不同程度的一致性：

强一致性：读操作可以立即读到提交的更新操作。

弱一致性：提交的更新操作，不一定立即会被读操作读到，此种情况会存在一个不一致窗口，指的是读操作可以读到最新值的一段时间。

最终一致性：是弱一致性的特例。事务更新一份数据，最终一致性保证在没有其他事务更新同样的值的话，最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生，不一致窗口的大小依赖于：通信延迟，系统负载等。

其他一致性变体还有：

单调一致性：如果一个进程已经读到一个值，那么后续不会读到更早的值。

会话一致性：保证客户端和服务器交互的会话过程中，读操作可以读到更新操作后的最新值。

 

隔离性（ Isolation ）：

指的是并发场景下的相互不干扰。

而并发会导致的问题有：

脏读：事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。

不可重复读：在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这回导致锁竞争加剧，影响性能。另一种方法是通过MVCC可以在无锁的情况下，避免不可重复读。

幻读：在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读是由于并发事务增加记录导致的，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。（不懂为啥，大家认为的幻读，更多指的是新增和删除数据）

 

持久性（Durability）：

指的是事务一旦完成（Commit/RollBack）,这些数据就是存在的，不因任何外力(服务器宕机)而发生变化

 

相关参考： https://www.zhihu.com/question/31346392/answer/59815366

 

不得不说的是，原子性，隔离性，持久性是为了一致性而做准备。AID本身是数据库特性，而C是依赖于业务层。C是目的，AID是手段

 

Mysql日志

在mysql的Innodb中，相关的事务操作特性是可以通过日志来解决的。

 

Mysql的日志分为很多种，其中分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）

其中redo log，undo log以及Binlog相对来说比较重要

 

binlog 二进制日志是server层的，主要是做主从复制，时间点恢复使用

redo log 重做日志是InnoDB存储引擎层的，用来保证事务安全（保证了事务的持久性，一致性）

undo log 回滚日志保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读（保证了事务的原子性）

 

Redo Log：

记录的是事务还没有提交后的每一个变化的值。必须要说明的是，事务未提交，同时他是先写缓存，再定时同步（fsync）到磁盘。

它本身保存是一个物理日志

 

Undo Log：

事务开始前，将他的数据生成到Unlog中，也就是修改前的数据。

它保存是逻辑日志，是数据的前一个版本。

 

BinLog:

只会在日志提交后，一次性记录执行过的事务中的sql语句以及其反向sql(作为回滚用)，

保存的是逻辑日志,执行的sql语句

 

假设有2个数值，分别为A和B，值为1，2

1 start transaction;

2 记录 A=1 到undo log;

3 update A = 3；

4 记录 A=3 到redo log；

5 记录 B=2 到undo log；

6 update B = 4；

7 记录B = 4 到redo log；

8 将redo log刷新到磁盘

9 commit

 

在1-8的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响.

如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时redo log已经持久化

若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘

 

数据库并发引起的三个问题

脏读：

两个事务AB，A读取到某条数据的值，B去修改这条数据，但是B没有提交。A再次去读的时候，发现数据不对了。这样重复两次读到未提交的，不一致的数据，被称为脏读。

不可重复读：

两个事务AB，A读取到某条数据的值，B去修改或者删除这条数据，且B提交。A再次去读的时候，发现数据不对了。这样重复两次读到已提交（update，delete）的，不一致的数据，被称为不可重复读。

幻读：

两个事务AB，A读取到某条数据的值，B去新增这条数据，且B提交。A再次去读的时候，发现数据不对了。这样重复两次读到已提交（insert）的，不一致的数据，被称为幻读。

 

然后笔者发现很奇怪的是，很多人将幻读定义为insert和delete。

嗯，然后我看了一下SQL92标准（数据库规范）

很明显，人家的定义的P2是修改和删除

 

同样的，在定义完成这么几种，并发问题后，SQL92提出了4种数据库隔离级别来解决这种问题

 

 

 

第一个隔离级别叫做：Read Uncommitted（未提交读），一个事务可以读取到其他事务未提交的数据，会出现脏读，所以叫做 RU，它并没有解决任何的问题。

第二个隔离级别叫做：Read Committed（已提交读），也就是一个事务只能读取到其他事务已提交的数据，不能读取到其他事务未提交的数据，它解决了脏读的问题，但是会出现不可重复读的问题。

第三个隔离级别叫做：Repeatable Read (可重复读)，它解决了不可重复读的问题，也就是在同一个事务里面多次读取同样的数据结果是一样的，但是在这个级别下，没有定义解决幻读的问题。

最后一个就是：Serializable（串行化），在这个隔离级别里面，所有的事务都是串行执行的，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以它解决了所有的问题。

 

然后区别的是Mysql的InnoDB中，RR解决了幻读的问题，这也是Mysql用RR作为默认隔离级别的原因，毕竟串行化太影响效率了

 

而至于为什么，RR能够解决幻读问题，是因为Mysql本身的gap锁。这个待会再整理。

 

实现数据库隔离级别的有两大方案，分别是MVCC与锁（LBCC）

 

关于MVCC

MVCC核心为多版本控制并发。

它的作用为，我可以查到在事务开始前的数据，即使后面被删除或者修改，但是后续新增的数据我是无法查到的。

它的具体实现方式为：修改数据时，建立备份或者快照，后面读取相应快照

 

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

 

这里有两个关键的字段：DB_TRX_ID，DB_ROLL_PTR。这两个字段其实是InnoDB自身默认的，每一条记录都有这两个默认字段。

其中DB_TRX_ID，指的是插入或更新行的最后一个事务的事务 ID，可以理解为当前版本号

DB_ROLL_PTR,回滚指针，删除数据时记录的事务ID，可以理解为删除版本号

 

我们来具体看一下他的实现效果 以及图解

插入初始数据：

 

此刻 mvcc的版本号 应该如下：

 

这个时候 我开始做一个查询（此时事务ID变成2）

 

但是此刻我的事务还没有提交

 

查询出来的结果，必然是两条记录

 

现在我插入数据

 

此时 mvcc的版本号应该是这样的：

 

这个时候 我们回到刚刚的第二个事务（还没有结束事务，commit或者rollback或者关闭客户端当前界面），再进行一个查询

此刻能够看到的是依旧只有两个数据，因为我 只能查找创建时间小于等于当前事务 ID 的数据，所以新增的数据不展示。

 

第四个事务，我们去删除一条数据

 

 

那么这个时候记录的就是删除版本了，id为1的删除版本为当期事务4

 

那么我们回到刚刚的查询，依旧是这样

这是因为，查询到 删除时间大于当前事务 ID 的行（或未删除），所以刚刚被删除的数据我们能够查询到

 

那么最后我们再修改一次数据

 

此时的版本号如下，他是删除了原本的事务记录，又新增了新的事务记录。（当然实际再存储的时候并不是两条一样的主键）

 

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

此刻我们依旧只能查询到B员工，并查询不到B1员工

 

所以效果如下。

 

我们再整体看一下数据，

 

 

而从整体上来看MVCC确实实现了 再我事务查询过程中，不管后续的新增，删除，修改。我的数据保持不变

 

 

 

MySql InnoDB 锁的基本类型

 

官网把锁分成了 8 类。

所以我们把前面的两个行级别的锁（Shared and Exclusive Locks），和两个表级别的锁（Intention Locks）称为锁的基本模式。

后面三个 Record Locks、Gap Locks、Next-Key Locks，我们把它们叫做锁的算法，也就是分别在什么情况下锁定什么范围。

 

Shared and Exclusive Locks 指的是 共享和排他锁

Intention Locks  意向锁（意向共享锁/意向排他锁）

Record Locks 行锁

Gap Locks 间隙锁

Next-Key Locks 临键锁

Insert Intention Locks 插入意向锁

AUTO-INC Locks 自增锁

Predicate Locks for Spatial Indexes 空间索引的谓词锁

 

锁的粒度

锁分为行级别，表级别锁。InnoDb中两者均存在，MyISAM中只有表级别锁。

行锁和表锁的差异：

1.锁的粒度：行锁<表锁

2.锁的加锁效率：行锁<表锁。因为表锁的话，不需要遍历和寻找对应的记录数。

3.锁碰撞:行锁<表锁。锁的颗粒度越小，碰撞率越低

 

 

共享和排他锁（Shared and Exclusive Locks）：

官方文档 如下描述

这两种锁均为行级别锁，共享（S）锁允许持有锁，读取行的事务。  独占（X）锁允许持有锁，更新或删除行的事务。

 

同样的，官方文档中，接下去描述的业务场景如下

如果事务T1在r行持有S锁，则来自某些不同事务T2的对r行锁定的请求将按以下方式处理：

T2请求S锁可以立即被授予。其结果是，T1与T2都在r上持有S锁。T2请求X锁不能立即授予。

如果事务T1在r行拥有独占（X）锁，则不能立即批准某个不同事务T2对r上任一类型的锁的请求。相反，事务T2必须等待事务T1释放对r行的锁定。

 

我们可以理解为，读读可以并行，排他锁和其他任和锁互斥。

 

额 这并不是贴了两个一模一样的图 这是两个不同的事务

 

其实这四张图就是说明了 读锁之间可以公用一个事务，但是读写 写读 写写都不行

 

 

需要重点说明的是，共享锁/排他锁是行锁，与间隙锁无关，但一个事务在请求共享锁/排他锁时，获取到的结果却可能是行锁，也可能是间隙锁，也可能是临键锁，这取决于数据库的 隔离级别以及查询的 数据是否存在。

 

意向共享/排他锁（Intention Locks）

 

官网中，开头描述的就是 InnoDB为了实现多级别的锁，所以才整出来这个意向锁。

意向锁是表锁，当一个表的行记录已经被共享锁和排他锁所占据时，这张表上就会产生一个意向共享/排它锁

也就是说 意向锁，他其实是InnoDB默认自身实现的。而当事务发现这张表上已经有一个意向锁的时候，说明其他事务已经给这张表上某条数据 加上了对应的锁

 

意向锁 有两个作用：

1.它定义了不同级别的锁，实现了锁的多粒度（表锁） 

2.它提高了表锁的判断效率，如果我当有一个很大量级的表，这个时候去加一个表锁，再锁表之前，我们要先检查一遍所有的明细有没有行级别锁，而这样是耗费效率的。但是有了意向锁之后，可以直接去获取到这个锁来判断这张表有没有明细被锁

 

官网中，有以下描述

 

在事务可以获取表中某行的共享锁之前，它必须首先获取表中的IS锁或更高级别的锁。

在事务可以获取表中某行的排它锁之前，它必须首先获取该表的IX锁。

 

意向锁不会阻止除全表请求（例如LOCK TABLES ... WRITE）以外的任何内容。意向锁的主要目的是表明有人正在锁定表中的行，或者打算锁定表中的行。

 

行锁（Record Locks）

行锁始终是锁定的索引列，主要用于防止其他事务更新，删除或者插入对应的这条数据

若一个表中没有索引，mysql会有隐藏的列来作为它的索引列

 

 

间隙锁（Gap Locks）

间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁，它本身是一个开区间。

例如：SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 For UPDATE;在这个事务过程中，阻止插入（10,20）内的数据

 

这段话表明间隙锁在本质上是不区分共享间隙锁或互斥间隙锁的，而且间隙锁是不互斥的，即两个事务可以同时持有包含共同间隙的间隙锁。

这里的共同间隙包括两种场景：其一是两个间隙锁的间隙区间完全一样；

其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。

间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。也就是说间隙锁的应用场景包括并发读取、并发更新、并发删除和并发插入。

 

官网中有这样的说法：可以显式禁用间隙锁。如果您将事务隔离级别更改为READ COMMITTED，就会发生这种情况。 在这些情况下，搜索和索引扫描禁用间隙锁，并且仅用于外键约束检查和重复键检查。

也就是说，在RU和RC两种隔离级别下，即使你使用select ... in share mode或select ... for update，也无法防止幻读。因为这种场景下只会有行锁，而不会有间隙锁。

这也是默认隔离级别为RR的原因。

 

邻键锁(Next-Key Locks)

官网中的描述就是 临建锁是行锁+间隙锁。也就是一个左开右闭的区间（3,5]

 

假设一个索引包含值10、11、13和20。那么他的临建锁，有以下区间

 

(negative infinity, 10]  （10,11]  (11,13]  (13, 20]  (20, positive infinity)

 

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行。在这种情况下，InnoDB 使用邻键锁进行搜索和索引扫描，以防止幻象行。

 

 

其实，记录锁，间隙锁，邻键锁是可以放在一起看的 如下图：

若 查询的是where id = 1,4,7,10  它走的是记录锁，锁住的是当前索引记录的那条数据。表中其他的数据并不会受到影响

 

若 查询的数据不存在，where id = 6     where id > 4 and id < 7，没有命中任何一条record。那么它走的是间隙锁

需要注意的是，查询记录为空，走的也是间隙锁

间隙锁阻塞的是insert，间隙锁与间隙锁之期间不冲突

 

若使用了范围查询，不仅命中了record还包含了Gap间隙。在这种情况下我们使用的就是临键锁，它是 MySQL 里面默认的行锁算法，相当于记录锁加上间隙锁。

比如 where id > 5 and  id <9 就是命中了7这个record 同时锁住了（4,7] (7,10]这两个区间

 

这里是有说法的，我锁住的是原本的最大的范围区间

如果 where id > 8 and id <=10  这个时候我锁的区间为 （7,10]和（10，+∞）

 

隔离级别

 

 

RU：

不加锁，所以他什么都做不了

RC：

RC 隔离级别下，普通的 select 都是快照读，使用 MVCC 实现。

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

除了两种特殊情况——外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会使用间隙锁封锁区间。

所以 RC 会出现幻读的问题。

RR

RR 隔离级别下，普通的 select 使用快照读(snapshot read)，底层使用 MVCC 来实现。

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

Serializable

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

 

关于RC和RR的选择：
 

RC 和 RR 主要有几个区别：

1、 RR 的间隙锁会导致锁定范围的扩大。

2、 条件列未使用到索引，RR 锁表，RC 锁行。

3、 RC 的“半一致性”（semi-consistent）读可以增加 update 操作的并发性。在 RC 中，一个 update 语句，如果读到一行已经加锁的记录，此时 InnoDB 返回记录最近提交的版本，由 MySQL 上层判断此版本是否满足 update 的 where 条件。若满足(需要更新)，则 MySQL 会重新发起一次读操作，此时会读取行的最新版本(并加锁)。

 

其实除了MySQL默认采用RR隔离级别之外，其它几大数据库都是采用RC隔离级别。

但是他们的实现也是极其不一样的。Oracle仅仅实现了RC 和 SERIALIZABLE隔离级别。默认采用RC隔离级别，解决了脏读。但是允许不可重复读和幻读。其SERIALIZABLE则解决了脏读、不可重复读、幻读。