MySQL事务隔离

🕗 发布于 2024-07-08 16:25 mysql 数据库

MySQL事务隔离

前言

前言

在 MySQL 中，事务隔离是指控制并发事务之间相互影响的程度。MySQL 支持多种事务隔离级别，包括读未提交、读已提交、可重复读和串行化。这些事务隔离级别可以通过设置 transaction_isolation 参数来进行配置。

在网上看到很多文章感觉看完以后影响都不是特别深刻，以我的理解来进行知识梳理，并将其记录，便于更多同学及以后知识回顾。

锁

在 MySQL 中，可以使用不同的锁机制来实现数据的并发控制和事务隔离。

下面以此表结构为例进行案例讲解：

CREATE TABLE `product` (
  `id` int NOT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `number` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

在这里插入图片描述

共享锁（Shared Lock）

也称为读锁（Read Lock），多个事务可以同时获取共享锁，但不允许其他事务修改该资源。共享锁可以并发地被多个事务持有，但不能与排他锁（Exclusive Lock）同时存在。

SELECT ... LOCK IN SHARE MODE;

示例一：第一个事务加共享锁，第二个事务尝试进行修改操作。

再第一个事务里面给查询语句加锁，不进行commit操作。

#加上共享锁
START transaction;
select * from `product` lock in SHARE mode;
COMMIT;

然后在第二个事务里面尝试修改内容

#尝试修改数据
START transaction;
-- insert into product(id,product_name,number) values(2,'测试商品2',100)
-- DELETE from product where id ='2'
update product set product_name='测试商品2' where id=1;
COMMIT;

我们会发现修改（含插入、删除操作）的时候会一直等待上一个事务提交，如果长时间未提交，则会超时。然后在第一个事务里commit操作后，第二个事务才会被执行，执行结果如下：

在这里插入图片描述

示例二：第一个事务加共享锁，第二个事务也加共享锁。

再第一个事务里面给查询语句加锁，不进行commit操作。

#加上共享锁
START transaction;
select * from `product` lock in SHARE mode;
COMMIT;

然后在第二个事务里面也给查询语句加锁

#加上共享锁
START transaction;
select * from `product` lock in SHARE mode;
COMMIT;

我们会发现多个事务的共享锁可以被同时持有，不会等待上一个事务提交，执行结果如下：

在这里插入图片描述

一个事务加了共享锁，其它事务的新增、删除、修改操作都会等待锁被释放，但是多个共享锁可以被多个查询事务持有。

排他锁（Exclusive Lock）

也称为写锁（Write Lock），只有一个事务可以获取排他锁，用于保证事务对数据的独占访问。排他锁不能与其他任何锁（共享锁或排他锁）同时存在，新增、删除、修改操作会自动加上排他锁。

SELECT ... FOR UPDATE;

示例一：第一个事务进行新增操作，第二个事务进行删除操作。

再第一个事务里面进行新增操作，不进行commit操作。

#加上排他锁
START transaction;
insert into product(id,product_name,number) values(2,'测试商品2',100)
COMMIT;

然后再第二个事务进行删除操作。

#加上排他锁
start transaction;
DELETE from product where id ='2'
-- update product set product_name='测试商品2' where id=1;
COMMIT

我们会发现插入、删除（含修改）操作的时候会一直等待上一个事务提交，如果长时间未提交，则会超时。执行结果如下：

在这里插入图片描述

示例二：第一个事务加排他锁，第二个事务也加排他锁。

再第一个事务里面给查询语句加锁，不进行commit操作。

#加上排他锁
START transaction;
select * from `product` for update;
COMMIT;

然后再第二个事务里面给查询语句加锁。

#加上排他锁
START transaction;
select * from `product` for update;
COMMIT;

我们会发现查询的时候会一直等待上一个事务提交，如果长时间未提交，则会超时。然后在第一个事务里commit操作后，第二个事务才会被执行，当第二个事务commit操作后（也就是所有的排他锁释放），其他事务才会执行。执行结果如下：

在这里插入图片描述

排他锁不能与其他任何锁（共享锁或排他锁）同时存在，如果多个事务加了排他锁，那么其他事务就会等待上一个事务提交才会执行。

行级锁（Row-Level Lock）

在 InnoDB 存储引擎中支持行级锁，它可以在事务中对表中的单行或多行数据进行加锁，以实现更细粒度的并发控制。行级锁可以是共享锁或排他锁。

以排他锁为例，假设，第一个事务先给id=1的条件加上排他锁：

#加上排他锁
START transaction;
select * from `product` where id =1 for update;
COMMIT;

第二个事务进行插入操作

#加上排他锁
START transaction;
insert into product(id,product_name,number) values(2,'测试商品2',100);
COMMIT;

第三个事务给id=1的条件进行修改操作

#加上排他锁
START transaction;
update product set product_name='测试商品2' where id=1;
COMMIT;

我们会发现第二个事务进行插入操作（只要不是被加锁的该条数据的操作）的时候不需要等待上一个事务释放，但是第三个事务修改的时候需要等到第一个事务释放后才能执行，执行结果如下：

在这里插入图片描述
行级锁主要就是三种，记录锁（record lock），间隙锁（gap lock），和临键锁（next-key lock），默认情况下都是：记录锁。

再MySQL 8.0中好像没有间隙锁（gap lock），和临键锁（next-key lock），用于锁定一个范围而不是特定的记录。下面是一个示例代码，演示了如何在MySQL中使用间隙锁：

#加上排他锁
START transaction;
SELECT * FROM product WHERE id BETWEEN 1 AND 5 FOR UPDATE;
-- SELECT * FROM product WHERE id > 4 AND id < 5 FOR UPDATE;
COMMIT;

如果另一个事务插入的数据不在1到5之间，它就不会被锁定，也就不会等待。

表级锁（Table-Level Lock）

在某些情况下，MySQL 会自动对整张表进行锁定，这被称为表级锁。表级锁可以是共享锁或排他锁。

--共享锁（读锁）
LOCK TABLES table_name READ;
--排他锁（写锁）
LOCK TABLES table_name WRITE;
--释放锁
UNLOCK TABLE

示例一：共享锁（读锁）

第一个事务先加共享锁，然后去做查询和修改的操作。

#共享锁（读锁）
lock table product read;
#尝试查询当前表操作
select * from product;
#尝试修改当前表操作
update product set product_name='测试商品2' where id=6;
#尝试查询其他表操作
select * from user;
#尝试修改其它表操作
update user set age=1 where id=6;
#释放锁
UNLOCK table

然后第二个事务也进行查询和修改的操作。

#尝试查询当前表操作
select * from product
#尝试修改操作
update product set product_name='测试商品2' where id=6;
#尝试查询其他表操作
select * from user;

我们会发现第一个事务加锁后，只能查询当前表数据，其他无法操作，第二个事务可以查询和修改其他表数据，但是修改锁定表的时候会一直阻塞，等到释放锁才能执行，执行结果如下：
在这里插入图片描述

示例二：排他锁（写锁）

第一个事务先加排他锁，然后去做查询和修改的操作。

#共享锁（写锁）
lock table product write;
#尝试查询当前表操作
select * from product;
#尝试修改当前表操作
update product set product_name='测试商品2' where id=6;
#尝试查询其他表操作
select * from user;
#尝试修改其它表操作
update user set age=1 where id=6;
#释放锁
UNLOCK table

然后第二个事务也进行查询和修改的操作。

#尝试查询当前表操作
select * from product;
#尝试修改当前表操作
update product set product_name='测试商品2' where id=6;
#尝试查询其他表操作
select * from user;
#尝试修改其它表操作
update user set age=1 where id=6;

我们会发现第一个事务加锁后，所有操作都可以进行，但是查询当前表数据非常慢，第二个事务查询和修改锁定表数据，会一直阻塞，等到释放锁才能执行，执行结果如下：
在这里插入图片描述

意向锁（Intention Lock）是一种锁定机制，用于在表级别指示事务将要对表中的行进行何种类型的操作。意向锁分为两种类型：意向共享锁（Intention Shared Lock，IS锁）和意向排他锁（Intention Exclusive Lock，IX锁）。

意向共享锁（IS锁）：当一个事务在某一行上请求共享锁时，会在表级别设置意向共享锁，表示该事务有意向在表中的某些行上设置共享锁。其他事务可以继续请求共享锁，但不允许请求排他锁。
意向排他锁（IX锁）：当一个事务在某一行上请求排他锁时，会在表级别设置意向排他锁，表示该事务有意向在表中的某些行上设置排他锁。其他事务则不能再请求共享锁或排他锁。

快照读和当前读

快照读：在使用快照读的情况下，读取的数据并不是当前时刻的数据，而是根据查询时刻生成的一个数据快照，也就是历史数据。当多个事务同时对同一张表进行读操作时，快照读不会阻塞其他事务的写操作，因此可以提高并发性能。

不加锁的简单的SELECT 都属于快照读，如下所示：

SELECT * FROM user WHERE ...

当前读：在使用当前读的情况下，读取的数据是当前时刻的真实数据。当前读会对数据加锁，直到事务提交或回滚，才会释放锁定，因此会阻塞其他事务的写操作。当前读适合用于需要修改数据的操作，例如 INSERT、UPDATE 或 DELETE 等加锁操作。

加锁的情况下都属于当前读，如下所示：

#共享锁
SELECT * FROM user LOCK IN SHARE MODE;
#排他锁
SELECT * FROM user for update;
#新增、修改、删除
INSERT INTO user values(...);
UPDATE user SET column=value[...] WHERE ...;
DELETE FROM user WHERE ...

查看锁

前面讲了这么多锁，如何确认加锁是否成功，或者加的锁是什么类型？MySQL提供了多种方式可以来查询：

查看当前系统中哪些数据行被锁定

在 performance_schema 数据库中，data_locks 表存储了当前正在被锁定的数据行的信息，以及这些锁的类型、状态等信息。

select * from performance_schema.data_locks;

以下是该表中常见的字段属性值及其解释：

ENGINE_LOCK_ID：表示锁定的引擎锁ID，用于标识特定的锁。
ENGINE_TRANSACTION_ID：表示锁定的引擎事务ID，用于标识锁定所属的事务。
THREAD_ID：表示持有或等待锁的线程ID。
EVENT_ID：表示事件ID，用于标识触发器或者存储过程的ID。
OBJECT_SCHEMA：表示锁定对象所属的数据库名称。
OBJECT_NAME：表示锁定对象的名称，如表名、索引名等。
INDEX_NAME：表示锁定的索引名称。
LOCK_TYPE：表示锁的类型，如 TABLE、RECORD 等。
LOCK_MODE：表示锁的模式或类型，如 S（共享锁）、X（排他锁）等。
LOCK_STATUS：表示锁的状态，如 GRANTED（已授予）、PENDING（等待中）等。
LOCK_DATA：表示锁定的数据。

示例一

使用共享锁来查看对应的状态：

#加上共享锁
START transaction;
select * from `product` where id =1 lock in share mode;
COMMIT;

第一行，LOCK_TYPE是TABLE（表锁) ，LOCK_MODE是IS（意向共享锁）；第二行LOCK_TYPE是RECORD（行锁) ，LOCK_MODE是S（共享锁），LOCK_DATA表示锁住主键为1的数据，执行如图所示：

在这里插入图片描述

示例二

使用排他锁来查看对应的状态：

#加上排他锁
START transaction;
select * from `product` where id =1 for update;
COMMIT;

第一行，LOCK_TYPE是TABLE（表锁) ，LOCK_MODE是IX（意向排他锁）；第二行LOCK_TYPE是RECORD（行锁) ，LOCK_MODE是X（排他锁），LOCK_DATA表示锁住主键为1的数据，执行如图所示：

在这里插入图片描述

用于从MySQL数据库的information_schema数据库中的INNODB_TRX表中检索数据。

INNODB_TRX表是InnoDB存储引擎提供的一个信息表，用于存储当前活动的事务信息。通过查询这个表，可以获取当前正在执行的事务的相关信息，例如事务的ID、事务的开始时间、事务状态等等。

SELECT * FROM information_schema.INNODB_TRX;

执行结果如下：
在这里插入图片描述

用于显示与锁相关的各种状态信息

执行这个命令将返回一个结果集，其中包含了当前 MySQL 实例中各种锁相关的状态变量及其对应的值。

show status like '%lock%'

这些状态变量提供了关于锁的各种信息，例如当前锁的数量、锁等待的数量、锁的超时数量等等。通过查看这些状态变量，可以了解到当前系统中锁的使用情况，从而帮助诊断和优化数据库性能。
（1）Innodb_row_lock_current_waits：当前等待的InnoDB行锁数量。

（2）Table_locks_waited：请求表级锁时发生等待的次数。

（3）Table_locks_immediate：立即获取到的表级锁数量。

（4）Innodb_lock_wait_timeout： InnoDB锁等待超时的次数。

执行结果如下：
在这里插入图片描述

显示当前打开的表（也称为数据文件）中正在被使用的表的信息

这个命令常用于查看当前 MySQL 实例中有哪些表正在被活动的连接使用。每行结果代表一个正在被使用的表，包含了表的名称、数据库名称、表的类型、表的引擎等信息。

SHOW OPEN TABLES WHERE In_use > 0;

执行结果如下：
在这里插入图片描述

事务

MySQL 中的事务是一组SQL语句，它们作为一个单独的逻辑工作单元执行，要么全部执行成功，要么全部执行失败回滚

事务的四个特性

ACID 是数据库事务的四个关键属性，指的是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这些属性确保了数据库在并发环境下的可靠性和一致性。

原子性（Atomicity）：事务是一个不可分割的操作单位，要么全部执行成功，要么全部失败回滚。如果事务中的任何一步操作失败，则整个事务将回滚到初始状态，所有修改都被撤消，数据库不会受到部分执行的影响。
一致性（Consistency）：事务在执行前后保持数据库的一致性。比如：我给你转账100元，对应的我卡里就要扣100元，你卡里就要增加100元。
隔离性（Isolation）：多个事务可以并发执行，但每个事务的执行应该与其他事务隔离开来，互不干扰。隔离性保证了事务之间的数据独立性，避免了并发执行时可能出现的问题，如脏读、不可重复读和幻读等。
持久性（Durability）：一旦事务提交，其结果就应该永久保存在数据库中，即使在系统崩溃或故障的情况下也不会丢失。持久性保证了数据的持久存储，通过将事务日志写入磁盘或其他可靠的存储介质来实现。

事务的并发问题

在事务的操作过程中，会有一些并发问题：

脏读（Dirty Read）：

当一个事务读取了另一个事务未提交的数据时，发生了脏读。如果另一个事务最终回滚，读取的数据可能是无效的或错误的，导致数据的不一致性。

不可重复读（Non-repeatable Read）：

不可重复读指的是在同一个事务中，由于其他事务修改了数据并提交，导致前后两次读取同一行数据得到不同结果。这种情况下，第一次读取的数据在事务结束前已经发生了改变。

幻读（Phantom Read）：

幻读与不可重复读类似，不过它是由于其他事务插入或删除了符合查询条件的数据，导致同一个事务在不同时间点执行相同查询时，结果集数量不一致。这种问题通常在Repeatable Read隔离级别下出现。

丢失更新（Lost Update）：

多个事务同时读取同一行数据，并尝试修改它，但由于并发控制机制的缺失或错误使用，某些事务的更新可能会被覆盖或丢失，导致最终数据不一致。

死锁（Deadlock）：

死锁指的是两个或多个事务相互等待对方释放资源而无法继续执行的情况。例如，事务A持有资源X，等待获取资源Y；同时事务B持有资源Y，等待获取资源X，这时候就形成了死锁。

事务的隔离级别

MySQL 提供了几种事务隔离级别，可以通过设置 transaction_isolation 参数来控制。

//方式一
SHOW VARIABLES LIKE 'transaction_isolation';

如图所示
在这里插入图片描述

//方式二
SELECT @@transaction_isolation;

如图所示

在这里插入图片描述

我们可以通过命令来修改设置事务隔离级别：

SET SESSION transaction isolation LEVEL READ UNCOMMITTED;

如图所示
在这里插入图片描述

事务隔离级别定义了一个事务内部可以看见其他事务执行的效果的方式。以下是 MySQL 支持的四种事务隔离级别，按照从低到高的顺序排列：

读未提交 (READ UNCOMMITTED)：

最低的隔离级别。允许一个事务读取另一个事务尚未提交的变更。

可能会导致脏读（读取到未提交的数据）、不可重复读（同一查询在同一事务中返回不同的结果）和幻读（同一查询在同一事务中返回不同的行数）问题。

案例讲解，表结构如下：
在这里插入图片描述
先执行命令，将事务一的隔离级别设置为读未提交 (READ UNCOMMITTED)

-- 设置隔离级别
SET SESSION transaction isolation LEVEL READ UNCOMMITTED;
-- 查看隔离级别
SELECT @@transaction_isolation;

（1）下面介绍一下脏读的情况，在事务二（默认可重复读 (REPEATABLE READ)）里面先修改id=1的数据，不提交事务：

-- 事务二
START transaction;
-- SET SESSION transaction isolation LEVEL READ UNCOMMITTED;
-- SET SESSION transaction isolation LEVEL READ COMMITTED;
SET SESSION transaction isolation LEVEL REPEATABLE READ;
-- SET SESSION transaction isolation LEVEL SERIALIZABLE;
SELECT @@transaction_isolation;
UPDATE product SET number=490 WHERE id=1;
ROLLBACK;

当事务一去读取id=1的数据的时候，读取到了事务二未提交的数据

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL READ UNCOMMITTED;
SELECT @@transaction_isolation;
SELECT * from product where id = 1;
COMMIT;

执行结果如图：
在这里插入图片描述

经过测试事务二再四种事务隔离级别下进行修改操作，只要事务一的隔离级别是读未提交 (READ UNCOMMITTED)，那么他就可以提前读取其它事务的未提交的数据，造成数据的脏读。

（2）然后再讲解不可重复读的情况，当事务一先去读取id=1的数据，然后等事务二提交完成后再去查询id=1的数据：

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL READ UNCOMMITTED;
SELECT @@transaction_isolation;
SELECT * from product where id = 1;
COMMIT;

在事务二（默认可重复读 (REPEATABLE READ)）里面先修改id=1的数据，提交事务：

-- 事务二
START transaction;
UPDATE product SET number=490 WHERE id=1;
COMMIT;

执行结果如图：
在这里插入图片描述

事务一再第一次查询，然后事务二进行修改操作，事务一第二次查询和第一次查询的两次结果并不一致，造成数据的不可重复读。

（3）最后再来介绍下幻读的情况，当事务一先去查询number!=0的数据，然后等事务二提交完成后再去查询number!=0的数据：

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL READ UNCOMMITTED;
SELECT @@transaction_isolation;
SELECT * from product where number != 0;
COMMIT;

在事务二（默认可重复读 (REPEATABLE READ)）里面先新增一个产品，提交事务：

-- 事务二
START transaction;
INSERT INTO product VALUES(2,'test产品2',200);
COMMIT;

执行结果如图：
在这里插入图片描述
事务一第一次查询number!=0的数据，然后事务二进行新增或删除数据操作，事务一第二次查询返回的结果集数量与第一次不相同，造成数据的幻读。

读已提交 (READ COMMITTED)：

MySQL 默认的隔离级别。保证一个事务不会读取到另一个正在执行的事务未提交的数据。

可能会导致不可重复读和幻读问题。

案例讲解，表结构如下：
在这里插入图片描述
先执行命令，将事务一的隔离级别设置为读已提交 (READ COMMITTED)

-- 设置隔离级别
SET SESSION transaction isolation LEVEL READ COMMITTED;
-- 查看隔离级别
SELECT @@transaction_isolation;

（1）下面介绍一下脏读的情况，在事务二（默认可重复读 (REPEATABLE READ)）里面先修改id=1的数据，不提交事务：

-- 事务二
START transaction;
UPDATE product SET number=490 WHERE id=1;
COMMIT;

当事务一去读取id=1的数据的时候，未读取事务二未提交的数据

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL READ COMMITTED;
SELECT @@transaction_isolation;
SELECT * from product where id = 1;
COMMIT;

执行结果如图：

在这里插入图片描述
事务二先进行修改操作，不提交事务，事务一再去读取该条数据时，不能读取其它事务的未提交的数据，所以不会造成数据的脏读。

（2）然后再讲解不可重复读的情况，当事务一先去读取id=1的数据，然后等事务二提交完成后再去查询id=1的数据：

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL READ COMMITTED;
SELECT @@transaction_isolation;
SELECT * from product where id = 1;
COMMIT;

在事务二（默认可重复读 (REPEATABLE READ)）里面先修改id=1的数据，提交事务：

-- 事务二
START transaction;
UPDATE product SET number=490 WHERE id=1;
COMMIT;

执行结果如图：

在这里插入图片描述

事务一再第一次查询，然后事务二进行修改操作，事务一第二次查询和第一次查询的两次结果并不一致，造成数据的不可重复读。

（3）最后再来介绍下幻读的情况，当事务一先去查询number!=0的数据，然后等事务二提交完成后再去查询number!=0的数据：

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL READ UNCOMMITTED;
SELECT @@transaction_isolation;
SELECT * from product where number != 0;
COMMIT;

在事务二（默认可重复读 (REPEATABLE READ)）里面先新增一个产品，提交事务：

-- 事务二
START transaction;
INSERT INTO product VALUES(2,'test产品2',200);
COMMIT;

执行结果如图：
在这里插入图片描述

事务一第一次查询number!=0的数据，然后事务二进行新增或删除数据操作，事务一第二次查询返回的结果集数量与第一次不相同，造成数据的幻读。

可重复读 (REPEATABLE READ)：

默认的事务隔离级别，确保在同一个事务内多次读取同样的数据时，得到的结果是一致的。保证一个事务不会读取到另一个正在执行的事务已提交但未提交的变更。

可以防止不可重复读，但仍可能出现幻读问题。

案例讲解，表结构如下：
在这里插入图片描述
先执行命令，将事务一的隔离级别设置为可重复读 (REPEATABLE READ)

-- 设置隔离级别
SET SESSION transaction isolation LEVEL REPEATABLE READ;
-- 查看隔离级别
SELECT @@transaction_isolation;

（1）下面介绍一下脏读的情况，在事务二（默认可重复读 (REPEATABLE READ)）里面先修改id=1的数据，不提交事务：

-- 事务二
START transaction;
UPDATE product SET number=490 WHERE id=1;
COMMIT;

当事务一去读取id=1的数据的时候，未读取事务二未提交的数据

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL REPEATABLE READ;
SELECT @@transaction_isolation;
SELECT * from product where id = 1;
COMMIT;

执行结果如图：

在这里插入图片描述

事务二先进行修改操作，不提交事务，事务一再去读取该条数据时，不能读取其它事务的未提交的数据，所以不会造成数据的脏读。

（2）然后再讲解不可重复读的情况，当事务一先去读取id=1的数据，然后等事务二提交完成后再去查询id=1的数据：

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL REPEATABLE READ;
SELECT @@transaction_isolation;
SELECT * from product where id = 1;
COMMIT;

在事务二（默认可重复读 (REPEATABLE READ)）里面先修改id=1的数据，提交事务：

-- 事务二
START transaction;
UPDATE product SET number=490 WHERE id=1;
COMMIT;

执行结果如图：

在这里插入图片描述

事务一再第一次查询，然后事务二进行修改操作，事务一第二次查询和第一次查询的两次结果一致，不会造成数据的不可重复读。

（3）最后再来介绍下幻读的情况，当事务一先去查询number!=0的数据，然后等事务二提交完成后再去查询number!=0的数据：

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL REPEATABLE READ;
SELECT @@transaction_isolation;
SELECT * from product where number != 0;
COMMIT;

在事务二（默认可重复读 (REPEATABLE READ)）里面先新增一个产品，提交事务：

-- 事务二
START transaction;
INSERT INTO product VALUES(2,'test产品2',200);
COMMIT;

执行结果如图：

在这里插入图片描述

事务一第一次查询number!=0的数据，然后事务二进行新增或删除数据操作，事务一第二次查询返回的结果集数量与第一次相同，但是第三次查询的结果集数量不同，所以仍会有造成数据的幻读。

串行化 (SERIALIZABLE)：

最高的隔离级别。强制事务串行执行，避免了脏读、不可重复读和幻读问题。通过在读取的每一行数据上设置共享锁来实现。

案例讲解，表结构如下：
在这里插入图片描述
先执行命令，将事务一的隔离级别设置为串行化 (SERIALIZABLE)

-- 设置隔离级别
SET SESSION transaction isolation LEVEL SERIALIZABLE;
-- 查看隔离级别
SELECT @@transaction_isolation;

（1）下面介绍一下脏读的情况，在事务二（默认可重复读 (REPEATABLE READ)）里面先修改id=1的数据，不提交事务：

-- 事务二
START transaction;
UPDATE product SET number=490 WHERE id=1;
COMMIT;

当事务一去读取id=1的数据的时候，未读取事务二未提交的数据

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL SERIALIZABLE;
SELECT @@transaction_isolation;
SELECT * from product where id = 1;
COMMIT;

执行结果如图：

在这里插入图片描述

事务二先进行修改操作，不提交事务，事务一再去读取该条数据时，会一直等待事务二释放锁后，才会往下执行，所以只会读取最新的数据，不会造成数据的脏读。

（2）然后再讲解不可重复读的情况，当事务一先去读取id=1的数据，然后等事务二提交完成后再去查询id=1的数据：

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL SERIALIZABLE;
SELECT @@transaction_isolation;
SELECT * from product where id = 1;
COMMIT;

在事务二（默认可重复读 (REPEATABLE READ)）里面先修改id=1的数据，提交事务：

-- 事务二
START transaction;
UPDATE product SET number=490 WHERE id=1;
COMMIT;

执行结果如图：

在这里插入图片描述

事务一再第一次查询，会给该条数据加锁，然后事务二进行修改操作，会等待事务一释放锁后，才会往下执行，不会造成数据的不可重复读。

（3）最后再来介绍下幻读的情况，当事务一先去查询number!=0的数据，然后等事务二提交完成后再去查询number!=0的数据：

-- 事务一
START transaction;
SET SESSION transaction isolation LEVEL SERIALIZABLE;
SELECT @@transaction_isolation;
SELECT * from product where number != 0;
COMMIT;

在事务二（默认可重复读 (REPEATABLE READ)）里面先新增一个产品，提交事务：

-- 事务二
START transaction;
INSERT INTO product VALUES(2,'test产品2',200);
COMMIT;

执行结果如图：

在这里插入图片描述

事务一第一次查询number!=0的数据，会给该条数据加锁，然后事务二进行新增或删除数据操作，会等待事务一释放锁后，才会往下执行，也就不会造成数据的幻读。

MVCC（多版本并发控制）

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种数据库管理系统中常用的并发控制技术，用于在数据库系统中支持事务的并发执行，在无锁的情况下也能处理读写并发。

MVCC 的核心思想是为每个数据行维护多个版本，以便不同事务可以同时读取和修改数据而不会相互影响。MVCC 的实现方式是为每个事务创建一个快照（Snapshot），这个快照包含了在这个事务开始时已经存在的所有数据版本。当事务需要读取一个数据时，它会根据这个快照来获取对应的数据版本，而不会受到其他并发事务的影响。当事务需要修改一个数据时，它会在新的数据版本上进行操作，并将旧的版本保留在快照中，直到事务提交或回滚，才会真正地删除旧版本。这样，其他事务在读取数据时可以根据自己的事务时间点或快照来选择合适的数据版本，从而实现并发访问和事务隔离。

在 MySQL 中，MVCC 可以在 READ COMMITTED 和 REPEATABLE READ 这两个隔离级别下工作。其中，READ COMMITTED 级别下的 MVCC 实现方式是，对于每个事务，只能看到已经提交的数据版本，无法看到其他并发事务中未提交的数据修改。而 REPEATABLE READ 级别下的 MVCC 实现方式是，在事务开始时创建快照，并在整个事务期间都使用这个快照来获取数据。因此，REPEATABLE READ 级别下的事务可以看到整个事务期间内的数据版本，无论其他事务是否已经修改了这些数据。

需要注意的是，在 SERIALIZABLE 级别下，MySQL 会禁用 MVCC 机制，而是采用锁定机制来实现事务隔离，从而保证数据的一致性和隔离性。

在lnnoDB中，会对增删改操作自动添加排它锁，因此两个事务不会出现脏写的情况，也就是不会出现两个事务交叉着对同一条记录进行修改，必须等待第一个事务提交才能进行第二个事务。

实现原理

MVCC的实现原理主要是依赖三个隐藏字段、UndoLog回滚日志、Read View。

隐藏字段

（1）DB_TRX_ID：表示最后一次插入或更新该行数据的事务ID。

（2）DB_ROLL_PTR：回滚指针。指向UndoLog日志中某行记录的上一个版本。

（3）DB_ROW_ID：如果没有设置主键且没有唯一索引的情况下，会使用此ID生成聚簇索引。

（4）如果是DELETE操作，在内部视为更新操作，记录在头的delete_flag设置为已删除，并不代表真的删除。

以前面介绍的product表为例，加上隐藏字段如下：

在这里插入图片描述

UndoLog回滚日志

回滚日志主要分为两种：

（1）insert undo log：当INSERT操作时，产生UndoLog回滚日志，在事务回滚时被用到，如果不回滚在事务提交之后就会被删除。

（2）update undo log：当UPDATE和DELETE操作时，产生UndoLog回滚日志，不仅在事务回滚的时候需要，快照读的时候也是需要的，所以不会随便删除，只有不再用到这个日志的时候，才会被purge线程统一清除（DELETE操作也是只是打一个删除标记，并不真正的删除）

多个事务操作同一条记录的时候会生成一个UndoLog日志，这些日志通过回滚指针串联在一起，称为版本链，它的执行流程如下：

比如：事务一，向product表插入一条数据，DB_TRX_ID和DB_ROLL_PTR设为NULL

在这里插入图片描述
事务二，对该条数据的numer字段进行修改操作，隐藏字段的DB_TRX_ID递增，将原数据拷贝到UndoLog中，回滚指针指向UndoLog的副本记录

在这里插入图片描述

事务三，又对该数据的numer字段进行修改操作，隐藏字段的DB_TRX_ID递增，将原数据拷贝到UndoLog中，回滚指针指向UndoLog的副本记录，最新的UndoLog副本记录作为表头和历史的UndoLog副本记录进行关联。

在这里插入图片描述
不同事务或者相同事务的对同一记录的修改，会导致该记录的UndoLog成为一条记录版本线性表，既链表，UndoLog的链首就是最新的副本记录，链尾就是最老的副本记录。

Read View 读视图

Read View 是事务进行快照读操作的时候生产的读视图，最大的作用就是判断数据的可见性，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID，且值是递增的。

可见性判断有四个全局属性：

（1）m_ids属性：创建Read View时，正在活跃事务的ID列表。

（2）m_low_limit_id属性：创建Read View时，尚未分配给下一个事务的事务ID。

（3）m_up_limit_id属性：创建Read View时，正在活跃事务的ID列表中最小的事务ID。

（4）m_create_trx_id属性：创建Read View的事务ID。

Read View遵循一个可见性算法：

（1）首先比较DB_TRX_ID < m_up_limit_id，如果小于，说明该版本的事务再当前事务生成Read View前已被提交，则该版本可以被当前事务访问，否则进入下一个判断。

（2）如果DB_TRX_ID >= m_low_limit_id，如果大于等于，说明该版本的事务再当前事务生成Read View后才提交，则该版本不可以被当前事务访问，如果小于则进入下一个判断。

（3）判断DB_TRX_ID是否存在m_ids里，如果存在，说明再当前事务生成Read View时，该版本的事务还是活跃的，则该版本不可以被当前事务访问；如果不在，说明当前事务生成Read View前已被提交，则该版本可以被当前事务访问

下面我们来举例说明：

事务一	事务二
begin	begin
/	update/commit
select	/

假设：事务二再事务一快照读之前进行了修改并提交操作，当前活跃事务的ID列表为：1，分配给下一个事务的事务ID为：2+1=3，最小的事务ID为：1。

在这里插入图片描述
然后执行事务一，进入判断逻辑：

在这里插入图片描述

原文地址：https://blog.csdn.net/qq_39940674/article/details/139033092

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：Apache Drill 2万字面试题及参考答案
下一篇：学习springMVC

C# DataTable使用Linq查询详解
C# 可以对DataTable进行查询。在 .NET 框架中，DataTable类提供了几种方法来查询数据，包括Select方法和扩展方法（在命名空间中）。
阅读更多2024-11-13
Llama旋转位置编码代码实现及详解
中介绍了旋转位置编码（RoPE）的特点和优势，这种输入长度动态可变的优势使得在Llama编码时，不需要掩码将多余的嵌入掩住。为了详细了解RoPE是如何实现的，接下来我们使用代码一步一步的来亲自实现Ro
阅读更多2024-11-13
鸿蒙next版开发：ArkTS组件通用属性（文本通用）
通过本文的介绍，你应该对HarmonyOS 5.0中ArkTS组件的文本通用属性有了基本的了解。这些属性是UI开发中的重要工具，合理利用这些属性可以使你的应用界面更加美观和实用。希望本文能够帮助你在开
阅读更多2024-11-13
DAY25|回溯算法Part04|LeetCode:491.递增子序列、46.全排列、47.全排列 II
可以看出元素1在[1,2]中已经使用过了，但是在[2,1]中还要在使用一次1，所以处理排列问题就不用使用startIndex了。题目要求递增子序列大小至少为2，而且不会无限递归，可以不加终止条件，st
阅读更多2024-11-13
django博客项目实现站内搜索功能
django博客项目实现站内搜索功能
阅读更多2024-11-13
ElasticSearch
需要注意的是，最新的es到了8.x的版本，很多的api发生了变化，我所下载的是7.12.1的版本我们要安装的内容包含2部分::存储、搜索和运算kibana:图形化展示首先Elasticsearch不用
阅读更多2024-11-13
生活感悟 -- 从晨练开始，生活的正循环
在安静的办公环境中，没有了平日的喧嚣与干扰，我可以从容地规划当天的工作任务，也有余裕去学习新的知识点，甚至能腾出一些时间来整理思绪、记录灵感。古人常说“日出而作，日入而息”，这是一种顺应自然的生活方式
阅读更多2024-11-13
客户案例 | 如何利用Ansys工具提供互联系统（以及系统的系统），从而使“软件定义汽车”成为可能
这是一个很大的进步，这是通过可靠的仿真软件也就是Ansys，提供更好、更准确的理解来实现的。作为功率系统和物联网领域的市场领导者，英飞凌依靠Ansys的多物理场生态系统，对这些系统的开发复杂性进行分类
阅读更多2024-11-13
掌握Spring Boot事务管理，也就一杯咖啡的时间！
事务管理是确保数据安全和一致性的关键组成部分。Spring Boot提供了强大的事务管理功能，支持声明式事务管理和编程式事务管理两种方式。通过合理配置事务的传播行为、隔离级别、超时时间和回滚规则等属性
阅读更多2024-11-13
F litter 开发之flutter_local_notifications
使用之前一定要代码里边获取通知权限。
阅读更多2024-11-13

MySQL事务隔离

MySQL事务隔离

前言

锁

共享锁（Shared Lock）

排他锁（Exclusive Lock）

行级锁（Row-Level Lock）

表级锁（Table-Level Lock）

快照读和当前读

查看锁

事务

事务的四个特性

事务的并发问题

事务的隔离级别

MVCC（多版本并发控制）

实现原理

相关文章