likes
comments
collection
share

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

作者站长头像
站长
· 阅读数 40

基础概念

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

隔离级别

隔离级别描述锁
读未提交(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中的锁有很多种,按照资源访问限制的不同程度,分为:

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

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

  1. 表锁（Table Locks）：表锁是MySQL中最基本的锁策略，适用于MyISAM、MEMORY等存储引擎。当一个事务需要对表进行操作时，会锁定整个表，防止其他事务在锁定期间对表进行操作。表锁的粒度较大，导致锁冲突的概率较高，因此在高并发场景下，表锁的性能较低。
  2. 行锁（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;
  1. 问这个b会卡住 还是查出来空：查出来为空
  2. 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的一些关键特性：

  1. 隐式锁定：在执行在线DDL操作时，MySQL会隐式地为涉及的表添加元数据锁。这些元数据锁会在DDL操作持续期间保持，以确保其他会话不会执行与当前DDL操作冲突的操作，如另一个ALTER TABLE操作。元数据锁通常比传统DDL操作中的表锁定更加轻量级，对数据库性能的影响较小。
  2. 锁定粒度：在线DDL试图以更细的锁定粒度来降低锁定冲突。例如，对于某些操作，它可能只锁定表的某个部分，而不是整个表。这允许其他会话在DDL操作执行期间访问和修改表的未锁定部分。然而，并非所有在线DDL操作都支持细粒度锁定，某些情况下仍可能需要对整个表加锁。
  3. 并发读取和写入：在线DDL允许在表结构修改过程中执行SELECT和INSERT、UPDATE、DELETE等DML操作。这是通过在操作过程中使用共享锁和排他锁来实现的。例如，在添加或删除索引时，MySQL可能会使用共享锁来允许其他会话继续读取数据，但在此过程中阻止写操作。这有助于避免长时间的表锁定。
  4. 操作阶段：在线DDL操作可以分为多个阶段，每个阶段可能涉及不同类型的锁定。例如，某些操作可能在开始阶段需要短暂的排他锁，然后在操作的其余部分使用共享锁。这可以进一步减少锁定时间，提高数据库的可用性。
  5. 操作中断：在线DDL支持在操作过程中暂停和恢复，以便在需要时中断长时间运行的DDL操作。

Online DDL操作阶段

  1. 准备阶段：在此阶段，MySQL为执行DDL操作做好准备。这可能包括对表结构和约束进行一些初始检查，以确保操作可以安全地进行。此阶段通常不需要对表进行锁定。
  2. 建立新结构阶段：在此阶段，MySQL创建一个新的表结构，以便在操作过程中维护数据的一致性。新结构通常是旧结构的副本，但包含更改后的列、索引等。在某些情况下，这可能需要在旧表上设置共享锁，以允许其他会话继续读取数据，但阻止写操作。此阶段的锁定时间可能较短。
  3. 数据复制阶段：在此阶段，MySQL将数据从旧表复制到新结构。根据操作的类型和表的大小，这可能需要一定的时间。在数据复制期间，MySQL可能会为旧表和新结构设置共享锁和排他锁，以确保数据一致性。然而，这些锁定通常具有较细的粒度，从而允许其他会话在操作过程中继续访问和修改未锁定的部分。
  4. 切换结构阶段：在此阶段，MySQL将新结构替换为旧表。这可能需要在表上设置短暂的排他锁，以确保其他会话在切换过程中不会访问或修改数据。一旦新结构成功替换旧表，锁定会被释放，其他会话可以继续访问和修改新表。
  5. 清理阶段：在此阶段，MySQL完成操作的最终步骤，例如删除旧表、更新元数据和统计信息等。此阶段通常不需要对表进行锁定。

死锁的定义与解决策略

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

策略：

  1. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  2. 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 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。

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

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

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

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

事物启动的时候，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 原理和踩坑

转载自:https://juejin.cn/post/7251501945272844348
评论
请登录