MySQL 트랜잭션의 ACID 특성(상세 설명)

트랜잭션은 MySQL과 같은 관계형 데이터베이스와 NoSQL을 구별하는 중요한 측면이며, 데이터 일관성을 보장하는 중요한 수단입니다. 이 글에서는 먼저 트랜잭션과 관련된 MySQL 기본 개념을 소개한 다음 트랜잭션의 ACID 특징을 소개하고 구현 원리를 분석합니다.

1. 기본 개념

트랜잭션은 데이터베이스에 액세스하고 업데이트하는 프로그램 실행 단위로, 모두 실행되거나 실행되지 않습니다. 관계형 데이터베이스로서 MySQL은 트랜잭션을 지원합니다. 이 기사는 MySQL5.6을 기반으로 합니다.

먼저 MySQL 트랜잭션의 기본 사항을 검토하세요.

1. 논리적 아키텍처 및 스토리지 엔진

이미지 출처: https://blog.csdn.net/fuzhongmin05/article/details/70904190

위 그림과 같이 MySQL 서버는 논리적 아키텍처는 위에서 아래로 세 개의 레이어로 나눌 수 있습니다:

(1) 첫 번째 레이어: 클라이언트 연결, 권한 인증 등을 처리합니다.

(2) 두 번째 계층: 쿼리 문의 구문 분석, 최적화, 캐싱, 내장 함수, 저장 프로시저 등의 구현을 담당하는 서버 계층입니다.

(3) 세 번째 계층: MySQL에서 데이터 저장 및 검색을 담당하는 스토리지 엔진. MySQL의 서버 계층은 트랜잭션을 관리하지 않으며 트랜잭션은 스토리지 엔진에 의해 구현됩니다. MySQL의 트랜잭션을 지원하는 스토리지 엔진에는 InnoDB, NDB Cluster 등이 있으며 그중 InnoDB가 가장 널리 사용됩니다. MyIsam, Memory 등과 같은 다른 스토리지 엔진은 트랜잭션을 지원하지 않습니다.

별도의 언급이 없는 한, 다음 글에 설명된 내용은 InnoDB를 기준으로 작성되었습니다.

2. 제출 및 롤백

일반적인 MySQL 트랜잭션은 다음과 같이 작동합니다.

start transaction;
……  #一条或多条sql语句
commit;

여기서 start transaction은 트랜잭션의 시작을 식별하고, commit은 트랜잭션을 커밋하고, 실행 결과를 데이터베이스에 기록합니다. . SQL 문 실행에 문제가 있는 경우 성공적으로 실행된 모든 SQL 문을 롤백하기 위해 롤백(rollback)이 호출됩니다. 물론 트랜잭션에서 직접 롤백 문을 사용하여 롤백할 수도 있습니다.

Autocommit

MySQL은 아래와 같이 기본적으로 자동 커밋 모드를 사용합니다.

자동 커밋 모드에서 트랜잭션을 명시적으로 시작하는 시작 트랜잭션이 없으면 각 SQL 문은 트랜잭션으로 처리됩니다. 커밋 작업을 수행합니다.

다음과 같은 방법으로 자동 커밋을 끌 수 있습니다. 자동 커밋 매개변수는 연결에 따라 다르므로 한 연결에서 매개변수를 수정해도 다른 연결에는 영향을 미치지 않습니다.

자동 커밋이 꺼진 경우 커밋이나 롤백이 실행되고 트랜잭션이 종료되고 다른 트랜잭션이 시작될 때까지 모든 SQL 문은 하나의 트랜잭션에 있습니다.

특수 작업

MySQL에는 몇 가지 특수 명령이 있습니다. 트랜잭션에서 이러한 명령을 실행하면 DDL 문(테이블 생성/테이블 삭제/변경/테이블 생성)과 같이 커밋이 즉시 트랜잭션을 실행하게 됩니다. ), 잠금 테이블 문 등.

그러나 일반적으로 사용되는 선택, 삽입, 업데이트 및 삭제 명령은 트랜잭션을 강제로 제출하지 않습니다.

3. ACID 특성

ACID는 트랜잭션을 측정하는 네 가지 특성입니다.

• Atomicity(또는 불가분성)
• Consistency(일관성)
• Isolation(격리)
• Durability

엄격한 기준에 따르면 표준에서는 ACID 특성을 동시에 충족하는 트랜잭션만 트랜잭션으로 간주됩니다. 그러나 주요 데이터베이스 공급업체의 구현에서는 실제로 ACID를 충족하는 트랜잭션이 거의 없습니다. 예를 들어 MySQL의 NDB 클러스터 트랜잭션은 내구성과 격리를 충족하지 않습니다. InnoDB의 기본 트랜잭션 격리 수준은 격리를 충족하지 않는 반복 가능한 읽기입니다. Oracle의 기본 트랜잭션 격리 수준은 격리를 충족하지 않는 READ COMMITTED입니다. ACID는 거래가 충족해야 하는 조건이라기보다는 거래를 측정하는 4가지 차원입니다.

ACID의 특징과 그 구현 원리는 이해의 편의를 위해 아래에 자세히 소개되며, 소개 순서는 엄격하게 A-C-I-D가 아닙니다.

2. 원자성

1. 정의

원자는 트랜잭션이 모든 작업이 수행되거나 수행되지 않는 분할할 수 없는 작업 단위임을 의미합니다. SQL 문이 실패하면 실행된 문도 롤백해야 하며 데이터베이스는 트랜잭션 이전 상태로 돌아갑니다.

2. 구현 원칙: 로그 취소

원자성의 원리를 설명하기 전에 먼저 MySQL 트랜잭션 로그를 소개하겠습니다. MySQL 로그에는 바이너리 로그, 오류 로그, 쿼리 로그, 느린 쿼리 로그 등 다양한 유형이 있습니다. 또한 InnoDB 스토리지 엔진은 두 가지 유형의 트랜잭션 로그인 리두 로그(redo log)와 실행 취소 로그( 롤백 로그). 재실행 로그는 트랜잭션 내구성을 보장하는 데 사용됩니다. 실행 취소 로그는 트랜잭션 원자성과 격리의 기초입니다.

실행 취소 로그에 대해 이야기해 보겠습니다. 원자성을 달성하는 핵심은 트랜잭션이 롤백될 때 성공적으로 실행된 모든 SQL 문을 실행 취소할 수 있는 것입니다. InnoDB는 실행 취소 로그에 의존하여 롤백을 실현합니다: 트랜잭션이 데이터베이스를 수정하면 InnoDB는 해당 실행 취소 로그를 생성합니다. 트랜잭션 실행이 실패하거나 롤백이 호출되면 트랜잭션은 다음을 수행해야 합니다. 롤백하려면 실행 취소 로그의 정보를 사용하여 데이터를 수정 전의 상태로 롤백할 수 있습니다.

undo 로그는 SQL 실행과 관련된 정보를 기록하는 논리적 로그입니다. 롤백이 발생하면 InnoDB는 실행 취소 로그의 내용을 기반으로 이전 작업과 반대되는 작업을 수행합니다. 각 삽입에 대해 각 삭제에 대해 삭제가 실행되고 각 업데이트에 대해 삽입이 실행됩니다. , 롤백 중에 삭제가 실행됩니다. 롤링 시 데이터를 다시 변경하기 위해 역방향 업데이트가 수행됩니다.

업데이트 작업을 예로 들어 보겠습니다. 트랜잭션이 업데이트를 실행할 때 생성된 실행 취소 로그에는 수정된 행의 기본 키(수정된 행을 알기 위해), 수정된 열과 같은 정보가 포함됩니다. , 그리고 수정 전과 수정 후의 이러한 열의 값을 사용하면 롤백 시 이 정보를 사용하여 업데이트 전 상태로 데이터를 복원할 수 있습니다.

3. 내구성

1. 정의

지속성은 트랜잭션이 커밋되면 데이터베이스에 대한 변경 사항이 영구적이어야 함을 의미합니다. 후속 작업이나 실패는 이에 영향을 미치지 않습니다.

2. 구현 원칙: redo 로그

redo 로그와 undo 로그는 모두 InnoDB 트랜잭션 로그에 속합니다. 먼저 리두로그가 존재하게 된 배경에 대해 이야기해보자.

InnoDB는 MySQL의 스토리지 엔진으로 데이터가 디스크에 저장됩니다. 그러나 데이터를 읽고 쓸 때마다 디스크 IO가 필요하면 효율성이 매우 낮습니다. 이를 위해 InnoDB는 캐시(버퍼 풀)를 제공합니다. 버퍼 풀은 디스크의 일부 데이터 페이지 매핑을 포함하고 데이터베이스에 액세스하기 위한 버퍼 역할을 합니다. 데이터베이스에서 데이터를 읽을 때 먼저 읽습니다. Buffer Pool. Buffer Pool이 Pool에 없으면 데이터베이스에 데이터를 쓸 때 디스크에서 읽어서 Buffer Pool에 넣고, Buffer Pool에 먼저 쓰고 수정한다. 버퍼 풀의 데이터는 정기적으로 디스크에 새로 고쳐집니다(이 프로세스를 더티 플러싱이라고 함).

버퍼 풀을 사용하면 데이터 읽기 및 쓰기 효율성이 크게 향상되지만 새로운 문제도 발생합니다. MySQL이 다운되고 버퍼 풀의 수정된 데이터가 디스크에 플러시되지 않으면 데이터 손실이 발생합니다. 거래 내구성은 보장되지 않습니다.

그래서 이 문제를 해결하기 위해 리두 로그가 도입되었습니다. 데이터가 수정되면 버퍼 풀의 데이터를 수정하는 것 외에도 트랜잭션이 제출될 때 작업이 리두 로그인 fsync 인터페이스에 기록됩니다. 리두 로그 플레이트를 플러시하기 위해 호출됩니다. MySQL이 다운되면 redo 로그의 데이터를 읽고 다시 시작할 때 데이터베이스를 복원할 수 있습니다. Redo 로그는 WAL(Write-ahead Logging, Write-ahead Log)을 사용하여 모든 수정 사항을 먼저 로그에 기록한 후 Buffer Pool에 업데이트하므로 MySQL 다운타임으로 인해 데이터가 손실되지 않도록 하여 내구성을 충족합니다. 요구 사항.

리두 로그도 트랜잭션이 커밋될 때 디스크에 로그를 써야 하는데 왜 버퍼 풀의 수정된 데이터를 디스크에 직접 쓰는 것(즉, 더러워지는 것)보다 빠른가요? 그 이유는 크게 두 가지입니다.

(1) Dirty cleaning은 매번 수정되는 데이터 위치가 무작위이기 때문에 무작위 IO이지만, redo 로그를 작성하는 것은 추가 작업이며 순차 IO에 속합니다.

(2) 더티화 단위는 데이터 페이지(페이지)입니다. MySQL의 기본 페이지 크기는 16KB입니다. 페이지를 조금만 수정하면 전체 페이지를 작성해야 하지만 리두 로그에는 실제로 필요한 부분만 포함됩니다. , 유효하지 않은 IO가 크게 줄어듭니다.

3. redo 로그 및 binlog

MySQL에는 쓰기 작업을 기록하고 데이터 복구에 사용할 수 있는 binlog(바이너리 로그)도 있다는 것을 알고 있지만 두 로그는 근본적으로 다릅니다.

(1) 다양한 기능: Redo 로그는 MySQL 다운타임이 내구성에 영향을 미치지 않도록 보장하기 위해 사용되며, binlog는 서버가 시점에 따라 데이터를 복구할 수 있도록 보장하기 위해 사용됩니다. 마스터-슬레이브 복제에도 사용됩니다.

(2) 다양한 수준: redo 로그는 InnoDB 스토리지 엔진에 의해 구현되고, binlog는 MySQL 서버 계층에 의해 구현되며(문서 앞부분의 MySQL 논리 아키텍처 소개 참조) InnoDB와 기타를 모두 지원합니다. 스토리지 엔진.

(3) 다양한 내용: redo 로그는 물리적 로그이며 내용은 디스크 페이지를 기반으로 합니다. binlog의 내용은 바이너리이며 binlog_format 매개변수에 따라 SQL 문, 데이터 자체를 기반으로 할 수 있습니다. 또는 둘의 혼합.

(4) 작성 시점이 다릅니다. binlog는 트랜잭션이 커밋될 때 작성됩니다. 리두 로그 작성 시점은 상대적으로 다양합니다.

• 前面曾提到：当事务提交时会调用fsync对redo log进行刷盘；这是默认情况下的策略，修改innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证。
• 除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。

四、隔离性

1. 定义

与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。

 

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们仅考虑最简单的读操作和写操作(暂时不考虑带锁读等特殊操作)，那么隔离性的探讨，主要可以分为两个方面：

• (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
• (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

2. 锁机制

首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。

锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

行锁与表锁

按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

如何查看锁信息

有多种方法可以查看InnoDB中锁的情况，例如：

select * from information_schema.innodb_locks; #锁的概况
show engine innodb status; #InnoDB整体状态，其中包括锁的情况

下面来看一个例子：

#在事务A中执行：
start transaction;
update account SET balance = 1000 where id = 1;
#在事务B中执行：
start transaction;
update account SET balance = 2000 where id = 1;

此时查看锁的情况：

show engine innodb status查看锁相关的部分：

通过上述命令可以查看事务24052和24053占用锁的情况；其中lock_type为RECORD，代表锁为行锁(记录锁)；lock_mode为X，代表排它锁(写锁)。

除了排它锁(写锁)之外，MySQL中还有共享锁(读锁)的概念。由于本文重点是MySQL事务的实现原理，因此对锁的介绍到此为止，后续会专门写文章分析MySQL中不同锁的区别、使用场景等，欢迎关注。

介绍完写操作之间的相互影响，下面讨论写操作对读操作的影响。

3. 脏读、不可重复读和幻读

首先来看并发情况下，读操作可能存在的三类问题：

（1）脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。举例如下（以账户余额表为例）：

（2）不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。举例如下：

（3）幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。举例如下：

4. 트랜잭션 격리 수준

SQL 표준에는 4가지 격리 수준이 정의되어 있으며 각 격리 수준이 지정되어 있습니다. 확인 위의 문제가 존재하는지 여부. 일반적으로 격리 수준이 낮을수록 시스템 오버헤드가 낮아지고 지원 가능한 동시성은 높아지지만 격리는 더욱 악화됩니다. 격리 수준과 읽기 문제의 관계는 다음과 같습니다.

실제 응용 프로그램에서는 read uncommitted가 동시성을 발생시킵니다. 문제가 많지만 다른 격리 수준에 비해 성능 향상이 매우 제한적이어서 덜 사용됩니다. 직렬화 가능트랜잭션을 직렬화하도록 하면 동시성 효율성이 매우 낮고 데이터 일관성 요구 사항이 매우 높고 동시성이 허용되지 않는 경우에만 사용할 수 있으므로 거의 사용되지 않습니다. 따라서 대부분의 데이터베이스 시스템에서 기본 격리 수준은 Read Committed(such as Oracle) 또는 입니다. 반복적으로(이하 RR) .

다음 두 명령을 통해 이 세션의 전역 격리 수준과 격리 수준을 볼 수 있습니다.

#🎜 🎜 #

InnoDB의 기본 격리 수준은 RR입니다. 다음 문서에서는 RR에 중점을 둡니다. SQL 표준에서 RR은 팬텀 읽기 문제를 피할 수 없지만 InnoDB에서 구현한 RR은 팬텀 읽기 문제를 방지한다는 점에 유의해야 합니다. MVCC MVCC란 무엇입니까? MVCC의 전체 이름은 다중 버전 동시성 제어 프로토콜인 다중 버전 동시성 제어입니다. 다음 예는 MVCC의 특성을 잘 반영합니다. 동시에 서로 다른 트랜잭션에서 읽는 데이터는 다를 수 있습니다(즉, 여러 버전). T5 시점에 트랜잭션 A와 트랜잭션 C는 서로 다른 버전의 데이터를 읽을 수 있습니다.

MVCC의 가장 큰 장점은 읽기가 잠겨 있지 않아 읽기와 쓰기에 충돌이 없고 동시성 성능이 좋다는 점입니다. InnoDB는 MVCC를 구현하며 주로 숨겨진 데이터 열(마크 비트라고도 함)과 실행 취소 로그에 의존하여 여러 버전의 데이터가 공존할 수 있습니다. 데이터의 숨겨진 열에는 데이터 행의 버전 번호, 삭제 시간, 실행 취소 로그에 대한 포인터 등이 포함됩니다. MySQL은 데이터를 읽을 때 숨겨진 열을 사용하여 롤백이 필요한지 여부를 찾을 수 있습니다. 롤백에 필요한 실행 취소 로그. 따라서 MVCC는 숨겨진 열의 세부 형식이 더 이상 구현되지 않습니다. 다음은 위에서 언급한 여러 문제를 바탕으로 별도로 설명하겠습니다.

(1) Dirty read

트랜잭션 A가 T3 시간 노드에서 zhangsan의 잔액을 읽으면 데이터가 발견됩니다. 다른 트랜잭션에 의해 수정되었으며 상태는 커밋되지 않았습니다. 이때, 트랜잭션 A는 최신 데이터를 읽은 후 해당 데이터의 Undo 로그를 기반으로 롤백 작업을 수행하고 트랜잭션 B가 수정되기 전의 데이터를 획득함으로써 더티 읽기를 방지합니다.

(2) 반복 불가능 읽기

트랜잭션 A가 T2 노드에서 처음으로 데이터를 읽을 때, 버전 번호가 1이라고 가정할 때 데이터의 버전 번호(데이터의 버전 번호는 행 단위로 기록됩니다), 트랜잭션 B가 커밋되면 이 행에 기록되는 버전 번호는 버전 번호가 2라고 가정하여 증가합니다. ; 트랜잭션 A가 T5에 있는 경우 데이터를 다시 읽을 때 데이터의 버전 번호(2)가 처음 읽을 때 기록된 버전 번호(1)보다 높은 것으로 확인되므로 롤백됩니다. 버전 번호가 1일 때 데이터를 얻기 위해 Undo 로그를 기반으로 작업을 수행하므로 반복 읽기가 가능합니다.

(3) 팬텀 리딩

InnoDB에서 구현한 RR은 다음 키 잠금 메커니즘을 통해 팬텀 리딩 현상을 방지합니다.

next-key lock

은 행 잠금의 한 유형이며 구현은 레코드 잠금(

record lock)과 동일합니다. ) + 갭 잠금(

gap lock)

의 특징은 기록 자체를 잠그는 것 뿐만 아니라(기록 잠금 기능) )# 🎜🎜#, 범위도 잠급니다(갭 잠금 기능). 물론, 여기서 말하는 것은 읽기 잠금 해제입니다. 이때 다음 키 잠금은 실제로 잠긴 것이 아니라 읽은 데이터에 표시를 추가할 뿐입니다(표시 내용에는 데이터의 버전 번호가 포함됩니다). 등), 정확성을 위해 이를 다음 키 잠금 메커니즘이라고 부르겠습니다. 이전 예를 사용하여 설명하겠습니다. 트랜잭션 A가 T2 노드에서 처음으로 06. 요약

요약하자면, InnoDB가 구현한 RR은 잠금 메커니즘, 숨겨진 데이터 열, 실행 취소 로그 및 다음 키 잠금을 통해 어느 정도 격리를 달성하며 이는 대부분의 시나리오 요구 사항을 충족할 수 있습니다. 그러나 RR은 팬텀 읽기 문제를 방지하지만 결국 직렬화 가능하지 않으며 완전한 격리를 보장할 수 없다는 점에 유의해야 합니다. 다음은 직접 확인할 수 있는 예입니다.

5. 일관성

1. 기본 개념

일관성이란 트랜잭션 실행이 완료된 후 데이터베이스의 무결성 제약 조건이 트랜잭션 전후 모두 파괴되지 않음을 의미합니다. 거래 실행 법적 데이터 상태. 데이터베이스의 무결성 제약 조건에는 엔터티 무결성(예: 행의 기본 키가 존재하고 고유함), 열 무결성(예: 필드의 유형, 크기 및 길이가 요구 사항을 충족해야 함)이 포함되지만 이에 국한되지는 않습니다. , 외래 키 제약 조건 및 사용자 정의 무결성(예: 이체 전후에 두 계정의 잔액 합계가 변경되지 않고 유지되어야 함)

2. 구현

일관성은 트랜잭션이 추구하는 궁극적인 목표라고 할 수 있습니다. 위에서 언급한 원자성, 지속성 및 격리성은 모두 데이터베이스 상태의 일관성을 보장하기 위한 것입니다. 또한 일관성을 구현하려면 데이터베이스 수준의 보장 외에도 애플리케이션 수준의 보장도 필요합니다.

일관성을 달성하기 위한 조치는 다음과 같습니다.

• 원자성, 내구성 및 격리를 보장합니다. 이러한 특성을 보장할 수 없으면 트랜잭션의 일관성을 보장할 수 없습니다.
• 데이터베이스 자체는 문자 삽입을 허용하지 않는 등의 보장을 제공합니다. 정수 열 문자열 값과 문자열 길이는 열 제한 등을 초과할 수 없습니다.
• 응용 프로그램 수준에서 보장합니다. 예를 들어 이체 작업에서 송금인의 잔액만 차감되고 수신자의 잔액은 늘어나지 않는 경우 데이터베이스가 얼마나 완벽하게 구현되었는지 상태는 보장할 수 없습니다. 일관성

6. 요약

다음은 ACID 특성과 구현 원칙을 요약합니다.

• 원자성: 명령문이 완전히 실행되거나 실행되지 않습니다. 이는 트랜잭션의 핵심 기능이며 트랜잭션 자체는 원자성에 의해 정의됩니다. 구현은 주로 실행 취소 로그를 기반으로 합니다

• 지속성: 가동 중지 시간 및 기타 이유로 인해 데이터가 손실되지 않도록 합니다. 트랜잭션 제출 후 구현은 주로 리두 로그를 기반으로 합니다

• 격리: 트랜잭션이 최대한 많이 실행되도록 보장합니다. InnoDB의 기본 격리 수준은 RR입니다. 메커니즘, 숨겨진 데이터 열, 실행 취소 로그 및 다음 키 잠금 메커니즘 일관성: 트랜잭션이 추구하는 궁극적인 목표, 일관성 구현에는 데이터베이스 수준 보호와 애플리케이션 수준 보호가 모두 필요합니다

• 권장 학습:
MySQL 튜토리얼

위 내용은 MySQL 트랜잭션의 ACID 특성(상세 설명)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!