0%

MySQL事务与锁

事务

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):无论进行读或者写都将给数据加锁,导致事务必须串行化队列进行。

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

1
2
3
4
5
6
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

查看当前事务隔离级别

1
2
3
4
5
6
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范围内的任意时间点。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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加锁,也是当前读,读取当前记录,比如下列方式:

1
2
3
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. 唯一索引上的范围查询会访问到不满足条件的第一个值位置

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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个,以此类推每次都会申请上一次的两倍。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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)