mysql并发相关知识梳理

最新推荐文章于 2021-02-26 16:43:05 发布

tszxlzc

最新推荐文章于 2021-02-26 16:43:05 发布

阅读量353

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/tszxlzc/article/details/88547253

版权

mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

mysql并发相关知识梳理

1.事务隔离级别

事务隔离级别		脏读	不可重复读	幻读
REPEATABLE READ	InnoDB 默认隔离级别。同一事务内的一致读操作读取由第一次读操作建立的快照。对应普通查询，这些SELECT语句彼此之间也是一致的。对于锁定读取(选择with For UPDATE或LOCK IN SHARE模式)、更新和删除语句，锁定取决于语句是使用具有惟一搜索条件的惟一索引，还是使用范围类型的搜索条件。	N	N	在加锁读时可以避免幻读
READ COMMITTED	即使在同一个事务中，每次一致的读，读取最新的快照	N	Y	Y
READ UNCOMMITTED	SELECT语句以非锁定方式执行	Y	Y	Y
SERIALIZABLE	这个级别类似于可重复读，但如果禁用了 `autocommit` ，innodb隐式地将select语句转为 `SELECT ... LOCK IN SHARE MODE`	N	N	N

脏读：最容易理解，另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据。

**不可重复读 ** ：解决了脏读后，会遇到，同一个事务执行过程中，另外一个事务提交了新数据，因此本事务先后两次读到的数据结果会不一致。

幻读：解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

RR隔离级别时为什么会有幻读？因为普通select是快照读，而insert、update、加锁读读是当前读。加锁后为什么又能避免幻读，因为有gap锁（当然也有记录锁），gap锁将可能插入满足条件的记录gap给锁上，防止满足条件的记录插入，也就避免了幻读。

准备表数据：

CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
INSERT INTO child (id) values (90),(102);
mysql> select @@global.tx_isolation, @@tx_isolation; 
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set

实验1

session A                                                    session B
mysql> start transaction;
Query OK, 0 rows affected


mysql> select * from child;
+-----+
| id  |
+-----+
|  90 |
| 102 |
+-----+
2 rows in set 

										   mysql> start transaction;
										   Query OK, 0 rows affected
                                               mysql> insert into child (id) values (103);
                                               Query OK, 1 row affected  
mysql> select * from child;
+-----+
| id  |
+-----+
|  90 |
| 102 |
+-----+
2 rows in set       
										   mysql> commit;
                                               Query OK, 0 rows affected
mysql> select * from child;
+-----+
| id  |
+-----+
|  90 |
| 102 |
+-----+
2 rows in set
至此验证了：普通查询， SELECT语句彼此之间也是一致的

mysql> insert into child (id) values (103);
1062 - Duplicate entry '103' for key 'PRIMARY'
mysql> select * from child lock in share mode;
 
+-----+
| id  |
+-----+
|  90 |
| 102 |
| 103 |
+-----+
3 rows in set
insert 和加锁读验证了幻读问题

实验2

session A                                                     session B
mysql> START TRANSACTION;  
Query OK, 0 rows affected
mysql> select * from child where id <= 90 for update;
+----+
| id |
+----+
| 90 |
+----+
1 row in set
                                                     mysql> start transaction;
                                                     Query OK, 0 rows affected

                                                     mysql> insert into child (id) values (104);
                                                     Query OK, 1 row affected

                                                     mysql> insert into child (id) values (89);
                                                     1205 - Lock wait timeout exceeded; try 												 restarting transaction
由于范围查询， session A中的事务获取了锁； session B中的事务要在 session A 范围查询内进行插入操作，同样要获取锁，锁等待超时。
                                                     mysql> commit;
                                                     Query OK, 0 rows affected		
mysql> select * from child for update;
+-----+
| id  |
+-----+
|  90 |
| 102 |
| 103 |
| 104 |
+-----+
4 rows in set

mysql> select * from child;
+-----+
| id  |
+-----+
|  90 |
| 102 |
| 103 |
+-----+
3 rows in set
对比select * from child for update 和select * from child两个查询， 说明加锁读是当前读

实验3

session A                                                     session B
mysql> start transaction;
Query OK, 0 rows affected
                                                    mysql> start transaction;
                                                    Query OK, 0 rows affected

                                                    mysql> insert into child (id) values (105);
                                                    Query OK, 1 row affected

                                                    mysql> commit;
                                                    Query OK, 0 rows affected
mysql> select * from child;
+-----+
| id  |
+-----+
|  90 |
| 102 |
| 103 |
| 104 |
| 105 |
+-----+
5 rows in set
说明由第一次select操作起开始建立快照，并不是从start transaction;开始

2.MVCC

InnoDB Multi-Versioning文档链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

Internally, InnoDB adds three fields to each row stored in the database. A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byte DB_ROLL_PTRfield called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.

innoDB为每一行记录增加了三个隐藏字段DB_TRX_ID 、DB_ROLL_PTR、 DB_ROW_ID

增加的字段	字段描述
DB_TRX_ID	6字节字段（事务标识），插入或更新行的最近的事务标识
DB_ROLL_PTR	7字节字段（回滚指针），回滚指针指向写入rollback segment的undo log ，undo log 包含重构（更新操作之前的）记录的必要信息
DB_ROW_ID	6字节字段（行id），当新行插入单调递增。如果InnoDB自动生成聚簇索引，则索引包含行ID值。否则，DB_ROW_ID列不会出现在任何索引中
deletion	在mysql内部，删除操作也被看做一个更新，专门有个二进制标示该行是否被删除

ReadView数据结构 （拥有的字段）


low_limit_id	“给下一个事务分配的事务ID” ，大于等于这个事务ID的，在ReadView创建的时候还没有开始，这些事务的数据当然不可见。low_limit_id即存储这个值事务号 >= low_limit_id的记录，对于当前Read View都是不可见的
up_limit_id	当前正在执行的事务”中，找到一个事务ID最小的。 up_limit_id即存储这个值事务号 < up_limit_id ，对于当前Read View都是可见的
trx_ids	事务ID介于low_limit_id、up_limit_id这两个值之间的事务，可能有的还未提交，有的已经提交。未提交的事务，数据当然不可见。将未提交的事务的ID，存入到trx_ids数组中
creator_trx_id	将本事务的ID存到creator_trx_id中

记录是否可见流程

read view 可见性示例

事务97542先执行

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected
mysql> INSERT INTO child (id) values (90),(101),(102);

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
mysql> start transaction;
Query OK, 0 rows affected
mysql> INSERT INTO child (id) VALUES (91);
Query OK, 1 row affected
mysql> select * from child;
+-----+
| id  |
+-----+
|  90 |
|  91 |
| 101 |
| 102 |
+-----+
4 rows in set

事务97543后执行

mysql> start transaction;
Query OK, 0 rows affected
mysql> INSERT INTO child (id) VALUES (92);
Query OK, 1 row affected

mysql> select * from child;
+-----+
| id  |
+-----+
|  90 |
|  92 |
| 101 |
| 102 |
+-----+
4 rows in set

事务状态

---TRANSACTION 0 97543, ACTIVE 137 sec, OS thread id 46072
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 1
MySQL thread id 4, query id 37 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 97544, sees < 0 97542
---TRANSACTION 0 97542, ACTIVE 143 sec, OS thread id 45380
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 1
MySQL thread id 3, query id 38 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 97544, sees < 0 97543

rollback segment

包含undo日志的存储区域。回滚段通常位于系统表空间中。在MySQL 5.6中，回滚段可以驻留在undo表空间中。从MySQL 5.7开始，回滚段也被分配到临时表空间。

Undo logs

Undo logs in the rollback segment are divided into insert and update undo logs. Insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits. Update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which InnoDB has assigned a snapshot that in a consistent read could need the information in the update undo log to build an earlier version of a database row.

rollback segment的undo log分为插入和更新undo log。只有在事务回滚时才需要插入undo log，事务提交后可以立即丢弃。更新undo log也用于读取一致性,但他们只有在没有（InnoDB已经分配了快照的）事务后才能被丢弃，快照在一个一致性读中需要 update 类型的undo log去建立一个早期版本的数据库行。

purge

A type of garbage collection performed by one or more separate background threads (controlled by innodb_purge_threads) that runs on a periodic schedule. Purge parses and processes undo log pages from the history list for the purpose of removing clustered and secondary index records that were marked for deletion (by previous DELETE statements) and are no longer required for MVCC or rollback. Purge frees undo log pages from the history list after processing them.

由一个或多个独立后台线程(由innodb_purge_threads控制)执行的一种垃圾收集类型，该线程按周期调度运行。清除从历史记录列表中解析和处理撤消日志页，已删除标记为删除(通过以前的DELETE语句)、不再需要MVCC或回滚的聚簇和二级索引记录。purge在处理undo log后从历史列表中释放undo log 页。

log类型	log理解
binlog	选择binlog日志作为replication主要原因是MySQL的特点就是支持多存储引擎，为了兼容绝大部分引擎来支持复制这个特性，那么自然要采用MySQL Server自己记录的日志而不是仅仅针对InnoDB的redo log；记录的是所有引擎的更新操作的日志记录；binlog记录的是事务的具体操作内容
undo log	InnoDB 层，undo log记录对于数据库的反向操作，或者说记录的是数据库里面的原数据；undo log主要功能是回滚，是为了保持事务的原子性，记录的是未提交事务的操作，也就是对原数据做了备份，重启实例以后可以恢复到之前的状态。
redo log	InnoDB 层， redo log 录记对于数据库的操作，或者说记录的是数据库里面的新数据（也就是被修改过的数据）。 1. redo log主要功能是重做，是为了保持事务的持久性，记录的是已提交事务的操作，也就是对新数据做了备份，如果数据库发生宕机，可以在重启实例以后完成需要的操作； 2. 数据库事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID特性。磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，这样能极大提高性能。

3.锁

3.1锁类型

InnoDB支持多粒度锁，允许行锁和表锁共存。

InnoDB实现了标准的行级锁，其中有两种锁，共享锁和独占锁。

意图锁是表级锁，指示事务稍后对表中的行需要哪种类型的锁(共享或独占)。

锁类型	描述
共享锁（ shared (S) ）	允许持有锁的事务读取一行
排它锁（ exclusive (X) ）	允许持有锁的事务更新或删除一行
意向共享锁（IS）	表示事务打算在表中的个别行上设置共享锁
意向排他锁（IX）	表示事务打算对表中的个别行上设置独占锁。

意图锁不会阻止除全表请求之外的任何东西(例如, LOCK TABLES … WRITE) 。意图锁定的主要目的是显示某人正在锁定一行，或打算锁定表中的一行

For example, SELECT ... LOCK IN SHARE MODE sets an IS lock, and SELECT ... FOR UPDATE sets an IX lock.

表级锁兼容性矩阵：

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

行锁	描述
gap lock	gap锁是对索引记录之间的间隙的锁，或对第一个索引前的间隙或最后一个索引之后的间隙的加的锁。RC隔离级别没有，RR隔离级别有。（左开右开）范围查询或等值查询且记录不存在的时候next-key lock退化成gap lock
Record Locks	记录锁是索引记录上的锁。记录锁总是锁定索引记录，即使一个表没有定义索引。对于这种情况，InnoDB创建一个隐藏的聚簇索引并使用这个索引进行记录锁定唯一性（主键或唯一）索引，条件匹配next-key lock退化成 record lock
next-key lock	next-key lock是索引记录上的记录锁和索引记录之前的间隙锁的组合，锁住记录+区间（左开右闭）
Insert Intention Locks	插入意向锁是gap lock的一种，如果插入的位置不冲突，不会阻塞彼此。

行锁的兼容矩阵

	Gap	Insert Intention	Record	Next-Key
Gap	兼容	兼容	兼容	兼容
Insert Intention	`冲突`	兼容	兼容	`冲突`
Record	兼容	兼容	`冲突`	`冲突`
Next-Key	兼容	兼容	`冲突`	`冲突`

**表注：**横向是已经持有的锁，纵向是正在请求的锁。

由于S锁和S锁是完全兼容的，因此在判别兼容性时只考虑持有的锁与请求的锁是这三种组合情形：X、S和S、X和X、X。另外，需要提醒注意的是进行兼容判断也只是针对于加锁涉及的行有交集的情形。

分析兼容矩阵可以得出如下几个结论：

INSERT操作之间不会有冲突。
GAP,Next-Key会阻止Insert。
GAP和Record,Next-Key不会冲突
Record和Record、Next-Key之间相互冲突。
已有的Insert锁不阻止任何准备加的锁。

插入意向锁示例

事务97554先执行

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

事务97555后执行

mysql>  START TRANSACTION;
Query OK, 0 rows affected

mysql> INSERT INTO child (id) VALUES (101);

事务状态

---TRANSACTION 0 97555, ACTIVE 10 sec, OS thread id 46072 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368, 1 row lock(s)
MySQL thread id 4, query id 66 localhost 127.0.0.1 root update
INSERT INTO child (id) VALUES (101)
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4624 n bits 72 index `PRIMARY` of table `test`.`child` trx id 0 97555 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;; 
 1: len 6; hex 000000017d11; asc     } ;; 
 2: len 7; hex 8000001212011c; asc        ;;

------------------
---TRANSACTION 0 97554, ACTIVE 30 sec, OS thread id 45380
2 lock struct(s), heap size 368, 2 row lock(s)
MySQL thread id 3, query id 64 localhost 127.0.0.1 root

3.2加锁分析

id为主键，RR隔离级别

delete from t1 where id = 10;
对于使用唯一索引能够定位到唯一行的sql语句，不需要gap锁（组合索引，查询条件只包含索引的几列的情况还是要gap锁的）。

idä¸»é”®+rc

id唯一索引+RR

delete from t1 where id = 10;

若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。

此组合中，id是unique索引，而主键是name列。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

id unique+rc

id非唯一索引，RR隔离级别

delete from t1 where id = 10;

这里值得注意的是，不同的事务可以在间隙上持有冲突锁。例如，事务A可以在间隙上持有共享间隙锁(gap S-lock)，而事务B可以在相同的间隙上持有独占间隙锁(gap X-lock)。gap锁的唯一目的是为了防止其他事务在间隙中插入记录，一个持有gap锁的事务不会阻止其他事务在同一个间隙获取gap锁。不管是gap S-lock还是gap X-lock。两个事务的gap锁不冲突，都是为了防止在gap间有记录插入。

id éžå”¯ä¸€ç´¢å¼• + rr

无索引，RR隔离级别：在没有索引的情况下，InnoDB只能使用表锁（对全部行、间隙加锁）

delete from t1 where id = 10;

id æ— ç´¢å¼•+rr

id无索引，RC隔离级别和RR级别对比

delete from t1 where id = 10;

使用RC隔离级别或启用innodb_locks_unsafe_for_binlog还有其他效果。MySQL评估了WHERE条件后，将释放不匹配行的记录锁。对于UPDATE语句，InnoDB会执行“半一致”读取，这样它就会将最新提交的版本返回给MySQL，这样MySQL就可以确定行是否匹配更新的位置条件。 select …for update 的时候可以将隔离级别设为RC，就是为了不加gap锁。

id æ— ç´¢å¼•+rc

4.快照读、当前读

	快照读	当前读
常见sql	select	select … lock in share mode select … for update insert update delete
实现方式	快照读是通过MVVC(多版本控制)和undo log来实现的	当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的

为什么将插入/更新/删除操作，都归为当前读？可以看看下面这个更新操作，在数据库中的执行流程：

update æ‰§è¡Œæµç¨‹

从图中，可以看到，一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

注：根据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

参考博客：
https://segmentfault.com/a/1190000012650596

http://hedengcheng.com/?p=148#_Toc322691905

http://hedengcheng.com/?p=771

https://www.2cto.com/database/201807/757941.html