MySQL数据库三：MySQL事务和锁

一、ACID特性

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

1. 原子性

事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

每一个写事务，都会修改BufferPool，从而产生相应的Redo/Undo日志，在Buffer Pool 中的页被刷到磁盘之前，这些日志信息都会先写入到日志文件中。

如果 Buffer Pool 中的脏页没有刷成功，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写的数据不会丢失。如果脏页刷新成功，此时数据库挂了，就需要通过Undo来实现了。

2. 持久性

一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。

一个“提交”动作触发的操作有：binlog落地、发送binlog、存储引擎提交、flush_logs，check_point、事务提交标记等。这些都是数据库保证其数据完整性、持久性的手段。

同时，redo log在系统Crash重启之类的情况时，可以修复数据，从而保障事务的持久性

3. 隔离性

一个事务内部的操作及使用的数据对其他的并发事务是隔离。

InnoDB 支持的隔离性有 4 种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。锁和多版本控制（MVCC）技术就是用于保障隔离性的

4. 一致性

事务开始之前和事务结束之后，数据库的完整性限制未被破坏。

一致性包括两方面的内容，分别是约束一致性和数据一致性：

• 约束一致性：创建表结构时所指定的外键、Check、唯一索引等约束，可惜在 MySQL 中不支持Check 。
• 数据一致性：是一个综合性的规定，可以理解为数据的完整性，它是由原子性、持久性、隔离性共同保证的结果，而不是单单依赖于某一种技术。

二、事务的演进

1. 并发事务

所谓并发事务，是指在某一时间段内,多个事务同时存取相同的数据库数据。

事务并发处理可能会带来一些问题：

• 更新丢失：当两个或多个事务更新同一行记录，会产生更新丢失现象。可以分为回滚覆盖和提交覆盖
  • 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了
  • 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了
• 脏读：一个事务读取到了另一个事务修改但未提交的数据。
• 不可重复读：一个事务中多次读取同一行记录不一致，后面读取的跟前面读取的不一致。
• 幻读：一个事务中多次按相同条件查询，结果不一致。后续查询的结果和前面查询结果不同，多了或少了几行记录。

2. 排队

完全顺序执行所有事务的数据库操作，不需要加锁，简单的说就是全局排队。序列化执行所有的事务单元，数据库某个时刻只处理一个事务操作，特点是强一致性，处理性能低。

3. 排他锁

如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。

4. 读写锁

读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁。

读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁。

5. MVCC

a. MVCC的思想

MVCC（Multi Version Concurrency Control）被称为多版本控制，是基于Copy on Write的思想。MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。

在事务1开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影
响其他事务对此记录的读取，实现写和读并行。

目前MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。

在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）:

• 快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）
• 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。（select… for update 或lock in share mode，insert/delete/update）

b. MVCC的底层原理

MVCC是通过Undo日志中的版本链和ReadView一致性视图来实现的，在多个事务同时存在时，SELECT语句找寻到具体是版本链上的哪个版本，然后在找到的版本上返回其中所记录的数据的过程。

版本链：

在MySQL中，会默认为表后面添加三个隐藏字段

• DB_ROW_ID：行ID，MySQL的B+树索引特性要求每个表必须要有一个主键。如果没有设置的话，会自动寻找第一个不包含NULL的唯一索引列作为主键。如果还是找不到，就会在这个DB_ROW_ID上自动生成一个唯一值，以此来当作主键（该列和MVCC的关系不大）；
• DB_TRX_ID：事务ID，记录的是当前事务在做INSERT或UPDATE语句操作时的事务ID（DELETE语句被当做是UPDATE语句的特殊情况）；
• DB_ROLL_PTR：回滚指针，通过它可以将不同的版本串联起来，形成版本链。相当于链表的next指针。
  
  在并发事务过程中，会不断产生Undo日志，基于这几个隐藏字段，可以将Undo日志构建成链的结构，称为Undo日志中的版本链

ReadView：

ReadView一致性视图主要是由两部分组成：所有未提交事务的ID数组和已经创建的最大事务ID组成（实际上ReadView还有其他的字段，但不影响这里对MVCC的讲解）。比如：[100,200],300。事务100和200是当前未提交的事务，而事务300是当前创建的最大事务（已经提交了）。

当执行SELECT语句的时候会创建ReadView，但是在读取已提交和可重复读两个事务级别下，生成ReadView的策略是不一样的：读取已提交级别是每执行一次SELECT语句就会重新生成一份ReadView，而可重复读级别是只会在第一次SELECT语句执行的时候会生成一份，后续的SELECT语句会沿用之前生成的ReadView

那么SELECT语句是如何找寻到具体是版本链上的哪个版本呢？

对当前事务按照事务ID进行划分区间，其中min_id指向ReadView中未提交事务数组中的最小事务ID，而max_id指向ReadView中的已经创建的最大事务ID

• 首先从版本链中拿出最上面第一个版本的事务ID开始逐个往下进行比对
• 如果落在绿色区间（DB_TRX_ID < min_id）：这个版本比min_id还小（事务ID是从小往大顺序生成的），说明这个版本在SELECT之前就已经提交了，所以这个数据是可见的。或者（这里是短路或，前面条件不满足才会判断后面这个条件）这个版本的事务本身就是当前SELECT语句所在事务的话，也是一样可见的；
• 如果落在红色区间（DB_TRX_ID > max_id）：表示这个版本是由将来启动的事务来生成的，当前还未开始，那么是不可见的；
• 如果落在黄色区间（min_id <= DB_TRX_ID <= max_id）：这个时候就需要再判断两种情况：
  • 如果这个版本的事务ID在ReadView的未提交事务数组中，表示这个版本是由还未提交的事务生成的，那么就是不可见的；
  • 如果这个版本的事务ID不在ReadView的未提交事务数组中，表示这个版本是已经提交了的事务生成的，那么是可见的。

MVCC已经实现了读读、读写、写读并发处理，如果想进一步解决写写冲突，可以采用乐观锁和悲观锁。

三、事务隔离级别

上面提到在并发事务中会出现的“更新丢失”、”脏读”、“不可重复读”和“幻读”等并发事务问题，为了解决这些问题，MySQL数据库提供了以下 4 种事务隔离级别供用户选择。

1. 读未提交(Read Uncommitted)

解决了回滚覆盖类型的更新丢失，但可能发生脏读现象，也就是可能读取到其他会话中未提交事务修改的数据。

2. 读已提交(Read Committed)

只能读取到其他会话中已经提交的数据，解决了脏读。但可能发生不可重复读现象，也就是可能在一个事务中两次查询结果不一致。

3. 可重复读(Repeatable Read)

解决了不可重复读，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

不过理论上会出现幻读，简单的说幻读指的的当用户读取某一范围的数据行时，另一个事务又在该范围插入了新行，当用户在读取该范围的数据时会发现有新的幻影行。

4. 串行化(Serializable)

所有的增删改查串行执行。它通过强制事务排序，解决相互冲突，从而解决幻度的问题。这个级别可能导致大量的超时现象的和锁竞争，效率低下。

5. 事务隔离级别和锁的关系

• 事务隔离级别本质上是对锁和MVCC使用的封装，隐藏了底层细节。
• 应当首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁。
• MySQL默认隔离级别：可重复读，Oracle、SQLServer默认隔离级别：读已提交

6. MySQL隔离级别控制

查看MySQL当前数据库的事务隔离级别命令如下：

show variables like 'tx_isolation';
1

设置事务隔离级别可以如下命令：

set tx_isolation='READ-UNCOMMITTED';
set tx_isolation='READ-COMMITTED';
set tx_isolation='REPEATABLE-READ';
set tx_isolation='SERIALIZABLE';
1
2
3
4

四、锁机制

1. 锁分类

从操作的粒度分类：

• 表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。
• 行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。
• 页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。应用在BDB 存储引擎中。

从操作的类型分类：

• 读锁（S锁）：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（X锁）：排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁。
• 意向读锁(IS锁)、意向写锁(IX锁)：属于表级锁，S和X主要针对行级锁。在对表记录添加S或X锁之前，会先对表添加IS或IX锁。

从操作的性能分类：

• 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
• 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

2. 行锁原理

行锁又分为共享锁和排他锁。InnoDB行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

• RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）
• GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）
• Next-key Lock锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）

在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock

• select … from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁
• select … from lock in share mode语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
• select … from for update语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
• update … where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
• delete … where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
• insert语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

以RR隔离级别为例，对更新操作“update t1 set name=‘XX’ where id=10”，分析InnoDB 对不同索引的加锁行为：

• 主键加锁
  
  仅在id=10的主键索引记录上加X锁

• 唯一键加锁
  
  先在唯一索引id上加X锁，然后在id=10的主键索引记录上加X锁

• 非唯一键加锁
  
  对满足id=10条件的记录和主键分别加X锁，然后在(6,c)-(10,b)、(10,b)-(10,d)、(10,d)-(11,f)范围分别加Gap Lock。

• 无索引加锁
  
  表里所有行和间隙都会加X锁。（当没有索引时，会导致全表锁定，因为InnoDB引擎锁机制是基于索引实现的记录锁定）。

3. 悲观锁

悲观锁（Pessimistic Locking），是指在数据处理过程，将数据处于锁定状态，一般使用数据库的锁机制实现。

从广义上来讲，前面提到的行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴。

4. 乐观锁

乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。

乐观锁实现：

• 使用版本字段（version）
  先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。
• 使用时间戳（Timestamp）
  与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。

5. 死锁

死锁产生的四个条件：

• 互斥条件：一个资源每次只能被一个进程使用；
• 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
• 不剥夺条件：进程已获得的资源，在没使用完之前，不能强行剥夺；
• 循环等待条件：多个进程之间形成的一种互相循环等待资源的关系。

下面介绍几种常见的死锁场景：

场景1：

用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，产生死锁。

解决方法：

• 对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

场景2：
如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

解决方法：

• SQL语句中不要使用太复杂的关联多表的查询；
• 使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

场景3：
两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁

解决方法：

• 在同一个事务中，尽可能做到一次锁定所需要的所有资源；
• 按照id对资源排序，然后按顺序进行处理

场景4：
事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。

解决方法：

• 对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作；
• 使用乐观锁进行控制。

死锁的排查：

• 查看死锁日志
  • 通过show engine innodb status\G命令查看近期死锁日志信息
  • 使用explain查看下SQL执行计划
• 查看锁状态变量
  • 通过show status like’innodb_row_lock%‘命令检查状态变量
  • Innodb_row_lock_current_waits：当前正在等待锁的数量
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度
  • Innodb_row_lock_time_avg： 每次等待锁的平均时间
  • Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间
  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数