06.事务

• 事务：是将一组数据库操作打包起来形成一个逻辑独立的工作单元,这个工作单元不可分割,要么不发生,要么全发生

• 事务的特性(ACID):成为事务必须满足这特性

  • 原子性(Atomicity):事务不可分割的性质
  • 一致性(Consistency):事务在执行前后数据库都处于一致性状态,即满足完整性约束
  • 隔离性(Isolation):多个事务并发执行时必须独立
  • 持久性(Durability):已提交的事务对数据库的修改是永久的持续存在的

• 事务的状态

  • 活动的(active)：事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态
  • 部分提交的(partially committed)：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态
  • 失败的(failed)：当事务处在活动的或者部分提交的状态时，可能遇到了某些系统或硬件错误而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态
  • 中止的(aborted)：如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态，回滚执行完毕，我们就说该事务处在中止的状态

  

  事务状态转换

6.1 事务基础

• 事务流程
  • 开启事务
  • 执行DML操作
  • 事务结束点：提交事务或者回滚事务

6.1 基础操作

• 开启事务

  MySQL

  -- 通过关键字 start transaction 或 begin 开启事务
  -- 后面可以跟：read only/read write(默认)/with consistent snapshot
  -- read only:只读事务，不能修改其他事务可访问的表，对临时表是可以修改的
  -- read write:读写事务，可以读写数据
  -- with consistent snapshot:使用一致性读，是一个单独的选项
  start transaction;
  start transaction read only, with consistent snapshot;

• 提交和回滚事务

  MySQL

  -- 提交事务
  commit;
  
  -- 回滚事务
  rollback;
  
  -- 可以指定事务的链式行为
  -- completion_type=0，默认，当事务结束后，下一个事务还需要使用 BEGIN 开始
  -- completion_type=1，当事务结束后，自动开始新事务
  -- completion_type=2，当事务结束后，自动中断数据库连接
  SET @@completion_type = 1;

• 创建保存点

  MySQL

  -- 创建一个保存点
  savepoint savepoint_name;
  
  --回滚到保存点
  rollback to savepoint_name;
  
  -- 删除保存点
  release savepoint savepoint_name;

  自动提交

  默认情况下，像MySQL这样的有自动提交(auto_commit默认为on)的数据库，相当于帮我们写了commit语句，也就是隐式事务

  • 如果设置为SET auto_commit = false，就相当于开启了事务，需要在执行完成后进行提交commit或者回滚rollback
  • 一般情况下虽然可以通过修改选项达到开启事务，为了规范书写还是考虑使用显式事务

6.2 隐式提交

MySQL

• 隐式提交：在下列操作中，数据库会隐式提交之前的事务，不受事务或auto_commit选项的影响
  • 数据库定义语言
  • 修改mysql数据库中的表，包括修改用户表(因此对用户操作也会隐式提交)
  • 重复的begin
  • 将auto_commit设置为on
  • 使用锁定相关语句，比如LOCK TABLES、UNLOCK TABLES
  • 数据库复制的语句：start slave;、stop slave;、reset slave;等
  • 加载数据的语句：LOAD DATA
  • 其他：使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET、TRUNCATE Table等语句

6.3 隔离级别

• 并发引起的问题:

  • 脏写:事务修改了另一个事务修改完的数据，当前事务提交后另一个事务需要回滚，内容未保存，
  • 脏读:在读取数据时,数据已被另一事件读取修改,之后数据被rollback,读取到了不存在在数据库中的数据,这种不正确的数据被称为脏数据
  • 不可重复读:在该事务读取数据时,数据已被读取磁盘并在修改值,之后数据被修改,两次数据不一致
  • 幻读:一个事务添加了新的表行，另一个事务在此事务操作前后两次获取的行数不同

  脏写问题过于严重，数据库不允许这种情况发生

• 事务的隔离级别：为了平衡性能，解决脏读、不可重复读和幻读这些问题，数据库提供了不同的事务的隔离级别

  • 读未提交READ UNCOMMITTED：允许一个事务读取其他事务未提交的数据。不能避免脏读、不可重复读和幻读
  • 读已提交READ COMMITTED：允许一个事务读取其他事务已提交的数据。不能避免不可重复读和幻读
  • 可重复读REPEATABLE READ：一个事务读到的数据与其他事务的修改提交无关。虽然直接查询是找不到这些其他事务添加的行，但其他事务插入的行是存在的，比如插入相同主键会报错，不能避免幻读

    一般情况下期望通过MVVC的机制，解决脏读、不可重复读和幻读问题，性能更高，但有时还是需要加锁解决，可以使用间隙锁解决幻读问题

  • 串行化SERIALIZABLE：一个事务对表操作时，其他事务都不能对该表操作，上面的问题都能避免，需要加锁读

  事务隔离级别查询

  • 可以通过以下方法获取和设置事务隔离级别

  MySQL

  -- 查询
  SELECT @@transaction_isolation;
  -- 默认隔离级别为 REPEATABLE-READ
  
  -- 设置为其他级别
  -- GLOBAL：对之后数据库的所有连接有效
  -- SESSION：对之后的本会话开启的新事务有效
  SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [
  READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE
  ];
  SET [SESSION|GLOBAL] transaction_isolation = [
  'READ-COMMITTED' | 'READ-UNCOMMITTED' |'REPEATABLE-READ' |  'SERIALIZABLE'
  ];

  隔离级别支持程度

  隔离级别	Oracle	MySQL
  READ UNCOMMITTED	❌	✔️
  READ COMMITTED	✔️(默认)	✔️
  REPEATABLE READ	❌	✔️(默认)
  SERIALIZABLE	✔️	✔️

6.4 事务日志

• 事务的原子、持久、一致性是通过事务日志保证的，许多数据库都有这样的机制，实现可能不同
  • redo日志：提供再写入操作，恢复提交了的事务修改的操作，保证持久性
  • undo日志：提供再回滚操作，是更新的前置操作，保证一致性、原子性

  undo日志也会产生redo日志

6.4.1 MySQL

• InnoDB是MySQL数据库中能进行事务处理的存储引擎

MySQL redo日志

• 对于数据库事务而言，希望只将提交的事务修改写入磁盘，如果修改进行到一半数据库崩溃，也希望恢复到提交的状态，一种解决思路是WAL(Write-Ahead Log)，也就是先写修改日志，日志写完才算事务提交成功，之后根据日志将数据修改到磁盘，如果修改阶段断电，可以通过日志恢复，这也就是redo日志

  • redo日志占用空间很小，减少了刷盘频率。刷盘是指将数据持久化存储在磁盘上，需要对相关数据页进行随机访问，速度慢，对应的写盘是将数据写入磁盘缓存中，速度更快
  • 事务执行过程中，日志不停记录(存储引擎层)，最后按顺序写入磁盘

• redo日志由两部分组成

  • redo log buffer：redo日志缓冲区，在服务器启动时就向操作系统申请了一大片连续内存空间作为缓冲区。这片内存空间被划分成若干个连续的redo 1og block
    • MySQL中一个block占用512字节，InnoDB内存缓存大小通过innodb_log_buffer_size参数设置(默认16M)
  • redo log file：redo日志文件，记录了持久化的日志
    • MySQL中文件默认在数据库目录下，两个文件ib_logfile0和ib_logfile1轮询使用，也就是先写到ib_logfile0，写完之后再写入ib_logfile1，然后循环使用
    • 轮询队列中进行两个操作，在write pos对应的文件写入，在checkpoint对应的文件擦除
    • 目录可以通过innodb_log_group_home_dir参数设置(默认.\)
    • 文件数量通过innodb_redo_log_files参数设置(默认2)
    • 每个文件最大值通过innodb_log_file_size参数设置(默认48MB, 50331648)，所有redo日志文件大小不超过512GB

• redo流程

  • 先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
  • 生成一条重做日志并写入redo日志缓冲区,记录的是数据被修改后的值
  • 当事务提交时，将redo日志缓冲区中的内容刷新到redo日志文件,对redo日志文件采用追加写的方式
  • 定期将内存中修改的数据刷新到磁盘中

  

  redo流程(以更新事务为例)

  redo日志刷盘策略

  redo日志一开始是存储在内存中的，然后将日志以一定频率刷盘到磁盘中，这个频率可以设置，也就是刷盘策略

  -- 设置InnoDB引擎的刷盘策略
  -- 0: 提交事务之后不刷盘
  -- 1: 系统在每提交一个事务就刷盘(默认)
  -- 2: 每提交一个事务就将日志写入文件缓存，由操作系统决定何时将文件缓存写入磁盘
  -- 另有InnoDB存储引擎的后台线程每秒进行一次redo日志刷盘
  -- 如果使用的内存缓存过半，也会触发redo日志刷盘
  SET GLOBAL innodb_flush_log_at_trx_commit = 1;

• Mini-Transaction：MySQL把对底层页面中的一次原子访问的过程称之为一个Mini-Transaction,即mtr,比如，向某个索引对应的B+树中插入一条记录的过程就是一个Mini-Transaction

  • 一个所谓的mtr可以包含一组redo日志，在进行崩溃恢复时这一组redo日志作为一个不可分割的整体，有的mtr的日志很多
  • 一个事务可以包含若干条语句，每一条语句其实是由若干个mtr组成，每一个mtr又可以包含若干条redo日志

• 写入日志缓冲区：redo log buffer包含多个日志块，每个块占512字节(和机械磁盘扇区大小相同)

  • 向缓冲区写入日志的过程是顺序的，在InnoDB全局有一个buf_free，记录了接下来要写入日志的起始位置
  • 每个mtr内的日志是连续记录的，但多个事务可能并发执行，因此每个事务的mtr记录可能不连续

  在写入日志文件时，为了与binlog日志同步，避免两日志内容不一致，会采用两阶段提交，先prepare预提交内容，在binlog写入后再进行commit提交

  日志块构成

  • 日志块有对应的头、体和尾组成，体中存储的就是redo日志
    • 日志头12B
      • LOG_BLOCK_HDR_NO用来标记日志在buffer中的位置，递增循环使用，占用4B
      • LOG_BLOCK_HDR_DATA_LEN表示块已经使用了多少字节，初始值为12，如果日志体全部写满，值被设置为512
      • LOG_BLOCK_FIRST_REC_GROUP表示日志块中第一个mtr日志组的偏移，如果和LOG_BLOCK_HDR_DATA_LEN相同，表示没有新的日志组
      • LOG_BLOCK_CHECKPOINT_NO表示最后一个被写入的检查点
    • 日志体496B，存储redo日志
    • 日志尾4B，包含一个校验和LOG_BLOCK_HDR_CHECKSUM

MySQL undo日志

• undo日志的作用

  • 回滚数据：根据undo log将数据库逻辑地恢复到执行之前的状态，物理结构可能有改变
  • MVVC：支持在undo log中读取旧数据，实现非锁定读

• undo日志结构：InnoDB对undo log的管理采用段的方式，也就是回滚段(rollback segment)。每个回滚段记录了1024个日志段undo log segment,而在每个日志段中进行undo页的申请

  • 在InnoDB1.1版本之前，只有一个回滚段,因此支持同时在线的事务限制为1024
  • 从1.1版本开始InnoDB支持最大128个回滚段,故其支持同时在线的事务限制提高到了128*1024，但回滚段都存储在共享表空间中，直到1.2版本才允许设置

  相关配置参数(一般不改动)

  • innodb_undo_directory:设置rollback segment文件所在的路径。该参数的默认值为./，表示当前InoDB存储引擎的目录
  • innodb_undo_logs:设置rollback segment的个数，默认值为128
  • innodb_undo_tablespaces:设置构成rollback segment文件的数量，这样rollback segment可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件，该文件就代表rollback segment文件。MySQL 8.0.14及以后废弃，改为通过语句如CREATE UNDO TABLESPACE动态创建管理

• 回滚段和事务的关系：

  • 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务
  • 当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段
  • 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘区或者在回滚段允许的情况下扩展新的盘区来使用
  • 回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个undo表空间
  • 当事务提交时：存储引擎会将undo log放入链表中，供之后清除线程使用，如果能重用(页使用空间小于3/4)，会分配给下一个事务使用
  • 一个页中可能有许多不同事务的日志，一个事务提交后不会马上清除此页，undo retention设置了日志过期时间，是否清除由清除线程决定

• undo log可以分为两类

  • insert log：仅供当前事务使用，可以在事务提交后直接清除
  • update log：供事务和MVVC使用，在事务提交后不能马上清除，放入链表，等待清除线程处理

  

  MySQL中的redo和undo日志

• 在InnoDB存储格式中有隐藏列DB_TRX_ID和DB_ROLL_PTR用于存储事务信息

  • DB_TRX_ID：当前事务的ID，如果数据发生变更，会将当前事务的ID写入到DB_TRX_ID列
  • DB_ROLL_PTR：当前事务的回滚指针，本质是一个undo log的指针，用于回滚数据

• 详细流程

  • 插入时，会记录日志序号、主键列和值，在进行rollback时，会根据日志序号找到对应的日志，并删除该行数据

    

    插入

  • 更新非主键时，会记录日志序号、原先的列值，并将新生成的日志指向原来的老的日志，在进行rollback时，会根据日志序号找到对应的日志，并恢复该行数据

    

    更新非主键

  • 更新主键时，会生成新的记录，并打开老记录deletedmark标识，新的记录中的回滚指针指向日志(记录原先主键)

    

    更新主键(左侧为原记录，右侧为新记录)

• 在正常情况下，还有一个binlog，一般用于主从集群同步数据，结合了三个日志的数据更新流程如下：

  

  结合三个日志的详细流程

6.5 锁

• 锁：锁是计算机协调多个线程并发访问数据的机制。锁机制保证数据的一致性，同时也为实现MySQL的隔离级别提供了保证
• 数据库并发问题的常用解决方法
  • MVVC：读操作，需数据库支持(MySQL、PostgreSQL、Oracle支持)。读写操作不冲突，性能更高
  • 加锁：读写操作。加锁后，读写操作都会产生冲突

6.5.1 锁的分类

• 从类型角度：

  • 排他锁:X锁,一个事务对数据上排他锁,任何事务无法对他上锁,直到事务完成,锁解开,在此期间只有该事务能读取和修改
  • 共享锁:S锁,上锁后,其他事务只能对他上共享锁,不能上排他锁,只能读

  能否上锁	X锁	S锁
  上了X锁	❌	❌
  上了S锁	❌	✔️
  无锁	✔️	✔️

  MySQL

  -- 如果是修改操作会为行自动上X锁
  
  -- 查询上表 X 锁
  SELECT * FROM table_name FOR UPDATE;
  
  -- 查询上表 S 锁
  SELECT * FROM table_name LOCK IN SHARE MODE;
  -- MySQL 8.0+ 语法
  SELECT * FROM table_name FOR SHARE;
  
  -- 锁的超时机制
  -- 在 5.7 及之前版本，在加锁时遇到等待时间超过 innodb_lock_wait_timeout 设置的时长，会返回错误
  -- 在 8 以后版本，添加了 NOWAIT 和 SKIP LOCKED 选项
  -- NOWAIT：如果锁被其他事务占用，则立即返回错误
  -- SKIP LOCKED：如果锁被其他事务占用，则跳过该行数据
  SELECT * FROM table_name FOR UPDATE NOWAIT;
  SELECT * FROM table_name FOR UPDATE SKIP LOCKED;

  锁的设计思想

  • 悲观锁：总是假设最坏的情况，每次获取数据时都会上锁，直到操作完成，其他事务会阻塞，许多独占锁是此思想的体现，排他锁就是一种悲观锁，适合写操作多的场景

  • 乐观锁：认为对同一数据的并发操作是小概率事件，不用对数据上锁，在每次更新时判断在此期间有没有事务修改数据，一般通过程序实现，而不是采用数据库锁机制，适用于多读的应用类型，提高了吞吐量，不存在死锁问题，比如JAVA中的java.util.concurrent.atomic包

    • 版本号机制：在表中设计版本号字段，每次更新操作时执行update ... SET version=version+1 where version = version，如果被更新过了，version会发生变化，修改失败
    • 时间戳机制：在表中设计时间戳字段，每次更新操作时执行update ... SET timestamp=now() where timestamp = timestamp，如果被更新过了，之前读取的时间戳和表中数据不一致，修改失败

    -- 抢购商品
    -- 第一步读取商品余量
    SELECT quantity, version as version1 FROM products WHERE id = 1;
    
    -- 第二步，判断商品余量是否充足，如果充足生成订单
    INSERT INTO orders (order_id) VALUES (1001);
    
    -- 第三步，更新商品余量
    UPDATE products SET quantity = quantity - 1 and version=version+1 WHERE id = 1 and version = version1;

:::

---

• 数据操作的粒度划分：

  • 表锁：不依赖数据库引擎，且是开销最小的锁，每次对表上锁，并发性能打折扣，可以避免死锁

    MySQL

    -- MyISM 引擎支持表锁
    -- innodb_table_locks 为 1 时
    -- 才能使用 LOCK 对 InnoDB 上锁，实际上也是模拟的表锁
    -- 大部分情况下也不应该对 InnoDB 表进行锁操作
    SET auto_commit = 0, innodb_table_locks = 1;
    
    -- 加锁，加表锁后无法再对其他表操作
    LOCK TABLES 表名 WRITE;
    LOCK TABLES 表名 READ;
    
    -- 解锁
    UNLOCK TABLES;
    
    -- 查看已加锁的表
    SHOW OPEN TABLES WHERE In_use > 0;

    意向锁

    意向锁：在有表锁和行锁共存的数据库引擎中，一般存在意向锁

    • 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度(表锁与行锁)的锁并存

    • 意向锁是一种不与行级锁冲突的表级锁，由引擎维护，用户无法手动操作

    • 表明某个事务正在某些行持有了锁或该事务准备去持有锁

    • 作用：对行加锁时，系统会给对应页和表加一个意向锁，当另一个事务想要给某些数据页或表加锁时，可以访问意向锁(是否有事务锁定了其中的部分数据)，而不需要对每行进行访问

      • 意向锁是用于方便其他表锁查看是否有行上锁，所以意向锁之间是相互兼容的，可以对同表上不同的意向锁
      • 意向锁是表级锁，只会和表级的S和X锁冲突

    • 意向锁的类型

      • 意向排他锁internal exclusive lock：也称为IX锁，表明事务有意对某些行加排他锁
      • 意向共享锁internal shared lock：也称为IS锁，表明事务有意对某些行加共享锁

      数据库	对应锁名	备注
      MySQL InnoDB	IS、IX	与行锁同时存在；SIX未公开
      SQLServer	IS、IX、SIX	sys.dm_tran_locks.request_mode可见
      Oracle	TMSS、TMSX	v$lock.type='TM',lmode=2/3/4/5/6 映射为 IS/IX/S/X
      PostgreSQL	无显式意向锁	通过MVCC+行锁完成，无需表意向锁

  • 行锁：对于支持行锁的引擎比如InnoDB，通常不会对表上锁，而是对行上锁，并发性能提高，但会增加锁的开销，可能产生死锁

    • 增删查改：对进行增删查改操作的行添加锁
    • 表修改DDL：对进行DDL操作的表使用表级锁或类似等价机制实现，比如InnoDB使用元数据锁Matadata Lock实现
    • 其他情况手动上锁，比如为了崩溃恢复需要
    MySQL

    -- 行锁：仅InnoDB支持
    -- 1. 记录锁：对单条记录单独上`S/X`锁
    
    -- 示例：对id=5的记录加排他锁
    SELECT * FROM table_name WHERE id = 5 FOR UPDATE;
    
    -- 2. 间隙锁：对多条记录之间的空隙加锁，加锁通过查询可插入到此间隙的主键完成
    -- 不允许其他事务在此空隙插入记录，直到事务提交。多个事务可以同时对一个间隙加锁
    -- 对最小数据前或最大数据后加速是通过对数据和`Infimum+Supremum`虚拟最大、最小记录之间加锁实现的
    
    -- 对id为5和8之间的空隙加锁，只需要查询一个在(5,8)之间的id
    SELECT * FROM table_name WHERE id = 6 LOCK IN SHARE MODE;
    -- 表中最大id为50，在最大记录后加间隙锁
    SELECT * FROM table_name WHERE id > 50 LOCK IN SHARE MODE;
    
    -- 3. 临间锁(Next-Key)：记录锁和间隙锁的结合，即锁住当前记录，也锁住前面的空隙
    
    -- 示例：对5前面的空隙加锁，同时锁住id=5的记录
    SELECT * FROM table_name WHERE id <= 5 AND id > 3 FOR UPDATE;
    
    -- 4. 插入意向锁(Insert Intention)：在每次添加新的记录时，如果因为间隙锁等待，`InnoDB`规定在对应空隙生成插入意向锁，表明希望插入，但因为间隙锁等待
    -- 插入意向锁可以共存，可以看成是一种特殊的间隙锁
    -- 插入意向锁只要是对不同记录的插入，在等待结束之后，插入事务之间不会产生冲突，不会相互阻塞
    -- 插入意向锁是加在间隙后的记录上的，不会影响别的事务获取此记录上任何类型的锁
    -- 虽然有意向锁三个字，但实际上不属于意向锁，因为意向锁是表锁，插入意向锁是行锁
    -- 当执行INSERT操作遇到间隙锁时，MySQL会自动生成插入意向锁
    INSERT INTO table_name (id, name) VALUES (6, 'example');

  • 页锁：在页的粒度上锁定，锁定的数据资源比行锁多，开销介于行锁和页锁之间，会出现死锁，并发度一般

  锁的升级

  每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如行锁升级为表锁，这样占用的锁空间降低了，但同时数据的并发度也下降了

• 全局锁：对数据库上锁，数据库将处于只读的状态

  MySQL

  FLUSH TABLES WITH READ LOCK;

• 各数据库特有的锁

MySQL

• 自增锁：在MySQL中，自增AUTO_INCREMENT字段会加自增锁(AUTO-INC锁)
  • 一般情况下，插入数据有三种方式
    • 简单插入：使用插入语句插入固定数据，预先确定需要插入的数据条数，且均没有设置需要自增字段
    • 批量插入：从查询或加载数据的方法批量插入数据，不知道具体的条数
    • 混合模式插入：预先确定需要插入的数据条数，但部分数据设置了自增字段
  • 用于插入时需要获取自增字段的锁，因此效率较低，允许设置innodb_autoinc_lock_mode
    • 设置为0：传统模式，每个事务执行插入操作时都需要获取自增表级锁，确保插入的顺序和语句的顺序一致，同时确保主从auto_increment字段一致
    • MySQL 8之前默认1：连续模式，对于插入数量已知的事务，通过轻量锁的控制下，获取需要自增的值，避免表锁定，其他情况还是需要申请自增表级锁
    • MySQL 8之前默认2：交错模式，所有的事务都不会获取自增表级锁，如果重新执行操作，比如使用statement-base的binlog恢复，可能每个记录得到的自增值会不一致。执行简单插入时，可以确保同语句分配的自增值连续，执行批量或混合插入时，插入的自增值可能出现间隙
• 元数据锁：MDL锁是MySQL 5.5引入的，属于表锁范畴
  • MDL的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上
  • 因此，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁
  • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查
  • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题
  • 如果有读锁之后修改表，会等待读锁释放，但此时已经添加写锁，其他事务无法再读取
  • MDL锁不需要显式使用，在访问一个表的时候会被自动加上

6.5.2 加锁流程

MySQL

• 插入操作通过隐式锁的结构来保证其他事务无法在本事务提交前访问到此数据
  • 插入操作除非遇见间隙锁会创建插入意向锁，默认是不会添加锁的。但如果不添加锁，记录没有锁保护，可能遇到插入后的记录被其他事务读取或修改，产生脏读或脏写，因此设计了隐式锁结构
  • 通过最后修改此行的事务id信息确保不修改和读取还在执行的事务添加的行
    • 对于聚簇索引，每行有一个trx_id字段记录了最后修改此行的事务id
    • 对于二级索引，每个页面有PAGE_MAX_TRX_ID字段记录了最后修改此页面的事务id(也就是最大的事务id)，如果当前最小活跃的事务id大于这个id，则当前事务可以访问此行数据，否则需要回表查找此行的trx_id
  • 如果事务还在执行，则不能修改和读取此行数据，本事务会创建一个锁，并进行等待，这种延迟锁机制可以减少锁的数量
• 删除操作先定位到数据位置，然后获取X锁，再执行delete mark操作。定位数据相当于获取X锁的锁定读
• 修改操作
  • 如果修改了主键，相当于先删除该行数据，再插入新的数据(分配空间)，加锁操作
  • 如果修改未造成记录的空间变化，先寻找到记录的位置，再获取X锁，由锁提供保护
  • 如果修改造成了记录的空间变化，也是先找到记录的位置，然后删除该行数据，再插入新的数据(分配空间)，新记录由插入操作的隐式锁提供保护
• 查询操作需要利用锁的分类中的方法显式加排他锁和共享锁
• 整体流程：
  • 在查询和修改之前会查看该记录的最后事务是不是活跃的事务，如果是，将隐式锁转换为显式锁
  • 检查操作是否有锁冲突，如果有就创建锁，并等待
  • 等待加锁成功，被唤醒或者超时，报错
  • 执行操作，如果是修改数据，将当前trx_id写入数据行

6.5.3 死锁的处理

• 死锁：多个事务互相等待对方的锁，导致事务都无法继续执行

• 出现条件

  • 有两个或以上的事务
  • 每个事务都已经持有锁并且申请新的锁
  • 锁资源同时只能被同一个事务持有或者不兼容
  • 事务之间因为持有锁和申请锁导致彼此循环等待

• 死锁的处理

  • 超时法:对锁进行超时检测,由于时限不好控,容易发生误判

    MySQL

    -- 默认 50s
    SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

  • 等待图法wait-for graph:类似前趋图求串行化调度,$T_i$等待$T_j$有i到j的有向边,如果有回路,说明出现死锁

    • 发现死锁后,选择一个牺牲最小的事务回滚,使其他事务得以进行
    • 检测需要耗时$O(n^2)$，使用此法需要控制并发数量

• 死锁避免：

  • 将逻辑修改，减少锁冲突
  • 减少显式加锁
  • 将隔离级别调低
  • 避免大事务，发生冲突的概率也会小

• 锁监控

  MySQL

  -- 可以检查 InnoDB_row_lock 等变量来分析锁的争夺情况
  SHOW VARIABLES LIKE 'innodb_row_lock%';
  -- Innodb_row_1ock_waits:系统启动后到现在总共等待次数(等待总次数)
  -- Innodb_row_lock_time:从系统启动到现在锁定总时间长度(等待总时长)
  -- Innodb_row_lock_time_avg:每次等待所花平均时间(等待平均时长)
  -- Innodb_row_lock_current_waits:当前正在等待锁定的数量
  -- Innodb._row_lock_time_max:从系统启动到现在等待最常的一次所花的时间
  
  -- 查询 information_schema 数据库
  -- INNODB_TRX：事务信息
  -- INNODB_LOCKS(8.0后为data_locks)：事务的锁情况
  -- INNODB_LOCK_WAITS(8.0后为data_lock_waits)：锁等待情况

6.5.4 锁的结构

MySQL

• 锁的结构：
  • 锁所在的事务信息：不论是表锁还是行锁，都是在事务执行过程中生成的。锁所在的事务信息在内存结构中只是一个指针，通过指针可以找到内存中关于该事务的更多信息，比如事务id
  • 索引信息：记录加锁的记录属于哪个索引，是一个指针
  • 表锁/行锁的信息：
    • 表锁：记录对哪个表加锁，也有其他信息
    • 行锁：记载了Space ID表空间、Page Number页号、n_bits代表在行锁末尾使用了多少比特位(一般比表的条数多一点，避免插入后需要重新分配)
  • 类型type_mode：分为rec_lock_type、lock_mode和lock_type三部分，有32位
    • 锁的模式(1ock_mode),占用低4位，可选的值如下：
      • L0CK_IS(0):表示共享意向锁，也就是IS锁
      • L0CK_IX(1)：表示排他意向锁，也就是IX锁
      • L0CK_S(2)：表示共享锁，也就是S锁
      • L0CK_X(3)：表示排他锁，也就是X锁
      • LOCK_AUTO_INC(4)：表示AUTO-INC锁

      在InnoDB存储引擎中，LOCK_IS,LOCK_IX,LOCK_AUTO_INC都算是表级锁的模式，LOCK_S和LOCK_X既可以是表级锁的模式，也可以是行级锁的模式

    • 锁的类型(lock_type),占用第5~8位，不过现阶段只有第5位和第6位被使用：
      • L0CK_TABLE当第5个比特位置为1时，表示表级锁
      • L0CK_REC当第6个比特位置为1时，表示行级锁
    • 行锁的具体类型(rec_lock_type),使用其余的位来表示。只有在lock_type的值为L0CK_REC时，也就是只有在该锁为行级锁时，才会被细分为更多的类型：
      • LOCK_ORDINARY(0):表示临键锁
      • L0CK_GAP(512)：也就是当第10个比特位置为1时，表示间隙锁
      • L0CK_REC_NOT_GAP(1024):也就是当第11个比特位置为1时，表示记录锁
      • LOCK_INSERT_INTENTION(2048):也就是当第12个比特位置为1时，表示插入意向锁
      • LOCK_WAIT(256)：表示is_wait属性，为1时表示等待锁
      • 其他的类型：不常用
  • 其他信息：为了管理系统生成的锁结构而设计的链表和哈希表
  • 行锁末尾的bit位：在行锁末尾一个bit对应一个heap_no，也就是对应行是否加锁

6.6 MVVC

• 多版本并发控制(MVCC)：MVCC是通过数据行的多个版本管理来实现数据库的并发控制。换言之，就是查询一些正在被另一个事务更新的行，可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁
  • MVVC实现目的是提高并发性能，更好地处理读和写的冲突，采用乐观锁思想

  MVCC没有正式的标准，在不同的DBMS中MVCC的实现方式可能是不同的，也不是普遍使用的

MySQL

• InnoDB中的MVVC：InnoDB是支持MVCC的数据库引擎
• 支持快照读：不加锁的查询都是快照读，读取的是快照数据，即不加锁的非阻塞读，避免了加锁，但读到的数据可能是历史版本，串行隔离级别下会退化成当前读(相当于加锁)
• 实现原理：多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在Undo Log里
  • 如果一个事务想要查询这个行记录，就需要用到ReadView了，它帮我们解决了行的可见性问题
  • ReadView就是事务在使用MVCC机制进行快照读操作时产生的读视图
  • 当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务id
• ReadView包含
  • creator_trx_id：创建此ReadView的事务id
  • trx_ids：创建事务时活跃的读写事务id列表
  • up_limit_id：最小的活跃事务id
  • low_limit_id：应该分配给下一个事务的id
• 使用规则
  • 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问
  • 如果被访问版本的trx_id属性值小于ReadView中的up_limit_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问
  • 如果被访问版本的trx_id属性值大于或等于ReadView中的low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
  • 如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断一下trx_id属性值是不是在trx_1ds列表中
    • 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问
    • 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问
• 快照读的流程
  • 首先获取事务自己的版本号，也就是事务id
  • 获取ReadView，读视图相当于此时的活跃事务快照
  • 查询得到的数据，然后与ReadView中的事务版本号进行比较
  • 如果不符合ReadView规则，就需要从UndoLog中获取历史快照
  • 直到所有数据符合要求

  在读已提交隔离级别下，每次读取都需要获取ReadView；在可重复度隔离级别下，只有第一次读取需要获取ReadView；实际上MVVC也解决了幻读，因为活跃事务的修改并不展示