MySql事务篇

在关系型数据库中，事务总是很重要的一环，在MySql中，只有Innodb表类型才支持事务

mysql默认自动提交事务

一、ACID特性

事务必须满足4个特性，即ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

原子性
- 特性：一个事务中的所有操作，要么全部成功，要么全部失败
- 在事务提交步骤：修改=》BP修改=》刷盘
  - 事务提交了，如果此时Buffer Pool的脏页没有刷盘，如何保证修改的数据生效？ Redo
  - 如果事务没提交，但是Buffer Pool的脏页刷盘了，如何保证不该存在的数据撤销？Undo
- 每一个写事务，都会修改BufferPool，从而产生相应的Redo/Undo日志，在Buffer Pool 中的页被刷到磁盘之前，这些日志信息都会先写入到日志文件中，如果 Buffer Pool 中的脏页没有刷成功，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写的数据不会丢失。如果脏页刷新成功，此时数据库挂了，就需要通过Undo来实现了
一致性
- 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作
- 一致性包括两方面的内容，分别是约束一致性和数据一致性
  - 约束一致性：创建表结构时所指定的外键、Check、唯一索引等约束，可惜在 MySQL 中不支持 Check
  - 数据一致性：是一个综合性的规定，因为它是由原子性、持久性、隔离性共同保证的结果，而不是单单依赖于某一种技术。
- 一致性也可以理解为数据的完整性。数据的完整性是通过原子性、隔离性、持久性来保证的，而这3个特性又是通过 Redo/Undo 来保证的。逻辑上的一致性，包括唯一索引、外键约束、check 约束，这属于业务逻辑范畴
隔离性
- 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
持久性
- 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失
- 一个完整事务的流程如下：
总结：ACID的4个特性有3个都与WAL（Write-Ahead Logging）有关系，都需要通过 Redo、Undo 日志来保证

二、事务隔离级别

在上面的事务ACID中，有一项为隔离性（Isolation），其中隔离性分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）

读未提交（Read uncommitted）
- 所有事务都可以看到没有提交事务的数据
- 例如：银行A给客户B账上转了1000元（原有账户有2000元），B去查看账户时，发现账户上有3000元（此时A未提交事务），后由于发现转多了，回滚事务了，此时B的账户只有1000元，这就是出现了脏读
读提交（read committed）
- 只能读取到其他会话中已经提交的数据，解决了脏读。但可能发生不可重复读现象，也就是可能在一个事务中两次查询结果不一致
- 例如：在A事务中，第一次查询用户表为10条，此时B事务新增了一条用户数据，提交事务，A事务未关闭，又查询了用户表，此时数据为11条
可重复读（repeatable read）
- 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题
串行化（Serializable）
- 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争
数据库的事务隔离级别越高，并发问题就越小，但是并发处理能力越差（代价）。读未提交隔离级别最低，并发问题多，但是并发处理能力好。以后使用时，可以根据系统特点来选择一个合适的隔离级别，比如对不可重复读和幻读并不敏感，更多关心数据库并发处理能力，此时可以使用Read Commited隔离级别
事务隔离级别，针对Innodb引擎，支持事务的功能。像MyISAM引擎没有关系
事务和锁关系
- 事务并发控制的整体解决方案，本质上是对锁和MVCC使用的封装，隐藏了底层细节
- 锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防止其他事务同时对数据进行读写操作
- 对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁

MySQL隔离级别控制

MySQL默认的事务隔离级别是Repeatable Read，查看MySQL当前数据库的事务隔离级别命令如下
```
show variables like 'tx_isolation';
```
或
```
select @@tx_isolation;
```

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

set tx_isolation='READ-UNCOMMITTED';
set tx_isolation='READ-COMMITTED';
set tx_isolation='REPEATABLE-READ';
set tx_isolation='SERIALIZABLE';

三、锁机制

首先对mysql锁进行划分：

按照锁的粒度划分：行锁、表锁、页锁
按照锁的使用方式划分：共享锁、排他锁
思想上划分：悲观锁、乐观锁
InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key Lock
- Record Lock：在索引记录上加锁
- Gap Lock：间隙锁
- Next-key Lock：Record Lock+Gap Lock

1、行锁

每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在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，仅锁住索引本身而非范围
- 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锁。
- 例如：
```
UPDATE `user` SET `name` = 'mysql' WHERE id=10
```
  - 主键加锁：仅在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引擎锁机制是基于索引实现的记录锁定）

2、表锁

表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁

3、页锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

4、悲观锁（Pessimistic Locking）

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

悲观锁的具体流程：
1. 在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）
2. 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定
3. 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了
4. 其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常
悲观锁的优点和不足：
- 悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性；当一个事物所以一行数据的时候，其他事物必须等待该事务提交之后，才能操作这行数据

5、乐观锁

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

乐观锁实现原理
- 使用版本字段（version）：先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version 是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。
- 使用时间戳（Timestamp）：与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp 时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。
乐观锁案例
- 第一步：查询商品信息（查询商品信息，数据版本为1）
- 第二部：根据商品信息生成订单（生成订单，版本+1）
- 第三部：修改商品库存（判断即将修改的版本是否为1，若为1则修改，若不为1 ，则放弃修改）

6、死锁与解决方案

表锁死锁
- 产生：用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了
- 解决：这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。
行级锁死锁
- 产生原因1：如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁
- 解决方案1：SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化
- 产生原因2：两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。
- 解决方案2：在同一个事务中，尽可能做到一次锁定所需要的所有资源；按照id对资源排序，然后按顺序进行处理
共享锁转换为排他锁
- 产生原因：事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁
- 解决方案：
  - 对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作；
  - 使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中；
死锁排查：MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。
- 查看死锁日志
  
  通过show engine innodb status\G命令查看近期死锁日志信息。
  
  使用方法：1、查看近期死锁日志信息；
  
  2、使用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：系统启动后到现在总共等待的次数
  如果等待次数高，而且每次等待时间长，需要分析系统中为什么会有如此多的等待，然后着手定制优化

7、MVCC

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

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

MVCC概念

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）
例如：
- 假设 F1～F6 是表中字段的名字，1～6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事务号和回滚指针，如下图所示。
具体的更新过程如下：
- 假如一条数据是刚 INSERT 的，DB_ROW_ID 为 1，其他两个字段为空。当事务 1 更改该行的数据值时，会进行如下操作，如下图所示。
  - 用排他锁锁定该行；记录 Redo log；
  - 把该行修改前的值复制到 Undo log，即图中下面的行；
  - 修改当前行的值，填写事务编号，使回滚指针指向 Undo log 中修改前的行。
- 接下来事务2操作，过程与事务 1 相同，此时 Undo log 中会有两行记录，并且通过回滚指针连在一起，通过当前记录的回滚指针回溯到该行创建时的初始内容，如下图所示。

posted @ 2022-03-11 14:16 小学程序员阅读(70) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

java徒

MySql事务篇

MySql事务篇

一、ACID特性

原子性

一致性

隔离性

持久性

二、事务隔离级别

读未提交（Read uncommitted）

读提交（read committed）

可重复读（repeatable read）