事务

ACID

ACID即事务的四种特性：

  • 原子性(Atomicity)：事务中的操作要么全部成功(commit)，要么全部失败(rollback)
  • 一致性(Consistency)：数据库总是从一个一致性状态切换到另一个一致性状态
  • 隔离性(Isolation)：一个事务所做的修改对其它事务不可见，好似串行执行
  • 持久性(Durability)：事务提交后持久化到数据库磁盘中保存

事务类别

  1. 普通事务。显示的启动事务可以通过执行begin或start transaction来开启一个事务，在执行第一个Innodb表SQL的时候事务才真正启动，如果要立刻启动可执行start transcation with consistent snapshot。结束事务需要执行commit提交事务或者rollback回滚事务
  2. 链表式事务。如果介意每次都要执行begin显式开启，多一次交互，可以通过commit work and chain语法在提交的同时开启一个新的事务
  3. 带有savepoint保存点的事务。在事务中通过savepoint创建保存点，后续可以rollback to savepoint回滚到保存点，而不必回滚整个事务。
  4. 分布式事务。通过分布式协议实现多个节点之间的事务

事务隔离级别

事务的隔离性要求解决脏读、不可重复读、幻读问题。MySQL支持的事务隔离级别如下，隔离力度越大，效率就会越低：

  • 读未提交(read uncommitted)：即事务能看到其它事务未提交的数据(脏读)，如果事务后续发生回退，就出现了数据不一致的情况，因此该级别在现实场景下意义不大

  • 读提交(read committed)：事务提交后，其它事务才能访问到事务变更的数据，RC模式也是大多数应用场景下使用的事务级别，但未解决可重复读和幻读问题

  • 可重复读(repeatable read)：一个事务执行过程中看到的数据总是一致的，即使其它事务进行了数据变更并提交，事务内部获取的还是事务启动时间点的数据。符合真正的隔离性要求。

  • 串行化(serializable)：无论进行读或者写都将给数据加锁，导致事务必须串行化队列进行。

假如当前存在如下字段数据，用于测试不同隔离级的区别

mysql> select * from tab1;
+------+
| id   |
+------+
|    1 |
+------+

id 事务A 事务B
1 begin;select * from tab1 begin
2 select select * from tab1
3 update tab1 set id=2 where id=1
4 select * from tab1
5 commit
6 select * from tab1
7 commit
8 select * from tab1
  • 读未提交：由于事务A能读到事务B未提交的事务，因此在id 4，6，7的事务A查询中返回的结果都是2
  • 读提交：ID 4中事务A查询步到事务B还未提交的数据，因此ID 4的查询结果还是1，但当事务B提交完成后，ID 6和8返回的结果都是2
  • 可重复读：可重复读在事务结束之前读取的数据都是事务开始时间的数据，因此ID 4和6的查询结果都是1，ID 8因为事务已提交，因此返回的是最新数据2
  • 串行化：ID 1中事务A对表进行了查询，当事务B再执行update时就会被锁住，直到事务A提交，因此ID 4和6返回1，ID8返回2

查看当前事务隔离级别

mysql> show variables like 'tx_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

Redo Log

事务的ACID是基于REDO和UNDO实现的。在一个高并发的OLTP系统中，如果每次DML操作都要从数据文件拉取对应记录修改再回写到数据文件中，这样产生的IO成本和时间成本都太高。因此MySQL采用了WAL(Write-Ahead Logging)机制，即先写日志，再写磁盘。InnoDB将更新记录写到redo log buffer中，然后同步刷新到重做日志文件，这个时候就已经完成了。redo log buffer的大小由参数innodb_log_buffer_size控制，默认为8MB。在下列三种情况下，会将redo log buffer中的内容刷新到redo log磁盘中

  • Master Thread每一秒将重做日志缓冲刷新到重做日志文件
  • 每个事务提交时会将重做日志缓冲刷新到重做日志文件
  • 当重做日志缓冲池空间小于1/2时，重做日志缓冲会刷新到重做日志文件

重做日志文件大小是固定的，由参数innodb_log_files_in_group控制文件数量，innodb_log_file_size控制文件大小。从头开始写，写到最后再回到第一个文件的开始循环写入
redo
write pos是当前记录的位置，一边写一边往后移，写道logfile3末尾就从logfile0的开始位置继续写；checkpoint是要擦除的位置，擦除前需要确保脏页数据已经刷新到磁盘中。两者之间的空间就是干净页，可以用于记录新的操作，如果write pos追上checkpoint就表示redo log满了，这时需要先推进checkpoint脏页刷新，因此需要确保redo log file足够大，否则频繁写满会导致性能下降。

Redo log能够在实例崩溃或异常重启时，保证已提交的记录不会丢失，这种能力称之为crash-safe。因此建议将参数innodb_flush_log_at_trx_commit设置为1，确保每个事务的redo log持久化到磁盘。

binlog

Redo log是Innodb存储引擎特有的日志，并且是覆盖写，不具备基于时间点恢复的作用。而Server层也有自己的日志，也就是binlog(归档日志)，binlog是逻辑日志，记录了语句原始的逻辑，但不具备crash-safe的能力。binlog是追加写入的，写到一定大小会写入下一个文件中，并不覆盖之前的文件。由于binlog记录了所有逻辑操作，且文件不会覆盖，因此我们可以通过binlog将数据库恢复到binlog范围内的任意时间点。

mysql> show binlog events in 'mysql-bin.000007'\G
*************************** 4. row ***************************
   Log_name: mysql-bin.000007
        Pos: 259
 Event_type: Query
  Server_id: 3307
End_log_pos: 338
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql-bin.000007
        Pos: 338
 Event_type: Rows_query
  Server_id: 3307
End_log_pos: 610
       Info: # INSERT INTO `departments` VALUES
('d001','Marketing'),
('d002','Finance'),
('d003','Human Resources'),
('d004','Production'),
('d005','Development'),
('d006','Quality Management'),
('d007','Sales'),
('d008','Research'),
('d009','Customer Service')
*************************** 6. row ***************************
   Log_name: mysql-bin.000007
        Pos: 610
 Event_type: Table_map
  Server_id: 3307
End_log_pos: 674
       Info: table_id: 225 (employees.departments)

二进制日志由参数log-bin控制，我们可以指定其文件路径及文件名格式，生成的index文件为二进制的索引文件，用来存放过往的二进制日志序号。二进制日志记录的信息和行为同时收到下列参数的影响：

  • max_binlog_size：指定了单个二进制日志文件的最大值，如果超过该值则生成新的二进制日志文件，序号+1并记录到index文件中
  • binlog_cache_size：系统给binlog分配了一块内存，由参数binlog_cache_size控制单个线程binlog cache的大小，如果超过限制则放到临时文件中，事务提交后则写入binlog并清空binlog cache。通过show global status查看binlog_cache_use和binlog_cache_disk_use的状态，可以判断binlog_cache_size是否设置合理。
  • sync_binlog：binlog写入时是先写到文件系统的page cache，并没有持久化到磁盘，等待fsync将数据持久化到磁盘上。这个机制是由参数sync_binlog控制的，为0时表示每次提交事务只写到page cache，不做fsync；为1时每次提交事务都会执行fsync；大于1时则表示每次提交事务写入到page cache，等累计N个事务再fsync，设置为大于1的值可能会在主机异常重启后丢失N个事务的binlog日志
  • log-slave-update：如果当前实例在复制中是slave角色，想要将master获取并执行的二进制日志写入自身的二进制日志文件中，需要开启log-slave-update，特别是在级联复制的场景下
  • binlog_format：binlog_format影响了记录二进制日志的格式，其有三个可选值：ROW、STATEMENT、MIXED。STATEMENT格式记录的是逻辑SQL语句；ROW格式记录表的行更改情况，导致二进制日志大小变大，RC隔离级别下必须设置为ROW；MIXED格式下，默认采用STATEMENT格式记录二进制日志，在部分情况下会使用ROW格式。

两阶段提交

为了确保binlog和redo log之间的逻辑一致性，引入了两阶段提交的过程，在数据更新之后先写入redo log并处于prepare阶段，再写入binlog，binlog写入完成后commit整个事务。

update

MVCC(多版本并发控制)

版本链

对于Innodb存储引擎的表，它的聚簇索引都包含两个隐藏列trx_id和roll_pointer。在每次对聚簇索引进行改动时都将当前事务id赋值给trx_id，将旧版本写入到undo日志中，对应的指针信息存入roll_pointer，用于查找记录修改前的信息。多个UNDO串联起来，就形成了一个链表，对记录的每次修改都会生成一个undo，当作该记录的一个版本，随着不断的更新所有版本的roll_pointer属性链接成一个链表，称之为版本链，版本链的头节点就是当前记录。当UNDO日志不在需要时就会被删除，这时已经没有比UNDO日志更早的readview了，因此我们应当避免使用长事务，否则UNDO一直无法被释放而占用大量空间

ReadView

在RR(可重复读)和RC(读提交)模式下，需要使用到上述的版本链，但是需要确认哪些版本是对当前事务可见的。因此Innodb设计了ReadView的概念，在事务生成readview时，会把当前系统正在执行的读写事务写入到m_ids列表中，另外还会记录min_trx_id(m_ids中最小的事务ID)和max_trx_id(分配给下一个事务的ID)，所以判断事务可见性的规则如下：

  • 如果记录的trx_id小于min_trx_id，则可见
  • 如果记录的trx_id大于max_trx_id，则不可见
  • 如果记录的trx_id在min_trx_id和max_trx_id之间，并存在于m_ids列表中则不可见，否则可见

如果某个版本对事务不可见，则顺着版本链继续查找上一个版本，一直查到最后一个版本都不可见，查询结果就不包含该记录。

RC和RR的区别在于生成readview的时机，RC是在每次读取数据时生成一个readview，所以readview总是当前已提交的数据；RR是在第一次读取数据时生成一个readview，并一直沿用至事务结束，因此readview总是事务第一次读取的数据内容。

当前读

transaction

在RR模式下，事务C没有显式启动事务，在SQL执行完成后自动提交。事务B执行+1后进行查询，查询到的结果是3，因为更新都是先读后写的，而读只能读当前值，否则事务提交之前其它事务提交的更新将全部丢失，这种机制称之为当前读。事务A在事务C和事务B执行更新提交前就开启了，因此它的查询结果还是1。这时如果给事务A中的select加锁，也是当前读，读取当前记录，比如下列方式：

sql> select k from t where id=1 lock in share mode;
or
sql> select k from t where id=1 for update;

锁

数据库锁的设计初衷是为了处理事务并发问题，当出现多并发访问时，数据库要合理安排资源访问规则，具体实现就是通过锁的方式。根据锁的粒度，可以分为全局锁，表锁和行锁。锁的粒度越大，并发性则越低。

全局锁

全局锁就是对整个数据库加锁，MySQL提供了一个全局加锁的方法，flush tables with read lock(FTWRL)，执行后整个数据库处于只读状态，DDL或DML都会被阻塞。全局锁通常用于针对全库做逻辑备份，否则备份出来的时间点不同，造成数据错误。

结合之前隔离级别的描述，在可重复读的级别下，也可以做到一致性视图。官方自带逻辑备份工具mysqldump中的参数–single-transcation就是在导出数据前开启一个事务，来确保一致性，过程中由于MVCC特性，并不影响业务操作。

意向锁

意向锁是用于揭示下一层级的锁，是实现多粒度锁的基础。MySQL的意向锁通常是加在表上的，并且意向锁是相互兼容的，当表上已经存在行锁时，再想加表锁的话就可以直接通过意向锁来判断，而不需要遍历所有行记录。

表级锁

MySQL中表级锁有两种：表锁和原数据锁(MDL)。

表锁可以通过lock table [ read | write ]添加表锁，解锁则执行unlock tables或者客户端断开时自动释放。需要注意的是lock table不仅限制别的线程读写，也限定了自身后续操作的对象。

MDL锁不用显示执行，在访问一个表的时候会自动加上，保证读写的正确性。MDL是在5.5中引入的，在执行查询或DML时添加MDL读锁，针对表结构进行修改时添加写锁，读锁之间不互斥，读写或写锁之间是冲突的，用于保持表结构操作的安全性。MDL是在事务提交后才进行释放的，因此要避免长事务长时间占用MDL锁，到时业务阻塞。

行锁

行锁是针对数据行的锁，当一个事务更新了一行，另一个事务也要更新同一行，则会被锁住，直到第一个事务commit，也就是两阶段锁协议。基于两阶段锁协议原则，如果事务中需要锁多个行，要把最可能发生冲突的，最影响并发的锁尽量往后放。

死锁

当并发系统不同线程出现循环资源依赖，都在等待对方线程释放资源资源，陷入无限循环等待的状态，称之为死锁。针对死锁的情况有两种应对方案：

  • 设置超时时间，由参数innodb_lock_wait_timeout控制，超时则退出。但时间不好控制，长了业务接受不了，等待时间太长，短了容易误判，将正常的锁等待误杀。
  • 通过死锁检测，发现后，主动回滚一个事务，让其它事务继续执行。设置参数innodb_deadlock_detect为on开启死锁检测，检查每一个锁住的事务，判断是不是循环等待。但如果阻塞队列很长，检测这个动作就需要消耗大量CPU资源

GAP锁

在RR模式下为了解决幻读问题引入了间隙锁(GAP LOCK),间隙锁就是在两个值的空隙中加入锁，避免新数据的插入，间隙锁之间不存在冲突。间隙锁和行锁合称next-key lock，每个next-key lock都是前开后闭区间。

幻读是在指一个事务前后两次查询，第二次看到了第一次所没有的行。

  • 在重复读隔离级别下，普通的查询是快照读，是不会看见其它事务插入的数据。幻读只出现在当前读的情况下
  • 幻读仅指新插入的行

加锁规则：

  1. 加锁的基本单位是next-key lock
  2. 查询过程中访问到的对象才会加锁
  3. 索引上的等值查询，为唯一索引加锁时，next-key lock退化为行锁
  4. 索引上的等值查询，向右遍历到最后一个值不满足等值条件时，next-key lock退化为间隙锁
  5. 唯一索引上的范围查询会访问到不满足条件的第一个值位置

通过下面例子，来简单了解一下加锁规则

mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    0 |    0 |
|  5 |    5 |    5 |
| 10 |   10 |   10 |
| 15 |   15 |   15 |
| 20 |   20 |   20 |
| 25 |   25 |   25 |
+----+------+------+
6 rows in set (0.00 sec)

lock

  • 根据规则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock
  • c为普通二级索引，因此需要查到到第一个不满足的值，所以查到10才会停下来。根据原则2，访问到的都要加锁，因此要给(5,10]加上next-key lock
  • 根据第4点，退化为(5,10)间隙锁
  • 根据第2点，查询采用了覆盖索引，不需要访问主键索引，因此SESSION B执行成功，SESSION C则会被(5,10)的间隙锁阻塞

自增锁

在创建表时，我们通常会选择自增值来作为主键。在获取自增值时，会产生自增锁，但自增锁和上面的锁不一样，它在是SQL执行完成之后就释放的锁。

在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

如果在表上ID列设置AUTO_INCREMENT时，如果插入数据时ID指定为0、null或者空值，那么就把自增值填充到自增字段，如果插入了具体值，就使用插入指定的值。根据插入的值和当前自增值的大小关系，自增值的变化也有所不同，如果插入值小于自增值，那自增值不变，否则就修改为新的自增值，新的自增值算法为从auto_increment_offset开始，以auto_increment_increment为步长，不断叠加，找到第一个大于插入值的值作为新的自增值

当插入失败或者事务回退时，自增id锁并不是事务锁，而是每次申请完自增值后立马释放，以便其它业务再进行申请，分配的自增值并不会回收。实际上在MySQL5.0中，自增锁是语句级别的，需要等到语句执行结束后释放，并发性收到影响。

在MySQL5.1.22中，引入参数innodb_autoinc_lock_mode，为0则表示采用MySQL5.0的策略；为1时普通insert语句自增锁申请后立马释放，insert…select这类的批量插入语句，自增锁需要等待语句结束；
为2时所有申请都是申请后立即释放。默认为1，如果insert…select的操作比较多建议设置为2并将binlog_format设置为row，在MySQL8.0.3后，如果binlog_format为row的前提下，innodb_autoinc_lock_mode默认为2

注：在insert语句指定多个value插入的情况下，即使innodb_autoinc_lock_mode为1，也是申请后立即释放的

另外对于批量插入语句时，MySQL有一个批量申请自增ID的策略：语句执行过程中第一次申请会分配1个，第二次申请会分配2个，第三次申请会分配4个，以此类推每次都会申请上一次的两倍。

mysql> create table t (a int primary key auto_increment, b int,c int);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t values(null, 1,1);
mysql> insert into t values(null, 2,2);
mysql> insert into t values(null, 3,3);
mysql> insert into t values(null, 4,4);

mysql> create table t2 like t;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t2(b,c) select b,c from t;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(null, 5,5);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    2 |    2 |
| 3 |    3 |    3 |
| 4 |    4 |    4 |
| 8 |    5 |    5 |
+---+------+------+
5 rows in set (0.00 sec)