Mysql相关知识2：Mysql隔离级别、MVCC、锁

MySQL的隔离级别

在 MySQL 中，隔离级别定义了事务之间相互隔离的程度，用于控制一个事务对数据的修改在何时以及如何被其他事务可见。MySQL 支持四种隔离级别，从低到高依次为：

• 读未提交（Read Uncommitted）
  • 特点：一个事务可以读取另一个未提交事务的数据，这是最低的隔离级别，会导致脏读（Dirty Read）问题，即一个事务读取到了另一个事务未提交的数据，若该事务回滚，读取的数据就是无效的。
  • 示例：事务 A 修改了某条记录但未提交，事务 B 此时读取了该记录，之后事务 A 回滚，那么事务 B 读取的数据就是脏数据。
• 读已提交（Read Committed）
  • 特点：一个事务只能读取另一个已经提交事务的数据，避免了脏读问题，但可能会出现不可重复读（Non - Repeatable Read）问题。不可重复读是指在一个事务内多次读取同一数据时，由于其他事务的修改，导致每次读取的结果不一致。
  • 示例：事务 A 第一次读取某条记录，然后事务 B 修改并提交了该记录，事务 A 再次读取该记录时，得到的结果与第一次不同。
• 可重复读（Repeatable Read）
  • 特点：在一个事务内多次读取同一数据时，会保证读取到的数据是一致的，避免了不可重复读问题，但可能会出现幻读（Phantom Read）问题。幻读是指在一个事务内，按照相同的查询条件进行多次查询时，由于其他事务插入或删除了符合条件的记录，导致查询结果集发生了变化。
  • 示例：事务 A 按照某个条件查询到了 10 条记录，然后事务 B 插入了一条符合该条件的记录并提交，事务 A 再次按照相同条件查询时，会得到 11 条记录。MySQL 的 InnoDB 存储引擎通过多版本并发控制（MVCC）和间隙锁（Gap Lock）来解决幻读问题。
• 串行化（Serializable）
  • 特点：最高的隔离级别，事务之间是串行执行的，即一个事务执行完后另一个事务才开始执行，避免了脏读、不可重复读和幻读问题，但会导致并发性能下降，因为事务需要排队执行。
  • 示例：所有事务依次执行，不会出现并发冲突，但会降低系统的吞吐量。

可以使用以下 SQL 语句查看和设置 MySQL 的隔离级别：

-- 查看当前会话的隔离级别
SELECT @@tx_isolation;
-- 设置当前会话的隔离级别为可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

可重复读的实现原理

在 MySQL 的 InnoDB 存储引擎中，可重复读隔离级别主要通过多版本并发控制（MVCC）和间隙锁（Gap Lock）来实现。

• 多版本并发控制（MVCC）
  • 原理：MVCC 是一种基于数据多版本的并发控制机制，它通过为每条记录保存多个版本，使得不同事务可以在不同的版本上进行操作，从而实现并发访问。在 InnoDB 中，每行记录除了实际的数据外，还包含两个隐藏列：创建时间戳（DB_TRX_ID）和删除时间戳（DB_ROLL_PTR）。创建时间戳记录了创建该版本的事务 ID，删除时间戳指向该记录的上一个版本。
  • 读操作：当一个事务执行读操作时，它会根据自己的事务 ID 和当前系统的事务 ID 快照，选择合适的记录版本进行读取。具体来说，事务会读取创建时间戳小于等于自己事务 ID 的记录版本，并且该记录版本的删除时间戳为空或者大于自己的事务 ID。这样可以保证事务读取到的是在它开始之前已经提交的数据版本，从而实现可重复读。
  • 写操作：当一个事务执行写操作时，会创建一个新的记录版本，并更新相应的时间戳。
• 间隙锁（Gap Lock）
  • 原理：间隙锁是为了解决幻读问题而引入的。当一个事务在可重复读隔离级别下执行查询时，InnoDB 会对查询条件所涉及的范围加间隙锁。间隙锁会锁定索引记录之间的间隙，防止其他事务在该间隙内插入新的记录，从而避免了幻读的发生。
  • 示例：假设事务 A 执行 SELECT * FROM table WHERE id BETWEEN 1 AND 10 FOR UPDATE;，InnoDB 会对 id 为 1 到 10 之间的间隙加锁，即使这些间隙中没有实际的记录。这样，其他事务就无法在这个范围内插入新的记录，从而保证了事务 A 在后续的查询中不会出现幻读。

综上所述，MVCC 保证了事务在读取数据时可以看到一致的数据版本，避免了不可重复读问题，而间隙锁则进一步解决了幻读问题，使得可重复读隔离级别更加可靠。

Mysql锁

在 MySQL 中，锁机制是保证数据一致性和并发控制的重要手段。按照不同的分类标准，MySQL 有多种类型的锁，下面为你详细介绍：

按锁的粒度分类

• 表级锁
  • 特点：对整张表进行锁定，开销小，加锁快；不会出现死锁，但锁定粒度大，发生锁冲突的概率高，并发度低。
  • 常见类型
    • 表共享读锁（Table Read Lock）：多个事务可以同时对一张表加读锁，加了读锁的表可以被其他事务读取，但不能被其他事务写入。例如，事务 A 对表 t1 加了读锁，事务 B 也可以对表 t1 加读锁进行查询操作，但如果事务 B 想要对表 t1 进行写操作，就需要等待事务 A 释放读锁。
    • 表独占写锁（Table Write Lock）：一个事务对表加了写锁后，其他事务不能对该表加任何类型的锁，只有持有写锁的事务可以对表进行读写操作。比如，事务 A 对表 t1 加了写锁，那么在事务 A 释放写锁之前，事务 B 无法对表 t1 进行读或写操作。
• 行级锁
  • 特点：对表中的某一行进行锁定，锁定粒度小，发生锁冲突的概率低，并发度高；但开销大，加锁慢，可能会出现死锁。
  • 常见类型
    • 共享锁（Shared Lock，S 锁）：也称为读锁，多个事务可以同时对同一行数据加共享锁，加了共享锁的行可以被其他事务读取，但不能被其他事务写入。例如，事务 A 对表 t1 的某一行加了共享锁，事务 B 也可以对该行加共享锁进行查询操作，但如果事务 B 想要对该行进行写操作，就需要等待事务 A 释放共享锁。
    • 排他锁（Exclusive Lock，X 锁）：也称为写锁，一个事务对某一行数据加了排他锁后，其他事务不能对该行数据加任何类型的锁，只有持有排他锁的事务可以对该行数据进行读写操作。比如，事务 A 对表 t1 的某一行加了排他锁，那么在事务 A 释放排他锁之前，事务 B 无法对该行进行读或写操作。
• 页级锁
  • 特点：锁定粒度介于表级锁和行级锁之间，开销和加锁时间也介于两者之间，并发度一般。页级锁是 MySQL 中 BDB 存储引擎使用的锁类型，但 BDB 存储引擎已经逐渐被淘汰，所以页级锁在实际应用中使用较少。

按锁的使用方式分类

• 意向锁
  • 作用：意向锁是一种表级锁，用于表明事务对表中的某些行或页有特定类型的锁。意向锁的存在是为了协调不同粒度的锁之间的关系，提高加锁的效率。
  • 常见类型
    • 意向共享锁（Intention Shared Lock，IS 锁）：表示事务打算对表中的某些行加共享锁。例如，当事务想要对表中的某一行加共享锁时，会先对表加意向共享锁。
    • 意向排他锁（Intention Exclusive Lock，IX 锁）：表示事务打算对表中的某些行加排他锁。比如，当事务想要对表中的某一行加排他锁时，会先对表加意向排他锁。
• 记录锁（Record Lock）
  • 作用：行级锁的一种，用于锁定表中的某一行记录。例如，SELECT * FROM table_name WHERE id = 1 FOR UPDATE; 语句会对 id 为 1 的行记录加排他锁。
• 间隙锁（Gap Lock）
  • 作用：用于锁定索引记录之间的间隙，防止其他事务在该间隙内插入新的记录，从而避免幻读问题。例如，SELECT * FROM table_name WHERE id BETWEEN 1 AND 10 FOR UPDATE; 语句会对 id 为 1 到 10 之间的间隙加间隙锁。
• 临键锁（Next - Key Lock）
  • 作用：是记录锁和间隙锁的组合，既锁定某一行记录，又锁定该行记录前面的间隙。在可重复读隔离级别下，InnoDB 存储引擎默认使用临键锁来防止幻读。例如，SELECT * FROM table_name WHERE id > 5 FOR UPDATE; 语句会对满足条件的行记录及其前面的间隙加临键锁。

按锁的状态分类

• 乐观锁
  • 原理：乐观锁假设在大多数情况下，事务之间不会发生冲突，因此在操作数据时不会对数据进行加锁，而是在更新数据时检查数据是否被其他事务修改过。通常通过在表中添加一个版本号或时间戳字段来实现。
  • 示例：在更新数据时，先查询数据的版本号，然后在更新语句中添加版本号的条件，只有当版本号与查询时一致时才进行更新。例如：

-- 查询数据
SELECT id, name, version FROM table_name WHERE id = 1;
-- 更新数据
UPDATE table_name SET name = 'new_name', version = version + 1 WHERE id = 1 AND version = <查询时的版本号>;

• 悲观锁
  • 原理：悲观锁假设在大多数情况下，事务之间会发生冲突，因此在操作数据时会对数据进行加锁，以防止其他事务对数据进行修改。上面提到的共享锁、排他锁等都属于悲观锁。