Mysql数据一致性与处理并发访问处理:隔离级别,锁策略与MVCC-每日运维

基础概念

脏读	一个事务中访问到了另外一个事务未提交的数据
不可重复读	一个事务内根据同一个条件对行记录进行多次查询，返回的结果不一致
幻读	同一个事务内多次查询返回的结果集不一样（增加了或者减少）

隔离级别

隔离级别	描述	锁
读未提交(read uncommit)	一个事务还没提交时，它做的变更就能被别的事务看到	任何操作都不会加锁
读提交(read commit)	一个事务提交之后，它做的变更才会被其他事务看到	在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的
可重复读(repeatable read)	一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的	读操作不需要加锁，而写操作需要加锁。
串行化读(serializable):	当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。	“写”会加“写锁”，“读”会加“读锁”

隔离级别	脏读	不可重复读	幻读
读未提交(read uncommit)	可能	可能	可能
读提交(read commit)	不可能	可能	可能
可重复读(repeatable read)	不可能	不可能(MVCC实现)	不可能（用next-key lock 保证）
串行化读(serializable)	不可能	不可能	不可能

mysql隔离级别有两个作用域，一个是当前会话隔离级别，另一个是系统隔离级别。
读取数据时，数据库会创建视图，访问的时候以视图的逻辑结果为准

隔离级别	视图
读未提交(read uncommit)	直接返回记录上的最新值，没有视图概念
读提交(read commit)	视图是在每个 SQL 语句开始执行的时候创建的
可重复读(repeatable read)	视图是在事务启动时创建的，整个事务存在期间都用这个视图
串行化读(serializable)	直接用加锁的方式来避免并行访问。

快照读（snapshot read）和当前读（current read）

MySQL中的当前读和快照读是指在读取数据时的不同方式。当前读（Current Read）是指读取最新的数据，而快照读（Snapshot Read）则是指读取某个特定时刻的数据快照。这两种读操作在不同的隔离级别下有不同的行为。

	当前读（Current Read）	快照读（Snapshot Read）
读未提交(read uncommit)	读取到其他事务未提交的数据（脏读）	读取到其他事务未提交的数据（脏读）
读提交(read commit)	当前读操作会等待其他事务的锁释放，然后读取已提交的最新版本的数据。	快照读只会读取已经提交的数据。这意味着事务不会看到其他事务正在修改的数据。然而，同一事务内的多次读操作可能会看到不同的数据版本，因为其他事务可能在此期间提交了修改。
可重复读(repeatable read)	当前读仍然会等待其他事务的锁释放，然后读取已提交的最新版本的数据。在当前读（current read）的情况下，MySQL通过next-key lock来避免幻读	在可重复读隔离级别下，事务在开始时会创建一个快照，所有快照读操作都会基于这个快照读取数据。在快照读（snapshot read）的情况下，MySQL通过MVCC（多版本并发控制）来避免幻读
串行化读(serializable)	在串行化隔离级别下，所有事务都会串行执行，因此当前读会读取到已提交的最新版本的数据。	同当前读

InnoDB 锁机制

MySQL中的锁有很多种,按照资源访问限制的不同程度,分为:

共享锁（Shared Locks）：也称为读锁（Read Locks），允许多个事务同时读取相同的数据，但在共享锁生效期间，其他事务不能对该数据进行修改。共享锁的目的是保证在一个事务读取数据时，其他事务不能修改这些数据。

排他锁（Exclusive Locks）：也称为写锁（Write Locks），当一个事务需要对数据进行修改时，会请求排他锁。在排他锁生效期间，其他事务既不能对该数据进行修改，也不能读取这些数据。排他锁的目的是保证在一个事务修改数据时，其他事务不能读取或修改这些数据。

从锁粒度的角度看,MySQL中有这么几种锁：

表锁（Table Locks）：表锁是MySQL中最基本的锁策略，适用于MyISAM、MEMORY等存储引擎。当一个事务需要对表进行操作时，会锁定整个表，防止其他事务在锁定期间对表进行操作。表锁的粒度较大，导致锁冲突的概率较高，因此在高并发场景下，表锁的性能较低。

行锁（Row Locks）：行锁是MySQL中更高级的锁策略，适用于InnoDB等存储引擎。它允许对表中的单个行进行锁定，减少锁冲突的概率，提高并发性能。行锁的粒度较小，但实现复杂，可能导致死锁（Deadlocks）等问题。

除了上述基本锁类型外，MySQL还支持其他锁策略，如间隙锁（Gap Locks）、意向锁（Intention Locks）等，以解决不同场景下的并发问题。

Record Locks (行锁)

Record Locks,平时所说的行锁,Record Locks是通过给索引上的索引项加锁来实现的 这意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE

上面这个语句会对t.c1 = 10记录加排他锁，其他事务对 t.c1 = 10 记录的插入，更新和删除操作都会被阻塞。

在没有定义索引的情况下,InnoDB 会创建一个隐藏的聚簇索引，并使用此索引进行记录锁定。

这是一段事务日志


RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

在事务日志中，行锁一般用这种方式表示

lock_mode X locks rec but not gap

X 表示 Exclusive ，于此相对的还有S 表示Shared

Gap Lock （间隙锁）

间隙锁是对索引记录之间的间隙的锁定，或者是对第一个或最后一个索引记录之前或之后的间隙的锁定。例如，执行 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 可阻止其他事务向 t.c1 列插入值 15，无论该列中是否已存在此类值，因为范围内所有现有值之间的间隙都被锁定。

间隙可能包括一个索引值、多个索引值，甚至为空。

对于使用唯一索引搜索唯一行的SQL，不需要间隙锁定。例如，如果 id 列具有唯一索引，以下语句仅对具有 id 值 100 的行使用Record Locks，而不管其他会话是否在前面的间隙中插入行：

SELECT * FROM child WHERE id = 100;

如果 id 没有索引或具有非唯一索引，该语句确实会锁定前面的间隙。

如果搜索条件仅包含联合唯一索引的某些列,还是会加上间隙锁.

不同事务可以在间隙上持有冲突的锁。例如，事务 A 可以在间隙上持有共享间隙锁（间隙 S-锁），而事务 B 在同一间隙上持有排他间隙锁（间隙 X-锁）。跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。

一般情况下，间隙锁只作用在可重复读隔离级别（RR）下. RC隔离级别下，在进行外键约束检测和唯一键约束检测的时候，会使用到Gap锁。

Insert Intention Locks(插入意向锁)

Insert Intention Locks 是一种插入意向锁,它是在真正插入一行记录之前由insert操作设置的.

只要两个事务插入的不是同一行，就不会互相阻塞。

插入意向锁在事务日志中的表现：

lock_mode X locks gap before rec insert intention waiting

完整日志

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 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 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

RC级别下使用Gap Lock的原因

RC级别下,进行唯一键约束检测的时候需要使用到间隙锁，是为了解决主从复制的一些bug,比如

当我们并发的用INSERT …ON DUPLICATE KEY UPDATE的时候，如果我们有多个唯一索引，那么有可能会导致binlog错误，也就是会导致主从复制不一致,具体可以参考 :bugs.mysql.com/bug.php?id=…

Gap Lock练习

现在一个表有三个字段 id a b 其中b有索引现在里面插入(5，5，5)，(10，10，10)，(15，15，15)

客户端a:
begin;
select * from tb where b=10 for update;

客户端b
begin;
select * from tb where b=9 for update;

问这个b会卡住还是查出来空：查出来为空

insert (9,9,9) 会发生什么: 会卡死

Next-key lock

Next-key 锁是一种结合了Record Locks (行锁) 和Gap Locks (间隙锁)的锁定机制,它锁定一个索引记录以及记录之前的间隙.

假设我们有一个按 c1 列排序的索引，索引中的记录值为

5, 10, 20, 30, 40

对于查询 SELECT * FROM t WHERE c1 BETWEEN 10 AND 30 FOR UPDATE;InnoDB 会锁定值为 10、20 和 30 的记录，并锁定它们之间的间隙。锁定范围如下：

(5, 10], [10, 20], [20, 30]

假如索引记录为

10, 20, 30, 40

锁定范围如下：

(-∞, 10], [10, 20], [20, 30]

next-key lock的事务数据在SHOW ENGINE INNODB STATUS和INNODB监视器输出中显示如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

这里的lock_mode X可以认为就是Next-Key lock

Next-key lock引发的死锁case

SQL : insert on duplicate key update

死锁现场

LATEST DETECTED DEADLOCK
------------------------
2021-08-13 16:00:22 0x7fe08f17d700
*** (1) TRANSACTION:
TRANSACTION 10992144786, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 48550292, OS thread handle 140557710284544, query id 51167478136 10.78.184.193 dev_1597323979 update
/*id:8307ed81*/insert into store_visit_info
         ( mall_id,
                has_wechat_group,
                wechat_group_number,
                store_id )
         values ( 614802394,
                1,
                1,
                1952182965122 )
         ON DUPLICATE KEY UPDATE mall_id = 614802394,

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 771 page no 6523 n bits 776 index uk_mall_id of table `sanmateo`.`store_visit_info` trx id 10992144786 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 596 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000024a523e3; asc     $ # ;;
 1: len 8; hex 00000000001548d6; asc       H ;;

*** (2) TRANSACTION:
TRANSACTION 10992144785, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4998
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 48549906, OS thread handle 140602450106112, query id 51167478135 10.78.184.193 dev_1597323979 update
/*id:8307ed81*/insert into store_visit_info
         ( mall_id,
                has_wechat_group,
                wechat_group_number,
                store_id )
         values ( 614802394,
                1,
                1,
                1952182965122 )
         ON DUPLICATE KEY UPDATE mall_id = 614802394,
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 771 page no 6523 n bits 776 index uk_mall_id of table `sanmateo`.`store_visit_info` trx id 10992144785 lock_mode X locks gap before rec
Record lock, heap no 596 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000024a523e3; asc     $ # ;;
 1: len 8; hex 00000000001548d6; asc       H ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 771 page no 6523 n bits 776 index uk_mall_id of table `sanmateo`.`store_visit_info` trx id 10992144785 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 596 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000024a523e3; asc     $ # ;;
 1: len 8; hex 00000000001548d6; asc       H ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------

死锁原因分析

insert on duplicate key update ，当检测到唯一键冲突的时候，会在出现冲突的唯一索引处加上next-key lock。
根据上面的日志，我们可以知道，两个事务成功获取到同一段间隙的next-key lock，尝试写入数据，在写入数据前需要插入意向锁，而意向锁的插入需要等待排他锁的释放，事务10992144786在等待事务10992144785释放排他锁，而事务10992144785在等待事务10992144786释放排他锁，形成死锁。

解决方案
在有竞争条件下，我们很难避免死锁的形成，我们的数据库应对死锁的策略是主动回滚其中一个事务，因此我们重点关注事物回滚带来的数据丢失问题。

Metadata Locking 与 DDL

元数据锁定（Metadata Locking）是一种同步机制，用于确保多个并发会话在访问和修改数据库对象（如表、视图以及存储过程等）时能够保持一致性和完整性。元数据锁定通过为这些对象添加特定类型的锁来实现，例如共享锁（Shared Locks）和排他锁（Exclusive Locks）。

当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

以下是DDL操作过程中元数据锁定的一些典型行为：

修改表结构（ALTER TABLE）：在执行ALTER TABLE操作时，MySQL会为要修改的表设置一个排他锁。在表结构修改期间，其他会话无法访问或修改该表。这确保了表结构更改过程中数据的完整性。一旦表结构修改完成，排他锁会被释放，其他会话可以继续访问表。
创建/修改/删除索引（CREATE INDEX、ALTER INDEX、DROP INDEX）：在执行与索引相关的DDL操作时，MySQL会为涉及的表设置一个排他锁。在索引更改期间，其他会话无法访问或修改该表。这确保了在创建、修改或删除索引时数据的一致性。操作完成后，排他锁会被释放，其他会话可以继续访问表。

Online DDL

MySQL 5.6中引入Online DDL功能, 它允许在表结构修改过程中，表仍然可以接受读取和写入操作。这种方法可以减少由于DDL操作导致的表锁定时间，提高数据库的可用性和并发性能。

传统的DDL操作，如ALTER TABLE，通常需要在整个操作过程中对表加锁，从而阻止其他会话执行读取或写入操作。在线DDL通过允许表在结构更改过程中继续接受读取和写入操作，可以显著减少锁定时间。然而，并非所有的ALTER TABLE操作都可以作为在线DDL执行。针对InnoDB表的支持取决于操作类型、索引类型等因素。

Online DDL的行文和存储引擎,DDL类型,DML操作类型有关

在线DDL的一些关键特性：

隐式锁定：在执行在线DDL操作时，MySQL会隐式地为涉及的表添加元数据锁。这些元数据锁会在DDL操作持续期间保持，以确保其他会话不会执行与当前DDL操作冲突的操作，如另一个ALTER TABLE操作。元数据锁通常比传统DDL操作中的表锁定更加轻量级，对数据库性能的影响较小。

锁定粒度：在线DDL试图以更细的锁定粒度来降低锁定冲突。例如，对于某些操作，它可能只锁定表的某个部分，而不是整个表。这允许其他会话在DDL操作执行期间访问和修改表的未锁定部分。然而，并非所有在线DDL操作都支持细粒度锁定，某些情况下仍可能需要对整个表加锁。

并发读取和写入：在线DDL允许在表结构修改过程中执行SELECT和INSERT、UPDATE、DELETE等DML操作。这是通过在操作过程中使用共享锁和排他锁来实现的。例如，在添加或删除索引时，MySQL可能会使用共享锁来允许其他会话继续读取数据，但在此过程中阻止写操作。这有助于避免长时间的表锁定。

操作阶段：在线DDL操作可以分为多个阶段，每个阶段可能涉及不同类型的锁定。例如，某些操作可能在开始阶段需要短暂的排他锁，然后在操作的其余部分使用共享锁。这可以进一步减少锁定时间，提高数据库的可用性。

操作中断：在线DDL支持在操作过程中暂停和恢复，以便在需要时中断长时间运行的DDL操作。

Online DDL操作阶段

准备阶段：在此阶段，MySQL为执行DDL操作做好准备。这可能包括对表结构和约束进行一些初始检查，以确保操作可以安全地进行。此阶段通常不需要对表进行锁定。

建立新结构阶段：在此阶段，MySQL创建一个新的表结构，以便在操作过程中维护数据的一致性。新结构通常是旧结构的副本，但包含更改后的列、索引等。在某些情况下，这可能需要在旧表上设置共享锁，以允许其他会话继续读取数据，但阻止写操作。此阶段的锁定时间可能较短。

数据复制阶段：在此阶段，MySQL将数据从旧表复制到新结构。根据操作的类型和表的大小，这可能需要一定的时间。在数据复制期间，MySQL可能会为旧表和新结构设置共享锁和排他锁，以确保数据一致性。然而，

这些锁定通常具有较细的粒度，从而允许其他会话在操作过程中继续访问和修改未锁定的部分。

切换结构阶段：在此阶段，MySQL将新结构替换为旧表。这可能需要在表上设置短暂的排他锁，以确保其他会话在切换过程中不会访问或修改数据。一旦新结构成功替换旧表，锁定会被释放，其他会话可以继续访问和修改新表。

清理阶段：在此阶段，MySQL完成操作的最终步骤，例如删除旧表、更新元数据和统计信息等。此阶段通常不需要对表进行锁定。

死锁的定义与解决策略

死锁：两个或两个以上的进程或事务相互等待

策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

MVCC 多版本并发控制

多版本并发控制（MVCC，Multi-Version Concurrency Control）是一种用于数据库管理系统的并发控制技术。它允许多个事务同时访问和修改数据，而无需等待锁定。MVCC通过为每个事务生成数据的"快照"来实现这一目标，从而使事务能够独立于其他事务工作，而不会相互干扰。

MVCC的核心思想是在事务开始时，为每个读取的数据行创建一个版本。事务会看到这个版本，而不是实际的数据行。这样，事务可以在不影响其他事务的情况下进行读取和修改操作。

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。在可重读Repeatable reads事务隔离级别下：

SELECT时，读取创建版本号当前事务版本号。
INSERT时，保存当前事务版本号为行的创建版本号
DELETE时，保存当前事务版本号为行的删除版本号
UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

MVCC在InnoDB中的实现方式

Read View 主要来帮我们解决可见性的问题的, 即他会来告诉我们本次事务应该看到哪个快照，不应该看到哪个快照。

在 Read View 中有几个重要的属性：

trx_ids，系统当前未提交的事务 ID 的列表。
low_limit_id，未提交的事务中最大的事务 ID。
up_limit_id，未提交的事务中最小的事务 ID。
creator_trx_id，创建这个 Read View 的事务 ID。

Innodb存储引擎中，每行数据都包含了一些隐藏字段：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR和DELETE_BIT。

DB_TRX_ID：用来标识最近一次对本行记录做修改的事务的标识符，即最后一次修改本行记录的事务id。delete操作在内部来看是一次update操作，更新行中的删除标识位DELELE_BIT。DB_ROLL_PTR：指向当前数据的undo log记录，回滚数据通过这个指针来寻找记录被更新之前的内容信息。

一行数据会对应多行这样的记录，例如，如果有多个事物对同一行数据进行更新，会形成这样的记

Untitled 1.png

事物启动的时候，mysql会为这个事物创建一个数组A，数组的元素为该事务启动瞬间，系统中启动了但还没提交的所有事务，数组中事务id的最小值记为low_limit_id，当前系统里面已经创建过的事务 ID 的最大值加 1 记为up_limit_id

读取某一行数据，如果该数据最新的事务ID小于low_limit_id 那么该版本是可见的

如果事务ID大于up_limit_id 该版本不可见根据回滚指针找到上一个版本记录

如果事务ID落在low_limit_id和up_limit_id 之间如果A中包含该事务ID，该版本不可见，未包含该事务ID 该版本可见

参考

MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking

MySQL :: MySQL 8.0 Reference Manual :: 8.11.4 Metadata Locking

Innodb中的事务隔离级别和锁的关系

我的阿里二面，为什么MySQL选择Repeatable Read作为默认隔离级别？

当并发insert on duplicate key update遇见死锁：更新丢失-阿里云开发者社区

【官方文档】mysql gap lock next-key lock

MySQL Online DDL 原理和踩坑

Mysql数据一致性与处理并发访问处理:隔离级别,锁策略与MVCC

Record Locks (行锁)

Gap Lock （间隙锁）

Insert Intention Locks(插入意向锁)

RC级别下使用Gap Lock的原因

Gap Lock练习

Next-key lock

Next-key lock引发的死锁case

Metadata Locking 与 DDL

Online DDL

死锁的定义与解决策略

MVCC在InnoDB中的实现方式

相关文章

发布评论取消回复

捡田螺的小男孩

[20240731]tmux配置使用问题.txt

百度搜索：蓝易云 – gitlab常用命令大全

Oracle 与人才市场平台开展新合作，助力 HR 主管简化招聘、扩展人才库并改善求职者体验

Mysql数据一致性与处理并发访问处理:隔离级别,锁策略与MVCC

Record Locks (行锁)

Gap Lock （间隙锁）

Insert Intention Locks(插入意向锁)

RC级别下使用Gap Lock的原因

Gap Lock练习

Next-key lock

Next-key lock引发的死锁case

Metadata Locking 与 DDL

Online DDL

死锁的定义与解决策略

MVCC在InnoDB中的实现方式

相关文章

发布评论 取消回复

捡田螺的小男孩

[20240731]tmux配置使用问题.txt

百度搜索：蓝易云 – gitlab常用命令大全

Oracle 与人才市场平台开展新合作，助力 HR 主管简化招聘、扩展人才库并改善求职者体验

发布评论取消回复