mysql 事务和锁知多少

痛定思痛。 2022-12-07 15:14 166阅读 0赞

## 前言 ##

mysql 事务特性、隔离级别，事务控制等等，面试的时候再也不怕啦。

## ACID 特性 ##

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

### 原子性 ###

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

每一个写事务，都会修改 BufferPool，从而产生相应的Redo/Undo日志，在Buffer Pool 中的页被刷到磁盘之前，这些日志信息都会先写入到日志文件中，如果 Buffer Pool 中的脏页没有刷成功，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写的数据不会丢失。如果脏页刷新成功，此时数据库挂了，就需要通过Undo来实现了。

### 持久性 ###

指事务一旦提交，它对数据库中的数据修改时永久性的，后续操作不会对其有任何影响，不会丢失。

如下图所示，一个“提交”动作触发的操作有：binlog落地、发送binlog、存储引擎提交、flush\_logs，check\_point、事务提交标记等。这些都是数据库保证其数据完整性、持久性的手段。

![format_png][]

MySQL的持久性也与WAL技术相关，redo log 在系统 Crash 重启之类的情况时，可以修复数据，从而保障事务的持久性。通过原子性可以保证逻辑上的持久性，通过存储引擎的数据刷盘可以保证物理上的持久性。

### 隔离性 ###

是指一个事务的执行，不会被其他事务干扰，一个事务的内部操作及使用的数据对其他的并发事务是隔离的。

### 一致性 ###

是指事务在开始前和事务结束后，数据库的完整性限制没有被破坏。一致性分为约束一致性和数据一致性。

*  约束一致性：创建表结构时所指定的外键、check、唯一索引等约束
 *  数据一致性： 有原子性、持久性、隔离性共同保障的结果。

![format_png 1][]

ACID 及它们之间的关系如下图所示，4个特性中有3个与 WAL 有关系，都需要通过 Redo、Undo 日志来保证等。

WAL的全称为Write-Ahead Logging，先写日志，再写磁盘。

![format_png 2][]

## 事务控制 ##

### 事务并发 ###

事务并发处理可能会带来一些问题，比如：更新丢失、脏读、不可重复读、幻读等。

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

### 排它锁 ###

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

![image-20200905095258313][]

在整个事务1结束之前，锁是不会被释放的，所以，事务2必须等到事务1结束之后开始。

### 读写锁 ###

读和写操作：读读、写写、读写、写读。

读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，这样下面的两个事务就可以同时被执行了。

![format_png 3][]

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

### MVCC ###

多版本控制MVCC，也就是Copy on Write的思想。MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。

![format_png 4][]

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

**概念**

MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。如何生成的多版本？每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号，该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。

**MVCC实现原理**

MVCC 最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，不过目前MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。

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

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

为了让大家更直观地理解 MVCC 的实现原理，举一个记录更新的案例来讲解 MVCC 中多版本的实现。假设 F1～F6 是表中字段的名字，1～6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事务号和回滚指针，如下图所示

![format_png 5][]

具体的更新过程如下：

1、用排他锁锁定该行；记录 Redo log

2、把该行修改前的值复制到 Undo log，即图中下面的行；

3、修改当前行的值，填写事务编号，使回滚指针指向 Undo log 中修改前的行。

![image-20200905104556867][]

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

## 事务隔离级别 ##

MySQL数据库是通过事务隔离级别来解决的，数据库系统提供了以下 4 种事务隔离级别供用户选择。

*  读未提交
    
    Read Uncommitted 读未提交：解决了回滚覆盖类型的更新丢失，但可能发生脏读现象，也就是可能读取到其他会话中未提交事务修改的数据。
 *  已提交读
    
    Read Committed 读已提交：只能读取到其他会话中已经提交的数据，解决了脏读。但可能发生不可重复读现象，也就是可能在一个事务中两次查询结果不一致。
 *  可重复度
    
    Repeatable Read 可重复读：解决了不可重复读，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上会出现幻读，简单的说幻读指的的当用户读取某一范围的数据行时，另一个事务又在该范围插入了新行，当用户在读取该范围的数据时会发现有新的幻影行。
 *  可串行化
    
    Serializable 串行化：所有的增删改查串行执行。它通过强制事务排序，解决相互冲突，从而解决幻度的问题。这个级别可能导致大量的超时现象的和锁竞争，效率低下。

数据库的事务隔离级别越高，并发问题就越小，但是并发处理能力越差（代价）。读未提交隔离级别最低，并发问题多，但是并发处理能力好。以后使用时，可以根据系统特点来选择一个合适的隔离级别，比如对不可重复读和幻读并不敏感，更多关心数据库并发处理能力，此时可以使用Read Commited隔离级别。

**事务隔离级别和锁的关系**

1、事务隔离级别是SQL92定制的标准，相当于事务并发控制的整体解决方案，本质上是对锁和MVCC使用的封装，隐藏了底层细节。

2、锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防止其他事务同时对数据进行读写操作。

3、对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁。

MySQL默认隔离级别：可重复读。Oracle、SQLServer默认隔离级别：读已提交

一般使用时，建议采用默认隔离级别，然后存在的一些并发问题，可以通过悲观锁、乐观锁等实现处理。

### MySQL 隔离级别控制 ###

查看数据库的隔离级别：

show variables like '%transaction_isolation%';

![image-20200905113926784][]

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

set global transaction_isolation ='READ-UNCOMMITTED'; 
     set global transaction_isolation ='READ-COMMITTED'; 
     set global transaction_isolation ='REPEATABLE-READ'; 
     set global transaction_isolation ='SERIALIZABLE';

## 锁机制 ##

### 锁分类 ###

在 MySQL中锁有很多不同的分类

*  从操作的粒度可分为表级锁、行级锁和页级锁。
    
     *  表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。
     *  行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。
     *  页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。应用在BDB 存储引擎中。
 *  从操作的类型可分为读锁和写锁
    
     *  读锁（S锁）：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。
     *  写锁（X锁）：排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

IS锁、IX锁：意向读锁、意向写锁，属于表级锁，S和X主要针对行级锁。在对表记录添加S或X锁之前，会先对表添加IS或IX锁。

S锁：事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，需要追加X锁，需要等记录的S锁全部释放。

X锁：事务A对记录添加了X锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操作。

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

### 行锁原理 ###

在 InnoDB 引擎中，我们可以使用行锁和表锁，其中行锁又分为共享锁和排他锁。**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，仅锁住索引本身而非范围。

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

### 悲观锁 ###

悲观锁（Pessimistic Locking），是指在数据处理过程，将数据处于锁定状态，一般使用数据库的锁机制实现。从广义上来讲，前面提到的行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴。

*  表级锁
    
    表级锁每次操作都锁住整张表，并发度最低。常用命令如下：
    
    手动增加表锁
    
        lock table 表名称 read|write,表名称2 read|write;
    
    查看表上加过的锁
    
        show open tables;
    
    删除表锁
    
        unlock tables;
    
    表级读锁：当前表追加read锁，当前连接和其他的连接都可以读操作；但是当前连接增删改操作会报错，其他连接增删改会被阻塞。
    
    表级写锁：当前表追加write锁，当前连接可以对表做增删改查操作，其他连接对该表所有操作都被阻塞（包括查询）。
    
    总结：表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。
 *  共享锁（行级锁-读锁）
    
    共享锁又称为读锁，简称S锁。共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。使用共享锁的方法
    
        select ... lock in share mode;
    
    只适用查询语句。
    
    总结：事务使用了共享锁（读锁），只能读取，不能修改，修改操作被阻塞。
 *  排他锁（行级锁-写锁）
    
    排他锁又称为写锁，简称X锁。排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁。使用排他锁的方法是在SQL末尾加上
    
        select ... for update;
    
    innodb引擎默认会在update，delete语句加上 for update。行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么会锁住全表记录。
    
    总结：事务使用了排他锁（写锁），当前事务可以读取和修改，其他事务不能修改，也不能获取记录
    
    锁（select… for update）。如果查询没有使用到索引，将会锁住整个表记录。

### 乐观锁 ###

乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。在数据库操作时，想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理，即不加锁，而是在进行事务提交时再去判断是否有冲突了。

乐观锁实现的关键点：冲突的检测。

悲观锁和乐观锁都可以解决事务写写并发，在应用中可以根据并发处理能力选择区分，比如对并发率要求高的选择乐观锁；对于并发率要求低的可以选择悲观锁。

乐观锁实现原理

*  使用版本字段（version）
    
    先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version
    
    是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。

![format_png 6][]

*  使用时间戳（Timestamp）
    
    与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。

### 死锁 ###

死锁产生的四个必要条件：

互斥条件：一个资源同一时刻只能被一个进程占用。

不可剥夺：一个资源除非被占用的进程主动释放，否则不能被剥夺

请求保持：一个进行请求资源时，没有获取到则保持请求，直到获取资源

循环等待：进程a 等待进程b 持有的资源，进程b 等待进程a 持有的资源

## 总结 ##

记住事务特性、隔离级别、排它锁、共享锁、读写锁、MVVC 等各个知识点就差不多啦。记不住的记得收藏喔，不迷路哈哈

[format_png]: /images/20221123/4d86d8f1ac44424a9c0302024e663882.png
[format_png 1]: /images/20221123/0cd0a82a8b6548d19513d07d42a068a6.png
[format_png 2]: /images/20221123/55e932f91f9b418098a422ab49fab7ec.png
[image-20200905095258313]: /images/20221123/aae70a44978440b8a4aebedaf31b7021.png
[format_png 3]: /images/20221123/698892a603f54b5c93cb2a78726b1878.png
[format_png 4]: /images/20221123/39ec2e2a095b44bfa0300dc7fb1f495e.png
[format_png 5]: /images/20221123/c5161a3d3908437f84653a323c77a743.png
[image-20200905104556867]: /images/20221123/19bd9ff2d22345a39c51171219fb10df.png
[image-20200905113926784]: /images/20221123/8e188c7ee83b462dbf31e59d0f876164.png
[format_png 6]: /images/20221123/b8fdfcef1dbc45d28caa2d705f5c8606.png