MySQL의 트랜잭션 격리에 대해 이야기해 보겠습니다.-MySQL 튜토리얼-php.cn

이 기사에서는 MySQL의 트랜잭션 격리를 이해하고 트랜잭션의 특성, 격리 수준, 트랜잭션 시작 방법 등을 소개합니다. 모두에게 도움이 되기를 바랍니다!

MySQL의 트랜잭션 격리에 대해 이야기해 보겠습니다.

트랜잭션은 일련의 데이터베이스 작업이 모두 성공하거나 모두 실패하도록 보장하는 것입니다. MySQL에서는 트랜잭션 지원이 엔진 계층에서 구현되지만 모든 엔진이 트랜잭션을 지원하는 것은 아닙니다. 예를 들어 MySQL의 기본 MyISAM 엔진은 트랜잭션을 지원하지 않습니다. [관련 권장 사항: mysql 튜토리얼(동영상)]

1. 트랜잭션의 특성

원자성: 트랜잭션의 모든 작업은 완전히 완료되거나 완료되지 않으며 중간 링크 어디에서 끝나지 않습니다. 트랜잭션 실행 중 오류가 발생하면 트랜잭션이 한 번도 실행되지 않은 것처럼 트랜잭션이 시작되기 전 상태로 롤백됩니다. 일관성: 트랜잭션 시작 전과 트랜잭션 종료 후 데이터베이스의 무결성.
더티 읽기: 트랜잭션 B가 트랜잭션 A의 커밋되지 않은 데이터를 읽습니다.
비반복 읽기: 한 트랜잭션이 다른 트랜잭션에 제출된 업데이트 데이터를 읽습니다.

팬텀 읽기/가상 읽기: 트랜잭션 다른 트랜잭션에 제출된 삽입 데이터를 읽습니다.

2. 트랜잭션의 격리 수준에는 커밋되지 않은 읽기, 커밋된 읽기, 반복 가능한 읽기 및 직렬화가 포함됩니다.

커밋되지 않은 읽기: 트랜잭션이 아직 제출되지 않은 경우 변경 사항은 다른 트랜잭션에서 볼 수 있습니다.

커밋 읽기 : 트랜잭션이 제출된 후 변경 사항은 다른 트랜잭션에서 볼 수 있습니다(더티 읽기 해결, Oracle의 기본 격리 수준)
반복 읽기 : 트랜잭션 실행 중에 표시되는 데이터는 항상 트랜잭션 실행 시 표시되는 데이터와 일치합니다. 트랜잭션이 시작되고 커밋되지 않은 변경 사항도 다른 트랜잭션에 표시되지 않습니다(더티 읽기 및 반복 불가능한 읽기를 해결하기 위해 MySQL 기본 격리 수준)
직렬화: 동일한 레코드 행에 대해 쓰기는 쓰기 잠금을 추가하고 읽기는 읽기-쓰기 잠금 충돌이 발생하면 나중에 액세스되는 트랜잭션은 계속 실행되기 전에 이전 트랜잭션이 완료될 때까지 기다려야 합니다. (더티 읽기, 반복 불가능한 읽기 및 팬텀 읽기에 대한 솔루션)

보안은 순서대로 제출되고 성능은 순서대로 저하됩니다

create table T(c int) engine=InnoDB;
insert into T(c) values(1);

로그인 후 복사

격리 수준이 읽기-커밋인 경우 V1은 1이고 V2는 2입니다. 트랜잭션 B의 업데이트는 커밋된 후에만 A에서만 볼 수 있습니다. V3도 2

격리 수준이 반복 읽기이면 V1과 V2는 1이고 V3은 2입니다. V2가 1인 이유는 트랜잭션 실행 중에 보이는 데이터가 일관성이 있어야 하기 때문이다. 격리 수준이 직렬화인 경우 V1과 V2의 값은 1, V3의 값은 2이다. 데이터베이스 내부에 뷰가 생성되고 액세스 시 뷰의 논리적 결과가 우선 적용됩니다. 반복 읽기 격리 수준에서 이 뷰는 트랜잭션이 시작될 때 생성되고 트랜잭션 전체에서 사용됩니다. 읽기-커밋 격리 수준에서 이 뷰는 각 SQL 문의 시작 부분에 생성됩니다. 읽기 커밋되지 않은 격리 수준에서는 뷰 개념 없이 레코드의 최신 값을 직접 반환하고, 직렬화된 격리 수준에서는 병렬 액세스를 방지하기 위해 잠금을 직접 사용합니다.

3. 트랜잭션 격리 구현(반복 가능) 예시로 읽기)

MySQL의 트랜잭션 격리에 대해 이야기해 보겠습니다.

MySQL에서는 각 레코드가 업데이트될 때 롤백 작업도 기록합니다. 레코드의 최신 값은 이전 상태의 값을 롤백하여 얻을 수 있습니다. 값이 1에서 2, 3, 4로 순서대로 변경되었다고 가정하면 롤백 로그에는 다음과 유사한 레코드가 있을 것입니다.

4를 5로 변경하는 또 다른 트랜잭션이 있어도 이 트랜잭션은 읽기 뷰와 동일합니다. A, B, C에 해당하는 거래는 충돌하지 않습니다

系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除

四、事务启动的方式

MySQL的事务启动方式有以下几种：

显示启动事务语句，begin或start transaction。提交语句是commit，回滚语句是rollback
set autocommit=0，这个命令将这个线程的自动提交关掉。意味着如果只执行一个select语句，这个事务就启动了，而且不会自动提交事务。这个事务持续存在直到主动执行commit或rollback语句，或者断开连接

建议使用set autocommit=1，通过显示语句的方式来启动事务

可以在information_schema库中的innodb_trx这个表中查询长事务，如下语句查询持续时间超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

로그인 후 복사

五、事务隔离还是不隔离

下面是一个只有两行的表的初始化语句：

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

로그인 후 복사

事务A、B、C的执行流程如下，采用可重复读隔离级别
MySQL의 트랜잭션 격리에 대해 이야기해 보겠습니다.

begin/start transaction命令：不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动，一致性视图是在执行第一个快照读语句时创建的

start transaction with consistent snapshot命令：马上启动一个事务，一致性视图是在执行这条命令时创建的

按照上图的流程执行，事务B查到的k的值是3，而事务A查到的k的值是1

1、快照在MVCC里是怎么工作的？

在可重复读隔离级别下，事务启动的时候拍了个快照。这个快照是基于整个库的，那么这个快照是如何实现的？

InnoDB里面每个事务有一个唯一的事务ID，叫做transaction id。它在事务开始的时候向InnoDB的事务系统申请，是按申请顺序严格递增的

每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记作row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本，每个版本有自己的row trx_id

下图是一个记录被多个事务连续更新后的状态：

MySQL의 트랜잭션 격리에 대해 이야기해 보겠습니다.

语句更新生成的undo log（回滚日志）就是上图中的是哪个虚线箭头，而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来的

按照可重复读的定义，一个事务启动的时候，能够看到所以已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前在启动了但还没提交的所有事务ID。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位就组成了当前事务的一致性视图。而数据的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的

这个视图数组把所有的row trx_id分成了几种不同的情况

MySQL의 트랜잭션 격리에 대해 이야기해 보겠습니다.

对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：

1）如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的

2）如果落在红色部分，表示这个版本是由将来启动的事务生成的，肯定不可见

3）如果落在黄色部分，那就包括两种情况

若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见
若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见

InnoDB利用了所有数据都有多个版本的这个特性，实现了秒级创建快照的能力

2、为什么事务A的查询语句返回的结果是k=1？

假设：

1.事务A开始时，系统里面只有一个活跃事务ID是99

2.事务A、B、C的版本号分别是100、101、102

3.三个事务开始前，(1,1)这一行数据的row trx_id是90

이렇게 하면 트랜잭션 A의 배열은 [99,100], 트랜잭션 B의 뷰 배열은 [99,100,101], 트랜잭션 C의 뷰 배열은 [99,100,101,102]

MySQL의 트랜잭션 격리에 대해 이야기해 보겠습니다.

위 그림에서 볼 수 있듯이 , 첫 번째 유효 업데이트는 트랜잭션 C이며 데이터가 (1,1)에서 (1,2)로 변경되었습니다. 이때 이 데이터의 최신 버전의 trx_id 행은 102이고, 버전 90이 과거 버전이 되었습니다

두 번째 유효 업데이트는 트랜잭션 B로, 데이터를 (1,2)에서 (1,3)으로 변경합니다. ) . 이때 이 데이터의 최신 버전은 101이며, 102가 과거 버전이 되었습니다. 트랜잭션 A가 쿼리할 때 트랜잭션 B는 아직 제출되지 않았지만 생성된 버전(1,3)이 현재 버전이 되었습니다. . 하지만 이 버전은 트랜잭션 A에 표시되지 않아야 합니다. 그렇지 않으면 더티 읽기가 됩니다. 이제 트랜잭션 A는 데이터를 읽으려고 하며 해당 뷰 배열은 [99,100]입니다. 데이터 읽기는 현재 버전부터 시작됩니다. 따라서 트랜잭션 A 쿼리문의 데이터 읽기 과정은 다음과 같다.

더 찾아보니 결국 ( 1,1), 저수위보다 작은 행 trx_id=90이 녹색 영역에 있음을 알 수 있습니다. 트랜잭션 A 쿼리에서는 이 데이터 행을 본 결과가 일관적이라고 합니다. 트랜잭션 보기의 경우 자체 업데이트가 항상 표시되는 것 외에도 다음과 같은 세 가지 상황이 있습니다.
버전 은(는) 제출되지 않았으며, visible
버전이 제출되었지만 뷰가 생성된 후에 제출되었으며, visible

버전이 제출되었으며, 뷰가 생성되기 전에 제출되었음을 알 수 있습니다. 트랜잭션 A가 시작될 때 트랜잭션 A의 쿼리문이 생성되었습니다. 이때

(1,2)이 제출되었지만 케이스 2에 속하는 뷰 배열이 생성된 후에 제출됨, visible
(1,1)이 뷰 배열이 생성되기 전에 제출됨, visible

트랜잭션 B가 데이터를 업데이트하려고 하면 더 이상 과거 버전을 업데이트할 수 없습니다. 그렇지 않으면 트랜잭션 C의 업데이트가 손실됩니다. 따라서 이때 트랜잭션 B의 집합 k=k+1은 (1,2)
에 기초한 연산입니다. 업데이트된 데이터를 먼저 읽고 쓴 후 이 읽기는 현재 값만 읽을 수 있습니다. 읽다. update 문과 더불어 select 문이 잠겨 있는 경우에도 현재 읽기입니다. 트랜잭션 C가 즉시 제출되지 않고 다음 트랜잭션 C'가 된다고 가정하면 어떻게 될까요?