MySQL事务隔离

MySQL事务隔离

  • 前言
    • 锁
      • 共享锁（Shared Lock）
      • 排他锁（Exclusive Lock）
      • 行级锁（Row-Level Lock）
      • 表级锁（Table-Level Lock）
      • 快照读和当前读
      • 查看锁
    • 事务
      • 事务的四个特性
      • 事务的并发问题
      • 事务的隔离级别
    • MVCC（多版本并发控制）
      • 实现原理

前言

在 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;

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

  1. ENGINE_LOCK_ID：表示锁定的引擎锁ID，用于标识特定的锁。
  2. ENGINE_TRANSACTION_ID：表示锁定的引擎事务ID，用于标识锁定所属的事务。
  3. THREAD_ID：表示持有或等待锁的线程ID。
  4. EVENT_ID：表示事件ID，用于标识触发器或者存储过程的ID。
  5. OBJECT_SCHEMA：表示锁定对象所属的数据库名称。
  6. OBJECT_NAME：表示锁定对象的名称，如表名、索引名等。
  7. INDEX_NAME：表示锁定的索引名称。
  8. LOCK_TYPE：表示锁的类型，如 TABLE、RECORD 等。
  9. LOCK_MODE：表示锁的模式或类型，如 S（共享锁）、X（排他锁）等。
  10. LOCK_STATUS：表示锁的状态，如 GRANTED（已授予）、PENDING（等待中）等。
  11. 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）：一旦事务提交，其结果就应该永久保存在数据库中，即使在系统崩溃或故障的情况下也不会丢失。持久性保证了数据的持久存储，通过将事务日志写入磁盘或其他可靠的存储介质来实现。

事务的并发问题

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

  1. 脏读（Dirty Read）：

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

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

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

  1. 幻读（Phantom Read）：

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

  1. 丢失更新（Lost Update）：

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

  1. 死锁（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 支持的四种事务隔离级别，按照从低到高的顺序排列：

  1. 读未提交 (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的数据，然后事务二进行新增或删除数据操作，事务一第二次查询返回的结果集数量与第一次不相同，造成数据的幻读。

  1. 读已提交 (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的数据，然后事务二进行新增或删除数据操作，事务一第二次查询返回的结果集数量与第一次不相同，造成数据的幻读。

  1. 可重复读 (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的数据，然后事务二进行新增或删除数据操作，事务一第二次查询返回的结果集数量与第一次相同，但是第三次查询的结果集数量不同，所以仍会有造成数据的幻读。

  1. 串行化 (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前已被提交，则该版本可以被当前事务访问

下面我们来举例说明：

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

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

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

在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/782628.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Chrome 127内置AI大模型攻略

Chrome 127内置AI大模型攻略

Chrome 127 集成Gemini：本地AI功能 Google将Gemini大模型整合进Chrome浏览器，带来全新免费的本地AI体验： 完全免费、无限制使用支持离线运行，摆脱网络依赖功能涵盖图像识别、自然语言处理、智能推荐等中国大陆需要借助魔法，懂都懂。 安装部署步骤： 1. Chrome V127 dev …
阅读更多...
golang验证Etherscan上的智能合约

golang验证Etherscan上的智能合约

文章目录 golang验证Etherscan上的智能合约为什么要验证智能合约如何使用golang去验证合约获取EtherscanAPI密钥Verify Source Code接口Check Source Code Verification Status接口演示示例及注意事项网络问题无法调用Etherscan接口&#xff08;最重要的步骤&#xff09; golan…
阅读更多...
YoloV9改进策略：Block改进|轻量实时的重参数结构|最新改进|即插即用（全网首发）

YoloV9改进策略：Block改进|轻量实时的重参数结构|最新改进|即插即用（全网首发）

摘要 本文使用重参数的Block替换YoloV9中的RepNBottleneck&#xff0c;GFLOPs从239降到了227&#xff1b;同时&#xff0c;map50从0.989涨到了0.99&#xff08;重参数后的结果&#xff09;。 改进方法简单&#xff0c;只做简单的替换就行&#xff0c;即插即用&#xff0c;非常…
阅读更多...
保健品商城小程序模板源码

保健品商城小程序模板源码

保健品商城小程序模板源码 简洁通用的保健品&#xff0c;健康生活&#xff0c;零售商品&#xff0c;电子商务微信小程序前端模板下载。包含&#xff1a;主页、购物车、客服、个人中心、我的订单、商品详情、我的钱包、设置等等。 保健品商城小程序模板源码
阅读更多...
程序员如何做好需求判断？

程序员如何做好需求判断？

1. 导语 本文作为2024上半年核心思考之二。 通过他人经验传导、个人实践、广泛阅读书籍(方法论类、企业经营类、传记类、财务类&#xff0c;具体书单附文末)&#xff0c;学会基于更高阶的经营者视角来做好业务需求判断。本文思路如下&#xff1a; 首先&#xff0c;抛一个灵魂问…
阅读更多...
【server】springboot 整合 redis

【server】springboot 整合 redis

1、redis 使用模式 1.1 单机模式 1.1.1 编译安装方式 1.1.1.1 下载 Redis的安装非常简单&#xff0c;到Redis的官网&#xff08;Downloads - Redis&#xff09;&#xff0c;下载对应的版本&#xff0c;简单几个命令安装即可。 1.1.1.2 编译安装 tar xzf redis-stable.tar.…
阅读更多...
IDEA 开发工具

IDEA 开发工具

IDEA 开发工具 IDEA软件激活新建项目新建project 运行调试 IDEA软件激活 访问激活码网进入带*的域名下载并解压左上角的zip包先执行sh uninstall.sh&#xff0c;再执行sh install.sh在带*的网页中复制并使用激活码code 新建项目 新建project file》New〉Project》New Proje…
阅读更多...
【测试】系统压力测试报告模板（Word原件）

【测试】系统压力测试报告模板（Word原件）

系统压力测试&#xff0c;简而言之&#xff0c;是在模拟高负载、高并发的环境下&#xff0c;对系统进行全面测试的过程。它旨在评估系统在面对极端使用条件时的性能表现&#xff0c;包括处理能力、响应时间、资源消耗及稳定性等关键指标。通过压力测试&#xff0c;开发团队能够…
阅读更多...
MySQL之备份与恢复和MySQL用户工具(一)

MySQL之备份与恢复和MySQL用户工具(一)

备份与恢复 备份脚本化 为备份写一些脚本是标准做法。展示一个示例程序&#xff0c;其中必定有很多辅助内容&#xff0c;这只会增加篇幅&#xff0c;在这里我们更愿意列举一些典型的备份脚本功能&#xff0c;展示一些Perl脚本的代码片段。你可以把这些当作可重用的代码块&…
阅读更多...
Python酷库之旅-第三方库Pandas(009)

Python酷库之旅-第三方库Pandas(009)

目录 一、用法精讲 19、pandas.read_xml函数 19-1、语法 19-2、参数 19-3、功能 19-4、返回值 19-5、说明 19-6、用法 19-6-1、数据准备 19-6-2、代码示例 19-6-3、结果输出 20、pandas.DataFrame.to_xml函数 20-1、语法 20-2、参数 20-3、功能 20-4、返回值 …
阅读更多...
【国产开源可视化引擎Meta2d.js】网格

【国产开源可视化引擎Meta2d.js】网格

画布背景网格 在线体验&#xff1a; 乐吾乐2D可视化 示例&#xff1a; // 设置默认缺省网格属性 meta2d.store.options.grid true; // 开启 meta2d.store.options.gridColor eeeeee; // 网格线条颜色 meta2d.store.options.gridSize 10; // 格子大小// 设置单个图纸的网格…
阅读更多...
Golang | Leetcode Golang题解之第222题完全二叉树的节点个数

Golang | Leetcode Golang题解之第222题完全二叉树的节点个数

题目&#xff1a; 题解&#xff1a; func countNodes(root *TreeNode) int {if root nil {return 0}level : 0for node : root; node.Left ! nil; node node.Left {level}return sort.Search(1<<(level1), func(k int) bool {if k < 1<<level {return false}…
阅读更多...
【ETABS】【RHINO】案例：Swallow to ETABS

【ETABS】【RHINO】案例：Swallow to ETABS

文章目录 01. Swallow Overview总览1 LOAD&#xff1a;Defination of LoadCase、Response Combo2 SectionArea Section and Area Load&#xff08;面截面定义与指定&#xff0c;面荷载指定&#xff09;Frame Section with rebarattr and linear load&#xff08;带钢筋属性框架…
阅读更多...
flutter：监听路由的变化

flutter：监听路由的变化

问题 当从路由B页面返回路由A页面后&#xff0c;A页面需要进行数据刷新。因此需要监听路由变化 解决 使用RouteObserver进行录音监听 创建全局变量&#xff0c;不在任何类中 final RouteObserver<PageRoute> routeObserver RouteObserver<PageRoute>();在mai…
阅读更多...
Hi3861 OpenHarmony嵌入式应用入门--UDP Server

Hi3861 OpenHarmony嵌入式应用入门--UDP Server

本篇使用的是lwip编写udp服务端。需要提前准备好一个PARAM_HOTSPOT_SSID宏定义的热点&#xff0c;并且密码为PARAM_HOTSPOT_PSK。 修改网络参数 在Hi3861开发板上运行上述四个测试程序之前&#xff0c;需要根据你的无线路由、Linux系统IP修改 net_params.h文件的相关代码&…
阅读更多...
基于轨迹信息的图像近距离可行驶区域方案验证

基于轨迹信息的图像近距离可行驶区域方案验证

一 图像可行驶区域方案 1.1 标定场景 1.2 标定步骤 设计一定间距标定场&#xff0c;在标定场固定位置设置摄像头标定标识点。主车开到标定场固定位置录制主车在该位置各个摄像头数据&#xff0c;通过摄像头捕获图像获取图像上关键点坐标pts-2d基于标定场设计&#xff0c;计算…
阅读更多...
Python | Leetcode Python题解之第222题完全二叉树的节点个数

Python | Leetcode Python题解之第222题完全二叉树的节点个数

题目&#xff1a; 题解&#xff1a; # Definition for a binary tree node. # class TreeNode: # def __init__(self, val0, leftNone, rightNone): # self.val val # self.left left # self.right right class Solution:def countNodes(self,…
阅读更多...
基于字典学习的地震数据降噪（MATLAB R2021B）

基于字典学习的地震数据降噪（MATLAB R2021B）

稀疏表示基于研究者们提出了许多变换基函数的方法逐渐成型&#xff0c;比如小波域&#xff0c;曲波域&#xff0c;dreamlet 域等&#xff0c;其原理是利用地震信号在变换域内的稀疏性和可分离性以去除噪声。继 Donoho发表非线性去噪方法-小波阈值萎缩方法&#xff0c;在后续的研…
阅读更多...
汉中茗茶小程序的设计

汉中茗茶小程序的设计

管理员账户功能包括&#xff1a;系统首页&#xff0c;个人中心&#xff0c;管理员管理&#xff0c;基础数据管理&#xff0c;茶叶管理&#xff0c;论坛管理&#xff0c;公告管理&#xff0c;茗茶历史管理 微信端账号功能包括&#xff1a;系统首页&#xff0c;茗茶信息&#xf…
阅读更多...
阶段三：项目开发---搭建项目前后端系统基础架构:任务9：导入空管基础数据

阶段三：项目开发---搭建项目前后端系统基础架构:任务9：导入空管基础数据

任务描述 本阶段任务是导入项目的基础数据&#xff0c;包括空管基础数据和离线的实时飞行数据&#xff08;已经脱敏&#xff09;。 任务指导 本阶段任务需要导入两种数据&#xff1a; 1、在MySQL中导入空管基础数据 kongguan.sql空管基础数据表说明&#xff1a; 1告警信息…
阅读更多...
最新文章

Copyright @ 2022~2023