MySQL的并发

MVCC

基本概念

标准的事务隔离级别：

READ UNCOMMITTED ：未提交读
READ COMMITTED ：已提交读
REPEATABLE READ ：可重复读
SERIALIZABLE ：可串行化

隔离级别允许发生的严重程度

READ UNCOMMITTED 隔离级别下，可能发生脏读、不可重复读和幻读问题。
READ COMMITTED 隔离级别下，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。
REPEATABLE READ 隔离级别下，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。
SERIALIZABLE 隔离级别下，各种问题都不可以发生

两个必要隐藏列（trx_id、roll_pointer）

trx_id ：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id 赋值给 trx_id 隐藏列
roll_pointer ：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

MVCC通过版本链实现的，增量的数据是存放在 undo page中的
然后通过 roll_pointer 指针指向undo page的最新版本
如果通过 trx_id 发现，不可见则继续往前查找，直到能找到这个版本，否则找不到

ReadView

通过版本链来判断事务的可见性

几个重要的属性

名字	解释
m_ids	表示在生成 ReadView 时当前系统中活跃的读写事务的事务id 列表
min_trx_id	表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务id ，也就是m_ids 中的最小值
max_trx_id	表示生成 ReadView 时系统中应该分配给下一个事务的 id 值
creator_trx_id	表示生成该 ReadView 的事务的事务id

判断记录某个版本是否可见的步骤

被访问版本的trx_id与 ReadView 中的 creator_trx_id相同，表示修改自己的记录，可以访问
被访问版本的trx_id小于 ReadView 中的min_trx_id，该事务在生成ReadView前已提交，可以访问
trx_id大于ReadView中的max_trx_id，在当前事务生成ReadView之后产生的，不可见
trx_id在ReadView的 min_trx_id 和 max_trx_id 之间，需要判断trx_id 是否在 m_ids 列表中，如果在说明创建ReadView时该事务是活跃，不可见；否则就不在创建ReadView时生成的，可见

REPEATABLE READ：在事务启动的时候，就生成一个 ReadView，事务内不变
READ COMMITTED：每次读取数据前都生成一个ReadView

二级索引的MVCC
每个page都有一个 PAGE_MAX_TRX_ID 属性，每当对该页面做操作时，如果大于这个值就更新
如果这个值小于ReadView 中的 min_trx_id，则表示可见
否则回表，去聚集索引中再判断

purage

insert undo 在事务提交后就可以删除了
delete、update的 undo需要支持MVCC还不能删除

一个事务写的一组undo log都有一个 Undo Log Header页面，这个页面中有一个 TRX_UNDO_HISTORY_NODE 属性
这表示一个 history链表节点，当事务提交后，就把这个事务执行过程中产生的 undo 插入到 history链表中
而每个回滚段都有一个 Rollback Segment Header的页面，其中包含了两个属性

TRX_RSEG_HISTORY，表示 history链表的基节点
TRX_RSEG_HISTORY_SIZE，表示history链表的页面数量

后台线程会检查当前事务的编号，然后从 history链表中取出编号较小的，如果其编号小于当前事务id
则表示可以删除了

Undo Truncate

coordinator线程会等待所有的worker完成一批Undo Records的Purge工作，之后尝试清理不再需要的Undo Log
trx_purge_truncate函数中会遍历所有的Rollback Segment中的所有Undo Segment
如果其状态是TRX_UNDO_TO_PURGE，调用trx_purge_free_segment释放占用的磁盘空间并从History List中删除
否则，说明该Undo Segment正在被使用或者还在被cache（TRX_UNDO_CACHED类型），那么只通过trx_purge_remove_log_hd将其从History List中删除。
Undo Truncate的频率由：innodb_rseg_truncate_frequency 参数控制，也就是攒了一批再做

Undo Tablespace Truncate

innodb_trx_purge_truncate配置打开
会尝试重建Undo Tablespaces以缩小文件空间占用
每一时刻最多有一个Tablespace处于inactive，inactive的Undo Tablespace上的所有Rollback Segment都不参与给新事物的分配
等该文件上所有的活跃事务退出，并且所有的Undo Log都完成Purge之后，这个Tablespace就会被通过trx_purge_initiate_truncate重建
包括重建Undo Tablespace中的文件结构和内存结构，之后被重新标记为active，参与分配给新的事务使用

锁

B+树的执行过程

基础部分

锁定读

共享锁
独占锁

加 S 锁的读取

1

SELECT ... LOCK IN SHARE MODE;

加 X 锁的读取

1

SELECT ... FOR UPDATE;

写操作

DELETE，先定位到这条记录，加 X锁，然后执行 delete_mark 操作
UPDATE(新旧记录大小相等)，先定位到这条记录，加 X锁，然后原地更新
UPDATE(新旧记录大小不同)，先定位到这条记录，加X锁然后彻底删掉(移动到垃圾链表中)，再插入一条记录
UPDATE(修改主键值)，也是先删除，再插入
INSERT，插入一条记录受隐式锁包含，不需要在内存中生成对应的锁结构

多粒度锁

表级 S锁，LOCK TABLES t READ
表级 X锁，LOCK TABLES t WRITE
表级意向共享锁 Intention Shared Lock
表级意向独占锁 Intention Exclusive Lock
表级 AUTO-INC 锁

行锁

Record Lock，记录锁只对记录本身加锁
Gap Lock，锁住记录前的间隙，防止别的事物向该间隙插入新纪录。在repeatable read隔离级别下可以很大程度解决幻读，解决方案有两种：一种是MVCC，另一种就是使用这种锁
Next-Key Lock，Record Lock与Gap Lock的结合体，既保护记录本身，也防止别的事务插入记录
Insert Intention Lock，插入时如果有gap锁会在内存中生成一个锁结构，表明有事务想在间歇锁中插入新记录，现处于等待状态
隐式锁，对聚集索引有 trx_id 隐藏列，如果其他事务想对此记录加S/X锁，需查看该记录的 trx_Id是否活跃，如果活跃则帮助此事务建立一个锁结构，然后自身的is_waiting设置为false；二级索引的Page Header有一个 PAGE_MAX_TRX_ID结构，如果小于当前活跃事务说明已提交可以访问，否则回表找到主记录建立锁

行锁结构

InnoDB中锁的结构如下：

解释一下

锁所在的事务信息：任何锁都属于一个事务，这里记载对应的事务信息。
索引信息：对于行级锁来说，需要记录加锁的记录属于哪个索引。
表/行锁信息：记录的对应的信息。
type_mode：32比特的数，包含lock_mode、lock_type、rec_lock_type这三部分
lock_mode（锁模式）占用低4比特
- LOCK_IS（十进制的0）：共享意向锁。
- LOCK_IX（十进制的1）：独占意向锁。
- LOCK_S（十进制的2）：共享锁。
- LOCK_X（十进制的3）：独占锁。
- LOCK_AUTO_INC（十进制的4）：AUTO_INC锁，轻量级锁。
lock_type（锁类型）占用第5-8位
- LOCK_TABLE（十进制的16）：也就是当第五比特设置为1时，表示表级锁
- LOCK_REC（十进制的32）：也就是当第六比特设置为1时，表示行级锁
- 当lock_type为行级锁时，才会由更多信息
- LOCK_ORDINARY，十进制0，表示 next-key
- LOCK_GAP，十进制512，表示gap锁
- LOCK_REC_NOT_GAP，十进制1024，表示记录锁
- LOCK_INSERT_INTENTION，十进制1024，表示插入意向锁
LOCK_WAIT，第9bit为1时表示登台，为0时表示获取到了锁
n_bits，一条激流对应者一个bit，一个page中包含很多记录，用不同bit来区分到底为哪条记录加锁的

rec lock
n_bit 计算公式：

1

n_bits = (1 + ((n_recs + LOCK_PAGE+BITMAP_MARGIN) + 8)) * 8

举个例子说明一下。假设page no为3这个Page中有250行记录，变量n_bits=250+64=314，那么是即位图需要40个字节用于位图的管理（n_bytes=1+314/8=40）。若Page中heap_no为2、3、4、5的记录上都已经上锁，则Page中记录与内存数据结构lock_rec_t的关系如下图所示

由图可见，在页142-3中，前4条用户记录都有锁，因此在对应的数据结构的lock_rec_t中对应的heap no位图值都为1

type_mode计算方式：

1
2


type_mode = LOCK_X | LOCK_REC | LOCK_ORDINARY | LOCK_WAIT
type_mode = 3 |32 | 0 | 256 = 291

gap lock 假设表数据如下：

如图所示（红色三角形所指的位置）得到5个区间：(infimum,2)、(2,4)、(4,6)、(6,8)、(8,supremum)，这些个区间就是间隙，间隙锁就是加在这些间隙上的

设置为可重复读

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


SET tx_isolation='REPEATABLE-READ'
SELECT @@tx_isolation;
# MySQL 8
SELECT @@TRANSACTION_ISOLATION;
SET GLOBAL innodb_status_output_locks = ON;

BEGIN;
SELECT * FROM xx WHERE a IN (3,5,7) FOR UPDATE;
SHOW ENGINE INNODB STATUS;
COMMIT;

重点：
2 lock struct(s), heap size 1192, 3 row lock(s)

locks gap before rec，见名知意就是：锁住的是以下记录（4、6、8）之前的间隙，锁类型是X（排他）锁
InnoDB存储引擎的间隙锁是“纯粹的抑制性”，这意味着它们的唯一目的是防止其他事务插入到间隙中。间隙锁是可以并存的，一个间隙锁被一个事务持有的时候不会阻止另一个事务对这个间隙持有间隙锁，前提是这个间隙是没有数据的（防止往这个间隙写数据，防止别的事务往这个间隙写数据发生幻读）。共享和独占间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

next-key lock
临键锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合，即：Next-Key Lock = Gap Lock + Record Lock，锁定的是一个范围，同时也会锁定记录本身

假设表有记录 10, 11, 13, 20
那么生成的 next-key-lock可能性如下：

1
2
3
4
5


(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

加锁语句分析

几种锁定读语句

SELECT … LOCK IN SHARE MODE
SELECT … FOR UPDATE
UPDATE
DELETE

匹配模式

如果是一个单点扫描区间，此匹配模式为 精确匹配
a = 1、 a = 1 AND b = 1 都是精确匹配
a = 1 AND b >= 1 不是精确匹配

唯一性搜索

如果能事先确定扫描区间最多只包含一条记录，这种情况就是唯一性搜索
匹配模式为精确匹配时
使用主键索引、或者唯一二级索引
不能有 IS NULL这种搜索条件
如果索引中包含多个列，在生成扫描区间时，每个列都得被用到

加锁条件受很多情况影响

事务隔离级别
使用的索引类型，主索引、唯一二级索引、普通索引
是否精确匹配
是否唯一性搜索
具体的执行语句，CURD ?

一般情况下，读取某个扫描区间中的记录过程如下：

快速定位到 B+树中扫描区间中的第一条记录，称为当前记录

为当前记录加锁，当前隔离级别是(读未提交、读已提交)时加：LOCK_REC_NOT_GAP(rec lock`)；隔离级别为(可重复读、串行化)时加： LOCK_ORDINARY(next-key lock)

判断索引下推是否成立，符合跳到(4)，否则读下一条记录并重复(2)；如果读取到头了，则跳过(4)、(5) 像server层返回查询结束

执行回表操作

判断是否查询完毕，是否到达边界了

srver层判断其余条件是否成立

获取下一条记录，并重复(2)

实例1

1

SELECT * FROM xx WHERE number > 1 AND number <= 15 AND contry = 'aa' LOCK IN SHARE MODE;

隔离级别为：读未提交、读已提交时，其加锁示意图

隔离级别为：可重复读、串行化时，其加锁示意图

实例2

1

SELECT * FROM 信息FORCE INDEX(idx_name) WHERE name > 'C' AND name <= 'X' AND country != ’xx5' LOCK IN SHARE MODE;

隔离级别为：读未提交、读已提交时，其加锁示意图

首先读一个二级索引，name为L
给这个二级索引加 S型rec_lock
判断是否可以索引下推，满足条件
回表，给聚集索引加 S型rec_lock
判断是否满足边界条件
server层继续判断其他条件，如country != ‘xx5’ 满足条件，返回给客户端
获取下一条记录，继续判断

隔离级别为：可重复读、串行化时，其加锁示意图

跟二级索引的(读未提交、读已提交)比较，二级索引加的是 next-key lock
二级索引会判断是否下推，然后回表
二级索引加锁后就不再释放
聚集索引加的是 S型rec lock，跟(读未提交、读已提交)差不多，只是最后一条记录没有释放锁

实例3
对于 FOR UPDATE 跟 LOCK IN SHARE MODE 类似，只不过是换乘了 X 型rec lock

实例4

1

UPDATE xx SET name = 'CC' WHERE number > 1 AND number <= 15 AND country = 'ww'

隔离级别为：读未提交、读已提交时，其加锁示意图

先扫描聚集索引，所有更新二级索引都需要加 X型 rec lock
跟查询二级索引正好相反，这次是主索引再找到二级索引

隔离级别为：可重复读、串行化时，其加锁示意图

实例5
对于 DELETE 场景，跟UPDATE类似，都是要加 X型 rec lock

实例6

1

SELECT * FROM xx WHERE name = 'C' FOR UPDATE;

隔离级别为：读未提交、读已提交时，其加锁示意图

由于是精确匹配，不会为下一条记录加锁

隔离级别为：可重复读、串行化时，其加锁示意图

跟上述类似，只是换成了 netx-key lock

实例7
对于不是精确匹配，并且没有找到对应的记录时

1

SELECT *FROM xx WHERE name > 'D" AND name < 'L' FOR UPDATE;

隔离级别为：读未提交、读已提交时，其加锁示意图

隔离级别为：可重复读、串行化时，其加锁示意图

实例8
对于从大到小反向读取时

1
2


SELECT *FROM xx FROCE INDEX(idx_name) WHERE name > 'C' AND name <= 'X' AND country != 'xx5' 
ORDER BY name DESC FOR UPDATE;

隔离级别为：可重复读、串行化时，其加锁示意图

一致性读

利用MVCC进行的读取操作，称为一致性读 Consistent Read，或者无锁一致性读
所有普通SELECT在读已提交、可重复读场景下，都是一致性读
一致性读不会对表中任何记录加锁

半一致性读

夹在一致性读、锁定读之间的读取方式
当隔离级别为读未提交、读已提交时，如果执行UPDATE就使用半一致性读
如果UPDATE语句读取到的记录已经被其他事务加锁了
仍然读出来，然后判断是否与 UPDATE中的条件相匹配，如果不匹配则不加锁，继续查找下一条
只有匹配时才加锁等待

INSERT语句
对于重复记录，也就是主键、唯一二级索引需要检查 id是否重复

当隔离级别为读未提交、读已提交时，加的是 S型rec lock
当隔离级别为可重复读、串行化时，加的是 S型 next-key lock

外键检查，插入子表，检查主键的key是否存在

如果存在则加 S型 rec lock
如果不存在，隔离级别为：读未提交、读已提交时，不加锁
如果不存在，隔离级别为：可重复读、串行化时，加 gap lock

死锁

两个事务模拟死锁

 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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47


------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-01-08 03:30:33 0x7efdb376b700
*** (1) TRANSACTION:
TRANSACTION 113977, ACTIVE 32 sec STARTING INDEX READ
mysql TABLES IN USE 1, locked 1
LOCK WAIT 3 LOCK struct(s), HEAP size 1136, 2 ROW LOCK(s)
MySQL thread id 25, OS thread handle 139628102186752, QUERY id 2169 10.202.61.6 root statistics
SELECT * FROM xx WHERE a = 2 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 46 page NO 3 n bits 72 INDEX PRIMARY of TABLE `test`.`xx` trx id 113977 lock_mode X LOCKS rec but NOT gap waiting
Record LOCK, HEAP NO 2 PHYSICAL RECORD: n_fields 6; COMPACT FORMAT; info bits 0
 0: len 4; HEX 80000002; ASC     ;;
 1: len 6; HEX 00000001bd29; ASC      );;
 2: len 7; HEX a5000001190110; ASC        ;;
 3: len 4; HEX 80000004; ASC     ;;
 4: len 4; HEX 80000006; ASC     ;;
 5: len 4; HEX 80000008; ASC     ;;

*** (2) TRANSACTION:
TRANSACTION 113978, ACTIVE 27 sec STARTING INDEX READ
mysql TABLES IN USE 1, locked 1
3 LOCK struct(s), HEAP size 1136, 2 ROW LOCK(s)
MySQL thread id 22, OS thread handle 139628102727424, QUERY id 2175 10.202.61.6 root statistics
SELECT * FROM xx WHERE a = 4 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 46 page NO 3 n bits 72 INDEX PRIMARY of TABLE `test`.`xx` trx id 113978 lock_mode X LOCKS rec but NOT gap
Record LOCK, HEAP NO 2 PHYSICAL RECORD: n_fields 6; COMPACT FORMAT; info bits 0
 0: len 4; HEX 80000002; ASC     ;;
 1: len 6; HEX 00000001bd29; ASC      );;
 2: len 7; HEX a5000001190110; ASC        ;;
 3: len 4; HEX 80000004; ASC     ;;
 4: len 4; HEX 80000006; ASC     ;;
 5: len 4; HEX 80000008; ASC     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 46 page NO 3 n bits 72 INDEX PRIMARY of TABLE `test`.`xx` trx id 113978 lock_mode X LOCKS rec but NOT gap waiting
Record LOCK, HEAP NO 3 PHYSICAL RECORD: n_fields 6; COMPACT FORMAT; info bits 0
 0: len 4; HEX 80000004; ASC     ;;
 1: len 6; HEX 00000001bd2a; ASC      *;;
 2: len 7; HEX a60000011a0110; ASC        ;;
 3: len 4; HEX 80000006; ASC     ;;
 4: len 4; HEX 80000008; ASC     ;;
 5: len 4; HEX 8000000a; ASC     ;;

*** WE ROLL BACK TRANSACTION (2)

InnoDB 只会显示最近一次发生的死锁，如果想将所有死锁信息都打印出来，可以设置：

1

innodb_print_all_deadlocks

设置为 ON，就会将每次发生的死锁，记录到 log中了

锁模式和类型

位置在： /mysql8-src/storage/innobase/include/lock0lock.h

 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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53


/** Lock modes and types */
/** @{ */
#define LOCK_MODE_MASK                          \
  0xFUL /*!< mask used to extract mode from the \
        type_mode field in a lock */
/** Lock types */
#define LOCK_TABLE 16 /*!< table lock */
#define LOCK_REC 32   /*!< record lock */
#define LOCK_TYPE_MASK                                \
  0xF0UL /*!< mask used to extract lock type from the \
         type_mode field in a lock */
#if LOCK_MODE_MASK & LOCK_TYPE_MASK
#error "LOCK_MODE_MASK & LOCK_TYPE_MASK"
#endif

#define LOCK_WAIT                          \
  256 /*!< Waiting lock flag; when set, it \
      means that the lock has not yet been \
      granted, it is just waiting for its  \
      turn in the wait queue */
/* Precise modes */
#define LOCK_ORDINARY                     \
  0 /*!< this flag denotes an ordinary    \
    next-key lock in contrast to LOCK_GAP \
    or LOCK_REC_NOT_GAP */
#define LOCK_GAP                                     \
  512 /*!< when this bit is set, it means that the   \
      lock holds only on the gap before the record;  \
      for instance, an x-lock on the gap does not    \
      give permission to modify the record on which  \
      the bit is set; locks of this type are created \
      when records are removed from the index chain  \
      of records */
#define LOCK_REC_NOT_GAP                            \
  1024 /*!< this bit means that the lock is only on \
       the index record and does NOT block inserts  \
       to the gap before the index record; this is  \
       used in the case when we retrieve a record   \
       with a unique key, and is also used in       \
       locking plain SELECTs (not part of UPDATE    \
       or DELETE) when the user has set the READ    \
       COMMITTED isolation level */
#define LOCK_INSERT_INTENTION                                             \
  2048                       /*!< this bit is set when we place a waiting \
                          gap type record lock request in order to let    \
                          an insert of an index record to wait until      \
                          there are no conflicting locks by other         \
                          transactions on the gap; note that this flag    \
                          remains set when the waiting lock is granted,   \
                          or if the lock is inherited to a neighboring    \
                          record */
#define LOCK_PREDICATE 8192  /*!< Predicate lock */
#define LOCK_PRDT_PAGE 16384 /*!< Page lock */

MVCC

基本概念

ReadView

purage

锁

基础部分

行锁结构

加锁语句分析

死锁

锁模式和类型

参考

相关文章

最近文章

分类

归档

标签

RSS