MySQL中的事务_mysql事务

MySQL中的事务

事务的定义

概念：数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。

就我的理解来说：简单的说事务就是一组同时执行成功或者同时执行失败的数据库操作命令。

事务的分类

• 只读事务

  • 开启方式：START TRANSACTION READ ONLY语句开启一个只读事务
  • 特点：
    • 只读事务中不可以对普通的表（其他事务也能访问到的表）进行增、删、改操作
    • 但可以对用户临时表做增、删、改操作

【拓展】数据库中的临时表（出现在MySQL的3.23版本以后）

• 作用：临时表有时候对于保存临时数据非常有用。临时表可以和普通表一样的进行操作，并且在当前的终端会话结束后被删除。
• 操作：
  • 表创建：CREATE TEMPORARY TABLE tableName
  • 表删除：DROP TABLE tableName;

• 读写事务（默认使用类型）

  • 开启方式：
    • START TRANSACTION READ WRITE语句开启一个读写事务
    • 或者直接使用 BEGIN、START TRANSACTION开启
      我们默认使用的就是读写事务
  • 特点：对普通表可以进行任何操作

事务的特性（ACID）

2. 原子性（Atomicity）：

   • 概念：一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
   • 我的理解：同一个事务中的操作最终执行状态一样（一组操作要么都成功，要么都失败）

3. 持久性（Durable）

   • 概念：持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
   • 我的理解：一个事务中的修改内容可以永久的在磁盘中进行保存，即使系统出现故障也可以进行持久保存

4. 隔离性（Isolation）

   • 概念：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
   • 我的理解：每个事务的执行时互不影响，保证并发场景下的“环境独立”

5. 一致性（Consistent）

   • 概念：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
   • 我的理解：事务中更改的数据最终会保证所有数据读取出的结果和磁盘中的数据保证一致

特性之间的关系

事务中最重要的特性其实时一致性，一致性是开始事务的最终目的，而原子性、隔离性和持久性都是实现一致性的手段

事务实现的方式

WAL日志预写

先将修改内容写入到日志中，然后再同步到系统磁盘中，InnoDB就是通过这种方式实现的，日志包括undo log和redo log来实现事务；其中数据落盘成功前也可以进行数据的改动。
MySQL的InnoDB的事务就是使用这种方式实现的。

Commit Log提交日志

在日志落盘成功后才进行数据的修改；必须落盘成功再进行数据修改。
OceanBase数据库就使用的这种方式实现的。

Shadow Paging影子分页

使用写时复制的机制实现，再进修改操作时对修改数据做一个复制操作，在复制区域进行修改，修改完成后再进行内容的替换。
安卓开发中SQLLitte就是通过这种方式实现的。

事务特性实现的原理

6. redo log：事务持久性实现的方法

   • redo日志作用：记录事务在执行过程中对数据库所做的数据修改
   • 使用redo日志记录变化的原因：
     • 数据是存放在数据页中的，如果直接使用数据页进行记录的话，这样的记录方式会导致使用很多额外的空间来存储数据页中很多没有变化的数据；并且使用数据页的话，页会导致多次的随机IO导致处理效率降低
     • 但是redo日志只是进行改变行修改数据的记录；的如果只是使用行记录的改变数据进行顺序追加，就可以避免使用页记录变化造成的问题
   • redo日志的大致格式：包括了日志类型、表空间Id、页号、偏移量和数据改动
     
   • redo日志的大小：
     • redo日志被分为了大小为512字节的块；
     • 日志文件默认大小为48MB；
     • 可以通过innodb_log_file_size参数进行设置。
   • redo日志的个数：
     • 默认有2个，分别为ib_logfile0和ib_logfile1；最多可以设置为100
     • 可以通过innodb_log_files_in_group参数进行设置
   • redo日志记录的内容：
     • 更行操作的改动记录
     • InnoDB默认维护的隐藏列row_id的维护：每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为7的页面中一个称之为Max Row ID的属性处
     • 开始事务时系统的维护的trx_id其实也是通过redo日志进行持久化的，和row_id实现的方式一样，每当这个变量的值为256的倍数时进行redo日志的记录
   • log buffer：日志缓冲区
     • 存在于内存之中
     • log buffer 默认大小为 16KB，区域被分为大小为512字节的块，方便写入磁盘中的redo日志
     • 大小可以通过 innodb_log_buffer_size 来进行设置
   • redo日志写入过程：
     • redo日志首先会写入到log buffer中
     • 在将log buffer中的数据顺序写入到磁盘中的redo日志
     • 写入操作以块为单位进行顺序写入，一个块写满后才会向下一块中进行写入（redo log 会循环写入，可以通过LSN来保证在覆盖前会保证脏页以进行了刷盘）

       Log Sequence Number（LSN）：日志序列号，规定初始的lsn值为8704

       • 查看系统中的LSN的值：SHOW ENGINE INNODB STATUS\G
       • Log sequence number：当前所有的redo日志最大的日志序号
       • Log flushed up to：已经进行了刷盘的redo日志的的日志序号
       • 使用Log sequence number和Log flushed up to进行比价来保证为未刷盘的记录不会被覆盖

   • redo日志的刷盘机制：
     • log buffer空间不足时
     • 事务提交时
     • 后台有一个线程，大约每秒都会刷新一次log buffer中的redo日志到磁盘
     • 正常关闭服务器时

7. undo log：事务一致性实现的方法

   • undo日志的作用：为了实现同一事务操作时如果事务执行失败时对已修改的数据进行回滚操作提供修改前原信息的日志
   • 日志格式：额外信息、事务id(trx_id)、回滚指针(roll_pointer)和记录的真实数据
     
     • roll_pointer：roll_pointer本质上就是一个指向记录对应的undo日志的一个指针
     • trx_id：一个事务的起点不是从begin/start transaction开始的，当事务中执行第一个写语句，事务才真正的开始，才会向MySQL来申请事务id
     • 记录头信息
       • delete_mask：行记录的删除标记
       • next_record：指向下一个记录的指针
   • 数据页的相关内容补充 ：
     • 数据页
       
       页中Page Header中PAGE_FREE的属性它指向由被删除记录组成的垃圾链表中的头节点
     • 页数据的组织形式
       
       • 数据页中的整行链表：页面中的记录会根据记录头信息中的next_record属性组成一个单向链表，我们把这个链表称之为正常记录链表
       • 数据页中的垃圾链表：被删除的记录其实也会根据记录头信息中的next_record属性组成一个链表，只不过这个链表中的记录占用的存储空间可以被重新利用
   • 不同操作下的undo日志记录：

     • Insert语句

       • 日志类型：TRX_UNDO_INSERT_REC类型
       • 说明：
         • 原则上需要添加聚集索引和二级索引的多条undo日志，实际只需要对聚集索引的改动进行记录就行
         • 回滚时可以通过聚集索引的id回推到对应的二级索引进行回滚的操作

     • Delete语句

       • 日志类型：TRX_UNDO_DEL_MARK_REC类型
       • 说明：Delete执行过程
         • 第一阶段：**delete_mask标识位设置为1；**但是并没有被加入到垃圾链表；处于中间状态，用于MVCC机制中的数据回滚使用
           
         • 第二阶段：**正常记录链表中移除，并且加入到垃圾链表中；**事务提交之后，会有专门的线程后来真正的把记录删除掉；这条已删除记录占用的存储空间也可以被重新利用了。
           

     • Update语句

       • 更新主键的情况
         • 日志类型：TRX_UNDO_DEL_MARK_REC类型 + TRX_UNDO_INSERT_REC类型
         • 说明：
           • 整个过程中会产生两条undo日志
           • 其实就是先进行delete删除操作，然后在进行insert插入操作
       • 不更新主键的情况
         • 日志类型：TRX_UNDO_UPD_EXIST_REC类型
         • 说明：其更更新的方式页分为两种
           • 就地更新（in-place update）:存储空间不发生变化
             • 更新后的列和更新前的列占用的存储空间都一样大
             • 直接在原记录的基础上修改对应列的值
           • 先删除旧记录，再插入新的记录：存储空间发生变化
             • 被更新的列更新前和更新后占用的存储空间大小不一致
             • 旧的记录从聚簇索引页面中删除掉，然后再根据更新后列的值创建一条新的记录插入到页面中

8. MVCC和锁机制：事务实现隔离性的方法

   并发事务带来的问题和隔离级别

   • 并发事务带来的问题：

     • 脏写：后面执行的更新操作导致了覆盖了其他事务进行的更新操作
     • 脏读：事务A读取到了事务B已经修改但是没有提交的数据
     • 不可重复读：事务A内部的相同查询语句在不同时刻读取的结果不一致
     • 幻读：事务A读取到了事务B提交的新增数据

   • 隔离级别：读未提交(read uncommitted)、读已提交(read committed)、可重复度(Redpeatable read)、可串行化(serializable)

     隔离级别	脏读(DIrty Read)	不可重复读(NonRepeatable)	幻读(Phantom Read)
     读未提交(Read uncommitted)	可能	可能	可能
     读已提交 (Read committed)	不可能	可能	可能
     可重复读 (Repeatable read)	不可能	不可能	可能
     可串行化 (Serializable)	不可能	不可能	不可能

     • MySQL的默认隔离级别是可重复度
     • 隔离级别的设置语句

       set tx_isolation='read-uncommitted';
       set tx_isolation='read-committed';
       set tx_isolation='repeatable-read';
       set tx_isolation='serializable';
       1
       2
       3
       4

   MVCC（Multiversion Concurrency Control，多版本并发控制）

   InnoDB的读已提交和不可重复读就是通过MVCC机制实现的；
   MVCC的实现借助了两个对象来实现，分别是undo日志版本链和read-view一致性视图

   • undo日志版本链
     

   • 一致性视图 read-view

     • 组成：当前未提交事务的事务id组成的数组和当前最大的事务id组成
     • 生成时间：事务执行第一条查询语句时生成
     • 更新时间：
       • RR级别:一致性视图生成后在事务提交之前都不会变动**
       • RC级别:每执行一条查询SQL语句前会根据最新数据生成一致性视图
         undo日志版本链
     • 实现过程：
       • 开启事务
       • 第一次执行查询SQL时生成一致性视图
       • 具体操作：
         • 读操作：进行版本链比较（当前事务id：trx_id，{[min_trx_id,…],max_trx_id}）
           • trx_id<min_trx_id：说明该undo日志对应的版本已提交；数据可见，直接使用数据即可
           • trx_id>max_trx_id：该版本对应的trx_id是未来的，当前一定没有进行事务提交；数据不可见，通过roll_pointer找到下一个undo日志
           • min_trx_id <= trx_id <= max_trx_id
             • 当前undo日志维护的trx_id没在未提交事务的事务id集合中则读取相应记录值
             • 当前undo日志维护的trx_id在未提交事务的事务id集合中则通过roll_pointer找到上个版本的undo日志进行重复比较
         • 写操作：undo和redo日志的追加
       • 提交事务

   • 举个MVCC进行比较例子（当前的隔离级别为可重复读）

     • 数据如下：
       
     • 执行过程如下：(提交事务为黄色，未提交的事务为蓝色)
       【步骤一】开始事务时，undo日志版本链如下：
       
       表中的数据事务id均为0
       【步骤二】事务A执行更新操作，update employees_s set name = 'Tom' where id = 2;生成新的undo日志，版本连如下：
       

   新的undo日志中事务为100，name字段修改为了Tom，并添加到了undo日志版本链的中
   【步骤三】事务B执行更新操作，update employees_s set name = 'Jim' where id = 3;生成新的undo日志，版本链如下：
   

新的undo日志中事务为200，name字段修改为了Jim，并添加到了undo日志版本链的中
【步骤四】事务C进行查询操作，select * from employees_s where id = 2;此时的事务C生成read-view一致性视图
json { 'uncommitted_trx_id_list':[100,200], 'max_trx_id': 300 }
所以根据一致性视图和当前undo日志版本链的比较规则，从undo日志的头节点进行遍历，通过row_id找到对应的undo日志，对于事务C来说，row_id为2，trx_id为100在事务C一致性视图的uncommitted_trx_id_list 中，所以这条undo日志对于事务C是不可见的，所以继续向下遍历，继续找row_id为2的undo日志，找到row_id为2，trx_id为0的undo日志，0 事务id不在事务C的uncommitted_trx_id_list中，这样来说对对于事务C是可以见的，就读到了当前的结果2,Lucy,18
【步骤五和步骤六】事务A进行提交，当前的undo日志版本链如下：

【步骤七】事务D进行查询操作，select * from employees_s where id = 3;此时事务D会生成一致性视图
json { 'uncommitted_trx_id_list':[200], 'max_trx_id': 300 }
所以根据一致性视图和undo日志版本链的比较规则，分别找到row_id为3两条undo日志，分别对应的事务id为300和0对应的改动日志，200不在事务D的uncommitted_trx_id_list集合中，数据对于事务D是可见的,读取到的数据就是3,Jim,24
【步骤八】事务C进行查询，还是依旧使用步骤四中生成的一致性视图，和当前的版本链进行比较，索引最终找到的两条undo日志为row_id=2,trx_id=0和row_id=3,trx_id=0的undo日志，结果为(2,Lucy,18),(3,Herry,24)
【步骤九】事务B提价事务后，undo日志版本链为：

【注意】上述的隔离级别是可重复读，但是当隔离级别改为读已提交的级别时，步骤八的结果会有所不同，不同的原因是因为不同隔离级别下生成一致性视图的时机时不一样的，之前我们说过了，可重复读的一致性视图是第一次查询语句执行的时候生成，直到事务提交；读已提交的级别的一致性视图是在每次查询语句执行前生成的，所以步骤八在读已提交的级别下一致性视图这样的
json { 'uncommitted_trx_id_list':[200], 'max_trx_id': 300 }
所以，在查询时找到的就是row_id=2,trx_id=100和row_id=3,trx_id=0的undo日志，结果为(2,Tom,18),(3,Herry,24)

锁机制

• 锁的概念：锁是计算机协调多个进程或线程并发访问某一资源的机制。
• 锁的分类：如下是我整理的思维导图，大家可以参考一下
  上面导图链接
• 不同引擎中使用的锁
  • MyISAM
    • 执行查询语句SELECT前，会自动给涉及到的所有表加读锁
    • 在执行UPDATE、INSERT和DELETE操作会给设计到的表加写锁
  • InnoDB
    • 在执行查询语句SELECT时（非串行化隔离级别）不会进行加锁
    • 在执行UPDATE、INSERT和DELETE操作会加上行锁
• 可重复读级别下的幻读问题解决方法：
  在通常的可重复读下是没法解决幻读问题的，但是MySQL通过间隙锁一定程度上解决了幻读的问题；但是间隙锁只在可重复的隔离级别下才生效
  间隙锁锁定的是索引某个区间的所有行记录（实际实现可能是在给给更新索引所在区间记录下来，在其他事务进行跟新操作时对其修改索引的范围于记录的范围进行比较，如果是记录的范围，则不允许进行操作）
  举个例子，表数据如下：

id为索引字段，此时数据就会划分为以下几个区间：(3,7),(7,20)和(20,∞)；
当执行insert into tabeleName (id,name) value(10,'Herry')时，处理会给id=10添加行锁外，还所锁(7,20)范围的所有行，事务在执行id在这个范围内的所有更新操作时，事务页会处于等待锁释放，这样的锁就称为间隙锁

【拓展】间隙锁 + 行锁 组成的左左开右闭的区间锁就成为 临键锁

锁使用的优化

其实对于锁使用的优化一定程度上就是对索引的设计的优化；

• 尽可能让所有数据的检索都通过索引来完成，避免无索引导致的行锁升级为表锁的情况发生
• 合理设计索引，优化业务语句的书写，尽量缩小索引的范围
• 减少每次锁定的资源的数据量

事务的优化原则

• 讲查询等数据准备操作放在事务之外进行执行
• 事务中避免远程调用，如果一定要在事务中进行远程调用，远程调用要进行超时时间的设置，防止造成大事务的情况
• 事务中要避免一次处理的数据过多，如果要处理的数据量较大，可以拆分事务分批执行
• 更新等涉及到加锁的操作尽可能放在事务靠后的位置执行
• 能异步处理的尽量异步处理，异步处理也要做超时日志
• 通过应用侧（业务代码）保证数据一致性[通过try...catch...进行补偿]，可不使用事务，但是不建议使用

事务处理的流程和断电后事务的恢复