基础概念
| 脏读 | 一个事务中访问到了另外一个事务未提交的数据 |
| 不可重复读 | 一个事务内根据同一个条件对行记录进行多次查询，返回的结果不一致 |
| 幻读 | 同一个事务内多次查询返回的结果集不一样（增加了或者减少） |
隔离级别
| 隔离级别 | 描述 | 锁 |
| 读未提交(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 将使用表锁。
jsx复制代码SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
上面这个语句会对t.c1 = 10记录加排他锁，其他事务对 t.c1 = 10 记录的插入，更新和删除操作都会被阻塞。
在没有定义索引的情况下,InnoDB 会创建一个隐藏的聚簇索引，并使用此索引进行记录锁定。
这是一段事务日志
jsx复制代码
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 ;;
在事务日志中，行锁一般用这种方式表示
jsx复制代码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，而不管其他会话是否在前面的间隙中插入行：
sql复制代码SELECT * FROM child WHERE id = 100;
如果 id 没有索引或具有非唯一索引，该语句确实会锁定前面的间隙。
如果搜索条件仅包含联合唯一索引的某些列,还是会加上间隙锁.
不同事务可以在间隙上持有冲突的锁。例如，事务 A 可以在间隙上持有共享间隙锁（间隙 S-锁），而事务 B 在同一间隙上持有排他间隙锁（间隙 X-锁）。跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。
一般情况下，间隙锁只作用在可重复读隔离级别（RR）下. RC隔离级别下，在进行外键约束检测和唯一键约束检测的时候，会使用到Gap锁。
Insert Intention Locks(插入意向锁)
Insert Intention Locks 是一种插入意向锁,它是在真正插入一行记录之前由insert操作设置的.
只要两个事务插入的不是同一行，就不会互相阻塞。
插入意向锁在事务日志中的表现：
jsx复制代码lock_mode X locks gap before rec insert intention waiting
完整日志
jsx复制代码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)
java复制代码客户端a:
begin;
select * from tb where b=10 for update;
java复制代码客户端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 列排序的索引，索引中的记录值为
sql复制代码5, 10, 20, 30, 40
对于查询 SELECT * FROM t WHERE c1 BETWEEN 10 AND 30 FOR UPDATE;InnoDB 会锁定值为 10、20 和 30 的记录，并锁定它们之间的间隙。锁定范围如下：
sql复制代码(5, 10], [10, 20], [20, 30]
假如索引记录为
sql复制代码10, 20, 30, 40
锁定范围如下：
sql复制代码(-∞, 10], [10, 20], [20, 30]
next-key lock的事务数据在SHOW ENGINE INNODB STATUS和INNODB监视器输出中显示如下：
java复制代码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
死锁现场
java复制代码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记录，回滚数据通过这个指针来寻找记录被更新之前的内容信息。
一行数据会对应多行这样的记录，例如，如果有多个事物对同一行数据进行更新，会形成这样的记
事物启动的时候，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 该版本可见
原文链接：https://juejin.cn/post/7251501945272844348