光与影的个人博客

NEUCoder

0%

MySQL事务

发表于 更新于 分类于 ，

MySQL事务

之前在分布式系统的学习中已经系统的整理了事务ACID以及隔离性概念，下面总结在MySQL中的事务，偏向于实现层面，主要内容来自《MySQL技术内幕:InnoDB存储引擎(第2版)》。

事务分类和使用

MySQL 默认提交事务（autocommit = 1），即执行完SQL一句后立马执行COMMIT操作，也就是所谓的隐性事务。显式事务的开启，需要手动输入事务控制语句，：

  • START TRANSACTION|BEGIN：开启事务
  • COMMIT：提交事务
  • ROLLBACK：回滚事务
  • SAVEPOINT identifier：创建事务中的保存点
  • REPEASE SAVEPOINT identifier：删除事务中的保存点
  • ROLLBACK TO[SACEPOINT]indentifier：回滚的指定的保存点

其中包括DDL等在内的数据库管理和修改操作开启隐性事务，无法在显式事务控制中使用。

系统的隔离级别由变量transaction_isolation变量控制，通过以下两种方式修改：

1
2
3
4
# 1.变量修改方式
set transaction_isolation = 'repeatable-read';
# 2.控制语句形式
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

事务的分类包括：

  1. 扁平事务（Flat Transactions）
  2. 带有保存点的扁平事务（with savepoints）
  3. 链事务（Chained Transactions）
  4. 嵌套事务（Nested Transactions）
  5. 分布式事务（Distributed Transactions）

链式事务可以理解为多个连续的事务，一个事务结束其后继事务立刻在相同的隔离级别以及访问模式中继续执行。MySQL中开启方式为

  1. 通过全局变量设置completion_type = 2

  2. 结束提交使用COMMIT AND CHAIN

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    START TRANSACTION;
               UPDATE accounts
                  SET balance = balance - 1500
                WHERE id = 1;
               UPDATE accounts
                  SET balance = balance + 1500
                WHERE id = 2;
     COMMIT AND CHAIN;
               UPDATE accounts
                  SET balance = balance - 1000
                WHERE id = 2;
               UPDATE accounts
                  SET balance = balance + 1000
                WHERE id = 3;
    COMMIT;

嵌套事务是一个多个事务按照树状层次结构组成的复合事务

  • 上层事务控制下层事务的提交和回滚
  • MySQL并不直接支持

原子性+持久性实现

Mysql中事务的原子性和持久性，基于redo和undo日志实现：

  1. redo日志：记录事务操作的日志，在事务提交前首先持久化redo日志，以实现事务的持久性
  2. undo日志：用于撤销事务操作，回滚数据库状态到事务开始之前，实现了事务的原子性

redo日志

redo日志保证持久性的原理可以简单理解为：

  1. 受限于磁盘读写性能，每次数据库的增删改操作都写入磁盘，会导致数据库性能较差
  2. 为了解决这一问题，引入了缓冲，先在内存缓冲中执行对应修改操作，在合适的时机写入磁盘
  3. 然而内存中的数据是易失的（volatile），如果内存数据写入磁盘之前丢失，则数据库的持久性遭到破坏
  4. 为了保证持久性，引入redo日志，记录修改操作，并持久化到磁盘

相当于用操作记录的持久化成本替代操作本身持久化成本，最终在保证持久性的情况下实现性能的提升。

然而redo日志本身持久化也类似于数据库持久化问题，也面临着性能和持久化的权衡，InnoDB中分为内存和磁盘两部分：

  1. Log Buffer：内存中的redo log buffer，事务在执行时实时写入日志到当前位置。
  2. Redo Log File：磁盘中存储redo log的文件 ，在事务提交前，会将Log Buffer中的日志持久化到Redo Log File中

InnoDB提供了innodb_flush_log_at_trx_commit变量控制Log Buffer持久化时机：

  • 0：事务提交时不进行redo log持久化，此时持久化时机只剩下master thread每1s执行一次的fsync。
  • 1：提交时将redo log写入Redo Log File，且调用fsync，保证真正写入。
  • 2：提交时将redo log写入Redo Log File，但不调用fsync，写入操作系统缓存。

Log Buffer中的日志刷新到磁盘的时机为：

  1. 事务提交时，具体刷新行为由innodb_flush_log_at_trx_commit控制
  2. 当Log Buffer一半的空间被使用完毕时
  3. Log checkpoint（包括Master thread Checkpoint等，具体未深入了解）

另外两个概念控制redo log的扩张收缩：

  1. LSN（Log Sequence Number）：日志序列号，记录当前redo log数量，其单位为字节数
  2. Checkpoint：检查点，代表已经完成持久化数据对应的操作号，当脏页刷新到磁盘中时，其已经实现了持久化，对应的redo log也就没必要存在。

最后补充一点，redo log以512byte大小的block进行存储，大小于磁盘扇区相同，因此不需要doublewrite避免数据损坏。

undo日志

undo日志可以说是redo日志的反方向的钟，用来实现事务的回滚操作，两者区别在于：

  1. undo日志记录逻辑操作，redo日志记录物理操作
  2. undo日志类似于临时文件，当“不需要回退时”undo日志即可删除，其持久化需要通过redo日志实现

undo的回退不是时光倒流，而是通过执行逆操作抵消历史操作，即delete->insert、insert->delete、update->update back，产生的时机为：

  1. 用户自定义表上的insert,update,delete操作
  2. 用户定义临时表上的insert,update,delete操作，其中临时表上的undo日志由于不需要错误恢复，不存在对应redo日志

undo日志的生命流程可以概括为：

  1. 开启事务，每执行一个SQL，产生对应的undo日志（undo日志会产生对应的redo日志）
  2. 当事务提交时，若无其他事务依赖（insert），则undo log直接删除；若存在其他事务依赖（mvvc依赖于undo实现delete、update的隔离），等待依赖事务结束，由purge操作统一回收。

purge操作用来真正的执行delete,update操作，回收对应undo日志

  • 由于隔离性（或者说mvvc）要求，delete update操作并不真正的执行，而是由purge操作统一处理
  • purge回收undo日志执行对应的delete,update操作，回收采用了一定的优化，缓解undolog随机读取的性能问题

undo日志存储按照段形式组织，undo日志存储在undo slot中

  1. 两个table space：根据是否为临时表分别将对应undo日志存储在undo table space和global table space

  2. 128个 rollback segment：每个table space中包括128个回滚段

  3. 1024个 undo segment：每个rollback segment根据页大小，对应不同数量undo undo segment

    1
    (innodb_page_size / 16) * innodb_rollback_segments * number of undo tablespaces

隔离性实现-锁

锁是InnoDB中进行并发控制，保证隔离性的手段之一，主要的分类方式有

  1. 锁的粒度：表级锁、行级锁（record lock）、页锁
  2. 锁的排他性：共享（shared）锁和排他(Exclusive)锁

下面总结InnoDB中主要的锁类型

Intention Locks（意向锁）

为了实现multiple granularity locking，事务在获取任意行级锁之前，首先获取Intention Locks，意向锁为表级别锁，与普通表级别锁的区别在于：

  1. 意向锁之间并不互斥
  2. 意向锁与其他普通表级锁满足一般的互斥性质

上述机制实现了多粒度，访问行只获取行级锁，后续当有操作需要获取表级锁时，由于只需要判断是否与意向锁互斥，而不需要遍历整个表，判断是否存在互斥行级锁。

综上所述，意向锁实际上可以理解为行锁的表级别标记

Gap Locks（间隙锁）

结合幻读场景理解，间隙锁对index范围上锁，如下SQL所示，该查询语句对范围10-20区间（开区间）上锁，若要插入一条c1=15的记录，需要等待当前锁释放。

1
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

Gap Locks存在的目的是为了阻止其他事务在当前事务涉及到对应区间数据时，向该区间内插入新的数据

  • 当数据库隔离级别为READ COMMITED时，Gap Locks关闭不再使用

Next-Key Locks

行锁和间隙锁的结合，锁定一条记录以及其左区间（索引确定的范围），假设表中包含index=10, 11, 13, and 20四条数据，则可能的Next-Key Lock上锁位置可以有

1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

InnoDB的REPEATABLE READ隔离级别中通过Next-Key Lock，实现幻读问题的解决

Insert Intention Locks（插入意向锁）

插入意向锁为了解决多个事务插入相同位置（相同主键）的情况，由于目标插入数据项不存在，不能使用普通的行锁解决，只能通过”意向”的方式，实现互斥。案例如下：

  1. 事务1获取对应区间的间隙锁

    1
    2
    3
    4
    5
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from test_select where id > 4 for share;
    Empty set (0.00 sec)

  2. 事务2此时向对应间隙插入数据，获取插入意向锁与间隙锁互斥，会等待事务1释放锁

    1
    2
    3
    4
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test_select values(5,6,7);

  3. 使用show engine innodb status命令查看系统当前锁，可以看到事务在等待锁释放

    1
    2
    3
    4
    insert into test_select values(5,6,7)
    ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 68 page no 4 n bits 72 index PRIMARY of table `test_db`.`test_select` trx id 9758 lock_mode X insert intention waiting
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

AUTO-INC Locks(自增锁)

当表具有自增（Auto Increment）字段时，为了避免并发插入导致的自增字段出现重复，插入前首先获得自增锁，该锁将并发插入变为了顺序插入，保证了自增字段逐个递增。

由于严格的自增锁会严重的降低并发性能，MySQL提供了innodb_autoinc_lock_mode 参数配置插入的不同机制（这部分内容还有问题，详细参考文档AUTO_INCREMENT Handling in InnoDB）

  1. 0：tradition模式，每个插入语句插入前获取AUTO-INC Lock，插入执行完毕后，释放锁
  2. 1：consecutive模式，如果已知insert语句要插入的数据条数，提前分配对应数量的自增值，避免长时间占有锁
  3. 2：interleaved模式：不使用表级锁，多个事务可并发插入，保证自增字段的唯一性，但不保证一个插入语句中插入的数据是连续的

死锁

InnoDB通过死锁检测手段，在发生死锁情况时主动关闭死锁依赖中最小的事务，从而破除思索。有两个参数控制：

  1. innodb_deadlock_detect：默认处于开启状态，表示是否进行死锁检测

  2. innodb_lock_wait_timeout：等待获取锁的超时时间，当一个事务超过一定时间等待锁，InnoDB会终止该事务

    1
    2
    mysql> insert into test_select values(5,6,7);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

隔离性实现-MVCC

Multiversion concurrency control (MVCC) is a database optimization technique that creates duplicate copies of records so that data can be safely read and updated at the same time.

MVCC(Multiversion concurrency control) 多版本并发控制是数据库优化并发性能的一种手段，通过创建读副本，在不使用锁的情况下，解决并发读写的冲突问题，整体上简单理解MVCC：

  1. 每个修改（insert、update、delete等）操作都会导致产生一个新版本的数据，并不会影响老版本数据
  2. 读操作会从有限多个版本的数据选择合适的数据进行读取，以满足隔离性要求。

MVCC虽然有效的避免了读写冲突，提升了并发性能，但存在一定问题

  1. MVCC机制难以设计和实现
  2. 不同版本的数据清理回收的时机难以确定

InnoDB MVCC实现

InnoDB的实现并没有采用多个数据版本的形式，而是采用单一数据+undo日志的实现思路

  1. 所有数据行存在两个隐藏属性：trx_id记录最新修改的事务ID，作为并发版本时间戳；roll_pointer指向最新undo log
  2. 所有的写操作（insert，update）直接写入数据，并不是生成一个新版本的数据；删除（delete）操作并不真的删除数据项，等待删除事务提交后，MVCC无需使用时，由purge操作一并回收
  3. 读操作根据构建ReadView，根据读取事务ID和读取数据行trx_id，判断是否需要基于undo log 回退

ReadView用于维护并发事务的时间戳信息，在读取过程中指导回退操作，主要包括：

  1. creator_trx_id：当前事务ID
  2. trx_ids：与当前事务存在冲突的并发事务ID列表
  3. up_limit_in：最小事务IDmin(trx_ids)，用来判断”过去”的事务
  4. low_limit_id：当前事务创建时，未分配的最小事务ID，用来判断”未来”的事务

判断是否需要回退避免冲突的条件如下：

  1. 可直接访问的情况（待访问数据行的trx_id ）
    • trx_id = creator_trx_id（自己的修改自己能看到）；
    • trx_id <= up_limit_in（历史已提交的事务能看到）;
    • trx_id <low_limit_id 且落在trx_ids外（非未来且和自己非并发的事务能看到）；
  2. 需要访问undo log回退到历史数据的情况
    • trx_id 落在trx_ids内（和自己并发未提交的修改）
    • trx_id >=low_limit_id （在自己之后事务修改）

上述判断条件的本质上就是保证当前事务只能看到历史版本的数据，Mysql只在两个隔离级别中使用MVVC

  1. Read Committed：一次事务中的每一次读操作都重新生成ReadView
  2. Repeatable Read：一次事务只在第一次读取操作时生成ReadView

区别于MySQL，PostgreSQL写新数据时，旧数据不删除，直接插入新数据，新旧数据上保存对应的事务ID，通过新旧数据+事务ID实现MVCC机制

一个案例展示MVCC

在MySQL的Repeatable Read隔离级别下，并行执行两个事务：

  1. 事务1查询表中所有数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> select * from test_select;
    +----+------+------+
    | id | age  | sex  |
    +----+------+------+
    |  1 |    2 |    3 |
    |  2 |    3 |    4 |
    |  3 |    4 |    5 |
    |  4 |    5 |    6 |
    +----+------+------+

  2. 事务2更新id=2的数据，并提交

    1
    2
    mysql> update test_select set age = 10 where id = 2;
    Query OK, 1 row affected (0.00 sec)

  3. 事务1查询id=2的数据，无变化

    1
    2
    3
    4
    5
    6
    mysql> select * from test_select where id = 2;
    +----+------+------+
    | id | age  | sex  |
    +----+------+------+
    |  2 |    3 |    4 |
    +----+------+------+

  4. 事务1更新id=2的数据，并查询结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> update test_select set age = age + 1 where id = 2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> select * from test_select where id = 2;
    +----+------+------+
    | id | age  | sex  |
    +----+------+------+
    |  2 |   11 |    4 |
    +----+------+------+

案例的结果表明，MVCC隔离读写操作，由快照读实现，所有的修改都实时的反映在最新数据上。

补充-分布式事务

MySQL支持分布式事务，称为XA事务，其中XA是由X/Open组织提出的分布式事务的规范，一个XA事务由一个或者多个资源管理器（数据库）、一个事务管理器以及一个应用程序组成

  • 基于两阶段提交（two-phase commit）方式实现，事务管理器为事务协调者
  • 资源管理器相当于分布式事务中参与的一个个数据库

总结

从本科开始接触数据库事务这一概念，一带而过没有深入学习，经过这段时间从理论以及MySQL实现角度的学习和总结，对于事务有了一定程度的理解，更能体会并发控制的困难和事务实现设计的巧妙，希望在以后的编码过程中，能够用到事务学习中了解到的并发控制思路。