24khandsome's Blog

24khandsome's Blog

性能调优04-Mysql锁与事务隔离级别与MVCC

发表于 更新于 分类于
Mysql索引优化实战

Mysql锁与事务隔离级别

1、锁定义

​ 锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外， 数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性 是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个 重要因素。

2、锁分类

1、从性能上分为悲观锁 和乐观锁(用版本对比来实现)

2、从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

​ 1、读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

​ 2、写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

3、从对数据操作的粒度分，分为表锁和行锁

2.1 表锁

​ 每次操作锁住整张表。

​ 开销小，加锁快；不会出现死锁；

​ 锁定粒度大，发生锁冲突的概率最高，并发度最低

2.1.1 基本操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE `mylock` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT, 
	`NAME` VARCHAR ( 20 ) DEFAULT NULL, 
	PRIMARY KEY ( `id` ) 
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
-- 插入数据 
INSERT INTO `mylock` ( `id`, `NAME` )
VALUES
	( '1', 'a' )，
	( '2', 'b' )，
	( '3', 'c' )，
	( '4', 'd' );
	
	
session 1	
-- 手动增加表锁
lock table mylock read
-- 查看表上加过的锁
show open tables;
select * from mylock
-- 删除表锁
unlock tables

存在锁
image-20211220212824765

2.1.2 案例分析(加读锁）

​ 当前session和其他session都可以读该表

​ 当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待

2.1.3 案例分析(加写锁）

​ 当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

2.1.4 案例结论

​ MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。

​ 1、对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

​ 2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

总结： 读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

2.2 行锁

​ 每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB与MYISAM的最大不同有两点：

​ 支持事务（TRANSACTION）

​ 支持行级锁

2.2.1行锁支持事务

事务（Transaction）及其ACID属性

​ 1、原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执 行,要么全都不执行。

​ 2、一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意 味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束 时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。

​ 3、隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并 发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是 不可见的,反之亦然。

​ 4、持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系 统故障也能够保持

并发事务处理带来的问题

​ 1、**更新丢失（Lost Update）(脏写)**当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每 个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。

​ 就是两个事务都更新一个数据，结果有一个人回滚了把另外一个人更新的数据也回滚没了

​ 解决办法：乐观锁加版本号或者悲观锁加行锁

​ 2、脏读（Dirty Reads）一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数 据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控 制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提 交的数据依赖关系。这种现象被形象的叫做“脏读”。 一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基 础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

​ 3、不可重读（Non-Repeatable Reads）一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现 其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不 可重复读”。 一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性

​ 4、幻读（Phantom Reads） 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插 入了满足其查询条件的新数据，这种现象就称为“幻读”。 一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

事务隔离级别

​ 脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数 据库提供一定的事务隔离机制来解决。

image-20211220223800568

​ 数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔 离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。

​ 同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用 对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。

​ 常看当前数据库的事务隔离级别: show variables like ‘tx_isolation’;

​ 设置事务隔离级别：set tx_isolation=’REPEATABLE-READ’;

​ set [ global | session ] transaction isolation level Read uncommitted | Read committed | Repeatable Read | Serializable;

Mysql5.8：

​ show variables like ‘%transaction_isolation%’

​ set session transaction isolation level read committed ;

​ set globaltransaction isolation level read committed ;

2.2.2 行锁与隔离级别案例分析

1、行锁演示

​ 一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

2、读未提交

（1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交 读），查询表account的初始值： set tx_isolation=’read-uncommitted’;

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那 客户端A查询到的数据其实就是脏数据：

（5）在客户端A执行更新语句update account set balance = balance - 50 where id =1，lilei的balance没有变成350，居然是400，是不是很奇怪，数据不 一致啊，如果你这么想就太天真 了，在应用程序中，并 不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别

3、读已提交

（1）打开一个客户端A，并设置当前事务模式为read committed（读已提交 ），查询表account的所有记录： set tx_isolation=’read-committed’;

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题：

（4）客户端B的事务提交

（5）客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题

4、可重复读

（1）打开一个客户端A，并设置当前事务模式为repeatable read，查询表 account的所有记录

​ set tx_isolation=’repeatable-read’;

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account并提 交

（3）在客户端A查询表account的所有记录，与步骤（1）查询结果一致，没 有出现不可重复读的问题

（4）在客户端A，接着执行update account set balance = balance - 50 where id = 1，balance没有变成400-50=350，balance值用的是步骤 （2）中的350来算的，所以是300，数据的一致性倒是没有被破坏。可重复读的 隔离级别下使用了MVCC(multi-version concurrency control)机制，

​ select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本 号，是当前读（当前版本）。

5、串行化

（1）打开一个客户端A，并设置当前事务模式为serializable，查询表account 的初始值： 

1
2
3
4
5
6
7
8
set session transaction isolation level serializable; 
start transaction; 
select * from account; 
| id | name | balance | 
| 1 | lilei | 10000 | 
| 2 | hanmei | 10000 | 
| 3 | lucy | 10000 | 
| 4 | lily | 10000 |

（2）打开一个客户端B，并设置当前事务模式为serializable，插入一条记录 报错，表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

1
2
3
4
5
set session transaction isolation level serializable;
start transaction;
insert into account values(5,'tom',0); 
Lock wait timeout exceeded; 
try restarting transaction
Mysql默认级别是repeatable-read，有办法解决幻读问题吗？

​ 间隙锁在某些情况下可以解决幻读问题

​ 要避免幻读可以用间隙锁在Session_1下面执行update account set name = ‘zhuge’ where id > 10 and id <=20;，则其他Session没法在这个范围所包含的 间隙里插入或修改任何数据

无索引行锁会升级为表锁

​ 锁主要是加在索引上，如果对非索引字段更新, 行锁可能会变表锁 ，**==且InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。==**

其他加锁方式

​ SELECT … LOCK IN SHARE MODE走的是IS锁(意向共享锁)

​ 一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，那么从业务角度讲，此时我直接insert一条child_id=100记录到child表是存在风险的，因为刚insert的时候可能在parent表里删除了这条c_child_id=100的记录，那么业务数据就存在不一致的风险。正确的方法是再插入时执行select * from parent where c_child_id=100 lock in share mode,锁定了parent表的这条记录，然后执行insert into child(child_id) values (100)就ok了。

​ SELECT … FOR UPDATE 走的是IX锁(意向排它锁)

2.2.3 案例结论

​ Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优 于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和 MYISAM相比就会有比较明显的优势了。

​ 相对来说，Innodb的风险更高，容易锁死锁

2.2.4 行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like’innodb_row_lock%’;

对各个状态量的说明如下： 

1
2
3
4
5
6
7
8
9
Innodb_row_lock_current_waits: 当前正在等待锁定的数量 
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度 
Innodb_row_lock_time_avg: 每次等待所花平均时间 
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间 
Innodb_row_lock_waits:系统启动后到现在总共等待的次数 
对于这5个状态变量，比较重要的主要是： 
Innodb_row_lock_time_avg （等待平均时长） 
Innodb_row_lock_waits （等待总次数） 
Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统 中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

2.2.5 死锁

set tx_isolation=’repeatable-read‘;

Session_1执行：select * from account where id=1 for update;

Session_2执行：select * from account where id=2 for update;

Session_1执行：select * from account where id=2 for update;

Session_2执行：select * from account where id=1 for update;

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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
-- 查看近期死锁日志信息：show engine innodb status
=====================================
2021-12-21 23:27:24 0x750c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 698 srv_active, 0 srv_shutdown, 695589 srv_idle
srv_master_thread log flush and writes: 696287
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 585
OS WAIT ARRAY INFO: signal count 545
RW-shared spins 548, rounds 960, OS waits 417
RW-excl spins 25, rounds 595, OS waits 27
RW-sx spins 3, rounds 61, OS waits 2
Spin rounds per wait: 1.75 RW-shared, 23.80 RW-excl, 20.33 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-12-21 23:27:01 0xe360
*** (1) TRANSACTION:
TRANSACTION 399250, ACTIVE 30 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 33, OS thread handle 23912, query id 802533 localhost ::1 root statistics
select * from account where id= 2 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2822 page no 5 n bits 80 index PRIMARY of table `study_imysql_ndex`.`account` trx id 399250 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000061775; asc      u;;
 2: len 7; hex a90000011e0110; asc        ;;
 3: len 4; hex 6e616d65; asc name;;
 4: len 4; hex 80003e80; asc   > ;;

*** (2) TRANSACTION:
TRANSACTION 399251, ACTIVE 15 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 34, OS thread handle 58208, query id 802537 localhost ::1 root statistics
select * from account where id= 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2822 page no 5 n bits 80 index PRIMARY of table `study_imysql_ndex`.`account` trx id 399251 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000061775; asc      u;;
 2: len 7; hex a90000011e0110; asc        ;;
 3: len 4; hex 6e616d65; asc name;;
 4: len 4; hex 80003e80; asc   > ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2822 page no 5 n bits 80 index PRIMARY of table `study_imysql_ndex`.`account` trx id 399251 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000061771; asc      q;;
 2: len 7; hex a7000001df0110; asc        ;;
 3: len 5; hex 6c696c6569; asc lilei;;
 4: len 4; hex 800001c2; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 399252
Purge done for trx's n:o < 399236 undo n:o < 0 state: running but idle
History list length 540
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283720369143296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283720369145040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283720369141552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 399250, ACTIVE 53 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 33, OS thread handle 23912, query id 802544 localhost ::1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
11388 OS file reads, 275231 OS file writes, 268926 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.15 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 113, seg size 115, 51 merges
merged operations:
 insert 76, delete mark 78, delete 42
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 3 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
0.36 hash searches/s, 0.38 non-hash searches/s
---
LOG
---
Log sequence number 3913168090
Log flushed up to   3913168090
Pages flushed up to 3913168090
Last checkpoint at  3913168081
0 pending log flushes, 0 pending chkp writes
267248 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8585216
Dictionary memory allocated 3494975
Buffer pool size   512
Free buffers       246
Database pages     256
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 10815, created 2268, written 6967
0.00 reads/s, 0.00 creates/s, 0.15 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 256, unzip_LRU len: 0
I/O sum[9]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=5632, Main thread ID=0000000000000B1C , state=sleeping
Number of rows inserted 279451, updated 682, deleted 187, read 1383947
0.15 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.97 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

2.2.6 优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小锁的范围

尽可能减少检索条件范围，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql

尽量放在事务最后执行

尽可能低级别事务隔离

MVCC机制详解

多个事务并发运行的时候，同时读写一个数据，可能会出现脏写、脏读、不可重复读、幻读几个问题

​ 脏写，就是两个事务都更新一个数据，结果有一个人回滚了把另外一个人更新的数据也回滚没了；
​ 脏读，就是一个事务读到了另外一个还没提交的时候修改的数据，结果另外一个事务回滚了，下次读就读不到了；
​ 不可重复读，就是多次读同一条数据，别的事务修改数据值还提交了，多次读到的值不同；
​ 幻读，就是范围查询，每次查询的的数据不同，有时候别的事务插入了新的值，就会读到的值不同

针对这些问题,才有了 RU（读未提交）、RC（读已提交）、RR（可重复读） 和串行四个隔离级别

​ RU 隔离级别，就是可以读到别人还没提交的事务修改过的数据；
​ RC 隔离级别，可以读到人家提交的事务修改过的数据，可以避免脏读问题；
​ RR 是不会读到别的事务已经提交事务修改的数据，可以避免脏读和不可重复读的问题；
​ 串行是让事务都串行执行，可以避免所有问题，包括脏读、不可重复读、幻读

MySQL 实现 MVCC 机制的时候，是基于 undo log 多版本链条 + ReadView 机制来做的，默认的 RR 隔离级别，就是基于这套机制实现了 RR 级别，除了避免脏写、脏读、不可重复读，还能避免幻读问题。因此我们一般来说我们都用默认的 RR 隔离级别就可以了

undo log版本链

​ 总起起来，就是多个事务串行执行的时候，每个人修改了一行数据，都会更新隐藏字段 trx_id 和 roll_pointer，同时之前多个数据快照对应的 undo log，会通过roll_pointer 指针串联起来，形成一个重要的版本链

我们每条数据其实都有两个子段，一个是 trx_id，一个是 roll_pointer。

trx_id 就是最近一次更新这条数据的事务id

roll_pointer 就是指向你更新这个事务之前生成的 undo log

简单来说：mysql中每行记录根据先后事务更新提交顺序用roll_pointer串起来的链条

image-20211222230309957

ReadView

​ ReadView简单来说就是你执行一个事务的时候就给你生成一个 ReadView，里面比较关键的东西有 4个

m_ids，这个就是说此时有哪些事务在 MySQL 里执行还没提交的
min_trx_id，就是 m_ids 里面最小的值
max_trx_id，就是说 MySQL 下一个要生成的事务 id，就是最大事务 id
creator_trx_id，就是你这个事务的 id

image-20211222230650385

总结

1、Review 其实就是查询时根据事务id，去查询数据中事务id <= 当前事务id的记录，如果发现记录事务id大于当前事务id，则沿着undo log链找到 <= 当前事务id的记录。

2、在读已提交的事务隔离级别下，每次执行select拍快照ReadView，从而达到每次读都是读到别的事务已提交的内容

3、在可重复读的事务隔离级别下，事务后首次查询时拍快照ReadView，从而达到可重复度的效果