深入理解Mysql事务隔离级别与锁机制

1、概述

我们的数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。

2、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。

原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

3、并发事务处理带来的问题

隔离级别是为了解决并发事务可能出现的三类问题而设计的：

重要区别：

不可重复读：针对已存在行的数据被修改。
幻读：针对行数变化（新增或删除行）。

4、事务隔离级别

“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。

注：MySQL的InnoDB在可重复读级别下，通过MVCC解决了快照读的幻读，通过间隙锁解决了当前读的幻读。

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

mysql8.0以前版本
常看当前数据库的事务隔离级别: show variables like 'tx_isolation';
设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

8.0版本
查看当前数据库的事务隔离级别: show variables like '%isolation%';
设置事务隔离级别：set transaction_isolation='REPEATABLE-READ';

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别。

5、锁详解

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类

从性能上分为乐观锁(用版本对比来实现)和悲观锁 。
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)。

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

select * from T where id=1 lock in share mode

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁。

select * from T where id=1 for update

从对数据操作的粒度分，分为表锁和行锁。

5.1、表锁：

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生冲突的概率最高，并发度最低。

‐‐手动增加表锁
 lock table 表名称 read(write),表名称2 read(write);
‐‐查看表上加过的锁
 show open tables;
 查看存在的表锁： Show OPEN TABLES where In_use > 0; In_use = 1 表示被锁
‐‐删除表锁
unlock tables;

5.2、行锁

偏向InnoDB开销大，加锁慢；会出现死锁；锁粒度最小，发生冲突的概率最低，并发度也最高。

InnoDB与MyISAM最大的不同有两点：1：InnnoDB支持事务； 2：采用了行级锁。

注意，InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(可重复读级别会升级为表锁，读已提交级别不会升级为表锁)

比如我们在可重复读级别执行如下sql ： select * from account where name = 'lilei' for update; ‐‐where条件里的name字段无索引则其它Session对该表任意一行记录做修改操作都会被阻塞住。

关于RR级别行锁升级为表锁的原因分析

因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其它事务修改(不可重复读问题) 或间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录。

通过分析 InnoDB_row_lock 状态变量来分析系统上行锁的争夺情况：

命令： show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits: 当前正在等待锁定的数量。
Innodb_row_lock_time：从系统启动到现在锁定的总时长。
Innodb_row_lock_time_avg：每次等待锁花费的平均时间。
Innodb_row_lock_time_max：从系统启动到现在等待最长的时间。
Innodb_row_lock_waits：系统启动到现在总共等待的次数。

总结： MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。 InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

深入理解 MySQL InnoDB 存储引擎中的这几种锁：X锁、S锁、IX、IS锁。它们是理解 InnoDB 并发控制机制的核心。

锁兼容性矩阵：

1、X锁 (独占锁 / 排他锁)- Exclusive Lock

作用：用于数据修改（写操作）。一个事务获取了某行（或某表）的X锁后，其他任何事务都不能再获取该行（或该表）的任何类型的锁（X、S、IX、IS）。这是最强的锁。
SQL 操作：
UPDATE ...
DELETE ...
INSERT ... (InnoDB对新插入的行加隐式X锁)。
显式加锁：SELECT ... FOR UPDATE。
与其他锁的关系：与所有锁都不兼容。

2. S锁 (共享锁) -- Shared Lock

作用：用于数据读取。一个事务获取了某行（或某表）的S锁后，其他事务也可以获取该行（或该表）的S锁或IS锁，但不能获取X锁或IX锁。多个读操作可以同时进行。
SQL 操作：
显式加锁：SELECT ... LOCK IN SHARE MODE (在 MySQL 8.0+ 中，更推荐语义更清晰的 SELECT ... FOR SHARE)。
与其他锁的关系：
与 S锁、IS锁兼容。
与 X锁、IX锁冲突。

3. IX锁 / IS锁 (意向锁)

意向锁是表级锁，是 InnoDB 为了支持多粒度锁定（允许行锁和表锁共存）而引入的机制。它的存在是为了快速判断表内是否有已被锁定的行，避免为了加一个表锁而去全表扫描每一行是否有行锁。

IX锁 (意向独占锁)

作用：表示事务打算（意向）在该表的某些行上设置X锁。它是在申请行级X锁之前，由 InnoDB 自动、隐式地对表加上的锁。
目的：如果一个事务对表T加了IX锁，就告诉其他事务：“我可能要修改表中的某些行，你们如果要给整张表加锁（比如ALTER TABLE需要的X表锁），请稍等，先检查一下我是否已经锁定了某些行”。
兼容性：

与 IX锁、IS锁兼容（多个事务可以同时打算修改不同的行）。与 S锁（表级）冲突（因为S表锁要求整个表只读）。与 X锁（表级）冲突。

IS锁 (意向共享锁)

作用：表示事务打算（意向）在该表的某些行上设置S锁。它是在申请行级S锁之前，由 InnoDB 自动、隐式地对表加上的锁。
目的： “我可能要读取表中的某些行，你们如果要给整张表加X锁，请稍等”。
兼容性：
- 与 IS锁、IX锁、S锁（表级）都兼容（因为读不阻塞读，也不阻塞其他事务“打算”修改）。
- 与 X锁（表级）冲突。

注意事项：

意向锁是自动加的：你不需要在 SQL 中指定 LOCK TABLE ... IS/IX。

当你执行 SELECT ... FOR UPDATE 时，InnoDB 会：

先自动对表加上 IX锁。
然后对符合条件的行加上 X锁。

意向锁是表级锁，但表明的是一种“意向”而非实际锁定整个表。它不会阻止其他事务也获取同一个表的IX或IS锁。真正冲突发生在行级或真正的表级锁上。

对于普通的SELECT。因为InnoDB默认的读（快照读）不加任何锁，使用MVCC机制。

间隙锁：

间隙锁是锁住索引记录之间的间隙，或者锁住第一条索引记录之前或最后一条索引记录之后的范围。它锁定的不是记录本身，而是记录之间的“空隙”。间隙锁是在可重复读隔离级别下才会生效，在innoDB下解决幻读。间隙锁之间完全兼容，多个事务可以同时持有相同间隙的间隙锁，这是因为间隙锁只用于阻止其他事务插入新记录，不阻止其他事务获取相同的间隙锁，但间隙锁会阻塞插入意向锁。间隙锁是基于索引的，如果没有索引，InnoDB会对全表加间隙锁（性能灾难！）。

假设有一个索引列 age，现有记录值：10, 20, 30, 40

间隙锁就是锁住这些括号 () 表示的开区间。

间隙锁举例：

1. 纯间隙锁（只锁间隙，不锁记录本身。）

-- 假设age上有索引，现有记录：10, 30
SELECT * FROM users WHERE age = 20 FOR UPDATE;
-- age=20的记录不存在，会锁住(10,30)这个间隙

2. 临键锁（Next-Key Lock）

记录锁 + 间隙锁的组合，锁住记录本身和它前面的间隙。这是 InnoDB 的默认行锁算法，锁住的是 (前一个索引值, 当前索引值]

6、MVCC（多版本并发控制）

6.1、核心思想：用“快照”代替“锁”

传统的并发控制（如行锁）在读写冲突时，通常会通过锁来让一个事务等待另一个事务完成。这虽然能保证数据安全，但严重影响了并发性能。

MVCC 采用了一种更聪明的方式：为每一行数据维护多个版本。这样，读操作不会被写操作阻塞，写操作也不会被读操作阻塞，从而极大地提高了并发性。

6.2、MVCC 的关键实现机制

MVCC 的实现通常依赖于三个核心组件：

隐式字段
Undo Log（回滚日志）
Read View（读视图）

1. 隐式字段

InnoDB 在每个数据行（记录）中，除了我们自定义的字段外，还会自动添加几个隐藏的系统字段：

DB_TRX_ID（6字节）：事务ID。表示最近一次插入或更新该行记录的事务ID。
DB_ROLL_PTR（7字节）：回滚指针。指向该行记录在 Undo Log 中的上一个历史版本。它形成了一个版本链。
DB_ROW_ID：行ID（如果没有主键）。

2. Undo Log（回滚日志）

Undo Log 主要有两个作用：

事务回滚：在事务执行过程中发生错误或执行 ROLLBACK 时，可以利用 Undo Log 将数据恢复到事务开始前的状态。
实现 MVCC：当某行数据被更新时，旧版本的数据不会被立即删除，而是会被拷贝到 Undo Log 中。DB_ROLL_PTR 指针就指向了这个旧版本。如果旧版本又被更新，它会指向更旧的版本，从而形成一个单向链表，即版本链。

3. Read View（读视图）

Read View 是 MVCC 的灵魂。它决定了对于一个事务来说，版本链中的哪个版本是“可见的”。

当一个事务执行快照读（普通的 SELECT 语句，不包括 SELECT ... FOR UPDATE）时，它会创建一个 Read View。这个 Read View 主要包含以下关键信息：

m_ids：生成 Read View 时，系统中已开始但未提交的事务ID列表。
min_trx_id：m_ids 中的最小值。
max_trx_id：生成 Read View 时，系统应该分配给下一个事务的ID。
creator_trx_id：创建该 Read View 的事务ID（对于只读事务，这个ID可能为0）。

6.3、可见性判断规则

当一个事务拿着它的 Read View 去访问某行数据的版本链时，它会从最新的版本开始，依次检查每个版本的 DB_TRX_ID，并应用以下规则来判断该版本是否可见：

如果 DB_TRX_ID 等于 creator_trx_id，说明该版本是当前事务自己修改的，可见。
如果 DB_TRX_ID 小于 min_trx_id，说明该版本在当前事务开始之前就已经提交了，可见。
如果 DB_TRX_ID 大于等于 max_trx_id，说明该版本是在当前事务创建 Read View 之后才开启的，不可见。
如果 DB_TRX_ID 在 min_trx_id 和 max_trx_id 之间，则需要判断 DB_TRX_ID 是否在 m_ids（活跃事务列表）中：
- 如果在，说明生成该版本的事务在创建 Read View 时还未提交，该版本不可见。
- 如果不在，说明生成该版本的事务在创建 Read View 时已经提交，该版本可见。

如果当前版本不可见，就顺着版本链的 ROLL_PTR 找到上一个版本，重复上述判断规则，直到找到一个可见的版本或到达链尾。

6.4、MVCC 与隔离级别

读已提交（READ COMMITTED）：每次执行快照读时都会生成一个新的 Read View。所以，它能读到其他事务最新已提交的数据。
可重复读（REPEATABLE READ）：只在第一次执行快照读时生成一个 Read View，后续都复用这个视图。所以，它在事务期间看到的数据是一致的。

结论：

UPDATE、DELETE、SELECT ... FOR UPDATE 等写操作，会触发“当前读”。当前读的含义：它不是读取快照版本，而是读取数据的最新已提交版本，并加锁。一个事务总是能看到它自己所做的修改。

案例：有一张初始表t_user, 数据为空。mysql5.7, 使用可重复读隔离级别。

测试表：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

可见性测试：

事务1
BEGIN
// 快照读 --生成read-view
SELECT * FROM t_user;

事务2
BEGIN
INSERT INTO t_user VALUES(20, 'bole1');
COMMIT

// 当前读，能读到已经提交的最新的数据
UPDATE t_user set name = 'bole66' WHERE id = 20;
// 在当前读到最新的数据后，事务2的数据对事务1可见。因此统计结果=1
SELECT COUNT(1) FROM t_user;
COMMIT

posted @ 2020-11-07 15:11 邓维-java 阅读(647) 评论(0) 收藏举报

刷新页面返回顶部

学如逆水行舟，不进则退！

当你的才华配不上你的任性时，那就努力奋斗吧！

深入理解Mysql事务隔离级别与锁机制

深入理解Mysql事务隔离级别与锁机制

1、概述

2、事务及其ACID属性

3、并发事务处理带来的问题

4、事务隔离级别

5、锁详解

5.2、行锁

1、X锁 (独占锁 / 排他锁)- Exclusive Lock

2. S锁 (共享锁) -- Shared Lock

3. IX锁 / IS锁 (意向锁)

IX锁 (意向独占锁)

IS锁 (意向共享锁)

间隙锁：

1. 纯间隙锁（只锁间隙，不锁记录本身。）

2. 临键锁（Next-Key Lock）

6、MVCC（多版本并发控制）

6.1、核心思想：用“快照”代替“锁”

6.2、MVCC 的关键实现机制

1. 隐式字段

2. Undo Log（回滚日志）

3. Read View（读视图）

6.3、可见性判断规则

6.4、MVCC 与隔离级别

公告

学如逆水行舟， 不进则退！

当你的才华配不上你的任性时， 那就努力奋斗吧！

深入理解Mysql事务隔离级别与锁机制

深入理解Mysql事务隔离级别与锁机制

1、概述

2、事务及其ACID属性

3、并发事务处理带来的问题

4、事务隔离级别

5、锁详解

5.2、行锁

1、X锁 (独占锁 / 排他锁)- Exclusive Lock

2. S锁 (共享锁) -- Shared Lock

3. IX锁 / IS锁 (意向锁)

IX锁 (意向独占锁)

IS锁 (意向共享锁)

间隙锁：

1. 纯间隙锁（只锁间隙，不锁记录本身。）

2. 临键锁（Next-Key Lock）

6、MVCC（多版本并发控制）

6.1、核心思想：用“快照”代替“锁”

6.2、MVCC 的关键实现机制

1. 隐式字段

2. Undo Log（回滚日志）

3. Read View（读视图）

6.3、可见性判断规则

6.4、MVCC 与隔离级别

公告

学如逆水行舟，不进则退！

当你的才华配不上你的任性时，那就努力奋斗吧！