인터뷰어: MySQL은 ACID를 어떻게 구현합니까?-Java인터뷰 질문들-php.cn

면접에서 면접관은 MySQL의 ACID에 대해서만 질문하면 즉시 8부작 에세이를 암송할 수 있습니다(아직 답변하지 못하는 사람도 있을 수 있습니다). 더욱 역겨운 점은 일부 면접관이 루틴을 따르지 않고 계속 질문한다는 것입니다. MySQL은 ACID를 어떻게 구현합니까?

솔직히 이 질문은 95%의 사람들을 설득할 수 있습니다.

오늘 이 글에서는 주로 MySQL InnoDB 엔진 하의 ACID 구현 원리에 대해 논의합니다. 트랜잭션이 무엇인지, 격리 수준이 무엇인지와 같은 기본 지식에 대해서는 자세히 설명하지 않습니다.

ACID

관계형 데이터베이스로서 MySQL은 가장 일반적인 InnoDB 엔진 측면에서 ACID를 어떻게 보장합니까?

(Atomicity)원자성:트랜잭션은 가장 작은 실행 단위이며 분할을 허용하지 않습니다. 원자성은 작업이 완전히 완료되거나 전혀 영향을 미치지 않도록 보장합니다.
(일관성) 일관성: 트랜잭션 실행 전후에 데이터가 일관되게 유지됩니다.
(격리) 격리: 데이터베이스를 동시에 사용하면 하나의 트랜잭션이 다른 트랜잭션의 방해를 받지 않습니다.
(내구성) 내구성: 트랜잭션이 커밋된 후. 데이터베이스에 오류가 발생하더라도 데이터베이스의 데이터 변경 사항은 지속됩니다.

Isolation

먼저 격리에 대해 이야기해 보겠습니다. 첫 번째는 4가지 격리 수준입니다.

격리 수준 제출 후 변경 사항은 다른 거래에서 볼 수 있습니다

반복 읽기	트랜잭션에서 다른 트랜잭션이 데이터에 대해 작동하는지 여부와 트랜잭션이 커밋되는지 여부에 관계없이 동일한 데이터를 읽은 결과는 항상 동일합니다. InnoDB 기본 레벨.
직렬화	트랜잭션은 순차적으로 실행됩니다. 각 읽기는 테이블 수준 공유 잠금을 획득해야 합니다. 읽기와 쓰기는 서로를 차단하며 시스템 동시성을 희생합니다.

다양한 격리 수준은 다양한 문제를 해결하기 위한 것입니다. 즉, 더티 읽기(dirty read), 팬텀 읽기(phantom read), 반복 불가능 읽기가 해당됩니다.

반복읽기아니요 등장허용 출연 불가출연 가능

隔离级别	脏读	不可重复读	幻读ㅋㅋㅋ

직렬화	허용되지 않음	허용되지 않음	허용되지 않음

그렇다면 서로 다른 격리 수준은 어떻게 이루어지며, 서로 다른 것들이 서로 간섭하지 않는 이유는 무엇일까요? 대답은 Locks 및 MVCC입니다.

Locks

먼저 MySQL에는 몇 개의 잠금이 있는지 이야기해 보겠습니다.

Granularity

세분성이란 테이블 잠금, 페이지 잠금, 행 잠금을 의미합니다. 테이블 잠금에는 의도적인 공유 잠금, 의도적인 배타적 잠금, 자체 증가 잠금 등이 포함됩니다. 행 잠금은 엔진 수준에서 각 엔진에 의해 구현됩니다. 그러나 모든 엔진이 행 잠금을 지원하는 것은 아닙니다. 예를 들어 MyISAM 엔진은 행 잠금을 지원하지 않습니다.

행 잠금 유형

InnoDB 트랜잭션에서 행 잠금은 인덱스의 인덱스 항목을 잠그는 방식으로 구현됩니다. 즉, InnoDB는 인덱스 조건을 통해 데이터를 검색할 때만 행 수준 잠금을 사용하고, 그렇지 않으면 테이블 잠금을 사용합니다. 행 수준 잠금도 공유 잠금과 배타 잠금의 두 가지 유형으로 나뉘며, 잠금 전에 획득해야 하는 의도 공유 잠금과 의도 배타 잠금도 있습니다.

공유 잠금: 읽기 잠금, 다른 트랜잭션은 S 잠금 추가가 허용되며, 다른 트랜잭션은 X 잠금 추가가 허용되지 않습니다. 즉, 다른 트랜잭션은 읽기만 가능하고 쓸 수는 없습니다. 선택...공유 모드에서 잠금 잠금. select...lock in share mode 加锁。
排它锁：写锁，不允许其他事务再加S锁或者X锁。insert、update、delete、for update

독점 잠금: 쓰기 잠금, 다른 트랜잭션에서는 S 잠금 또는 X 잠금을 추가할 수 없습니다. 삽입, 업데이트, 삭제, 업데이트잠금.

행 잠금은 필요할 때

추가되지만 더 이상 필요하지 않을 때 즉시 해제되지 않고 거래가 끝날 때까지 해제되지 않습니다. 이는 2단계 잠금 프로토콜입니다. 🎜🎜

행 잠금 구현 알고리즘

레코드 잠금

단일 행 레코드에 대한 잠금은 항상 인덱스 레코드를 잠급니다.

Gap Lock

Gap Lock, 팬텀 읽기의 이유를 생각해 보세요. 사실 행 잠금은 행만 잠글 수 있지만 새 레코드를 삽입할 때 업데이트해야 할 것은 레코드 사이의 "간격"입니다. 팬텀 리딩 문제를 해결하려면 간격 잠금 기능을 추가하세요.

Next-Key Lock

Gap Lock + Record Lock, 열린 상태 및 닫힌 상태 유지.

자물쇠 분리

하부 자물쇠에 대한 일반적인 소개를 보실 수 있습니다. 잠금을 사용하면 트랜잭션이 데이터를 쓸 때 다른 트랜잭션이 쓰기 잠금을 얻을 수 없고 데이터를 쓸 수 없습니다. 이렇게 하면 트랜잭션 간 격리가 어느 정도 보장됩니다. 그런데 앞서 언급한 것처럼 쓰기 잠금을 추가하면 왜 다른 트랜잭션도 데이터를 읽을 수 있는 걸까요? 읽기 잠금을 얻을 수 없는 것 아닌가요?

MVCC

앞서 언급했듯이 잠금을 사용하면 현재 트랜잭션은 쓰기 잠금 없이 데이터를 수정할 수 없지만 읽을 때는 다른 트랜잭션에 의해 데이터 행이 수정되어 제출된 경우에도 여전히 읽을 수 있습니다. 동일한 행은 여전히 반복해서 읽을 수 있습니다. 이것이 바로 MVCC, 다중 버전 동시성 제어, 다중 버전 동시성 제어입니다.

버전 체인

몇 가지 추가 필드가 포함된 Innodb의 행 레코드 저장 형식: DATA_TRX_ID 및 DATA_ROLL_PTR.

DATA_TRX_ID: 데이터 행 버전 번호. 최근에 이 행의 레코드를 수정한 거래 ID를 식별하는 데 사용됩니다.

DATA_ROLL_PTR: 행의 롤백 세그먼트에 대한 포인터입니다. 이 줄에 기록된 모든 이전 버전은 undo log에 연결 목록 형식으로 구성됩니다.

undo 로그: 데이터가 수정되기 전의 로그를 기록합니다. 자세한 내용은 나중에 설명하겠습니다.

ReadView

은 각 SQL의 시작 부분에 생성되며 몇 가지 중요한 속성이 있습니다.

trx_ids: 현재 시스템의 활성(커밋되지 않은) 트랜잭션 버전 번호 모음입니다.

low_limit_id: 현재 읽기 보기를 생성할 때 "현재 시스템의 최대 트랜잭션 버전 번호+1"입니다.

up_limit_id:현재 읽기 보기가 생성되면 "시스템은 활성 트랜잭션최소 버전 번호"

creator_trx_id:현재 읽기 보기가 생성될 때의 트랜잭션 버전 번호

쿼리 시작

이제 쿼리를 시작하면 선택 항목이 나오고 데이터 행이 발견됩니다.

DATA_TRX_ID
DATA_TRX_ID >= low_limit_id:

현재 읽기 뷰가 생성된 후 데이터가 생성되며, 해당 데이터가 표시되지 않음을 나타냅니다.

표시되지 않으면 어떻게 해야 하나요? DATA_ROLL_PTR에 따라 실행 취소 로그에서 이전 버전을 찾으세요.

up_limit_id <low_limit_id : 격리 수준에 따라 다릅니다.

RR 수준 팬텀 읽기

잠금 및 MVCC를 사용하면 트랜잭션 격리가 해결됩니다. 여기에서 확장해 보겠습니다. 기본 RR 수준이 팬텀 판독을 해결합니까? 가상 읽기는 일반적으로 INSERT 및 반복 불가능한 대상 UPDATE를 대상으로 합니다.

thing 1 thing 2

begin begin

select * from dept

- 부서(이름) 값에 삽입("A")

- commit

update dept set name="B"

commit

우리는

id name 1 A 2 B
로그인 후 복사
라고 예상했지만 실제로는

id name 1 B 2 B
로그인 후 복사
로 밝혀졌습니다. 실제로 MySQL 반복 읽기의 격리 수준은 팬텀 읽기 문제를 완전히 해결하지는 않지만, 데이터를 읽을 때 팬텀 읽기 문제를 해결합니다. 수정 작업에는 여전히 팬텀 읽기 문제가 있습니다. 이는 MVCC가 팬텀 읽기를 완전히 해결하지 못한다는 것을 의미합니다.

원자성

원자성에 대해 이야기해 봅시다. 앞서 언급했듯이 실행 취소 로그는 로그를 롤백합니다. 격리 MVCC는 원자성과 마찬가지로 실제로 이를 달성하기 위해 의존합니다. 원자성을 달성하는 핵심은 트랜잭션이 롤백될 때 성공적으로 실행된 모든 SQL 문을 실행 취소할 수 있는 것입니다.

트랜잭션이 데이터베이스를 수정하면 InnoDB는 해당 실행 취소 로그를 생성합니다. 트랜잭션 실행이 실패하거나 롤백이 호출되어 트랜잭션이 롤백되는 경우 실행 취소 로그의 정보를 사용하여 데이터를 롤백할 수 있습니다. 수정 전의 모습입니다. Undo 로그는 SQL 실행과 관련된 정보를 기록하는 논리적 로그이다. 롤백이 발생하면 InnoDB는 실행 취소 로그의 내용을 기반으로 이전 작업과 반대되는 작업을 수행합니다.

각 삽입에 대해 롤백 시 삭제가 실행됩니다.

각 삭제에 대해 롤백 시 삽입이 실행됩니다.

각 업데이트에 대해 롤백 시 반대 업데이트가 실행됩니다. 데이터를 다시.

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

Persistence

Innnnodb에는 많은 로그가 있으며 지속성은 다시 실행 로그에 의존합니다.

SQL 업데이트 문을 실행하는 방법

지속성은 확실히 쓰기와 관련이 있습니다. MySQL에서 자주 언급되는 WAL 기술의 전체 이름은 Write-Ahead Logging입니다. 그 핵심은 로그를 먼저 작성한 다음 기록하는 것입니다. 디스크. 작은 가게에서 장사를 하는 것처럼 핑크색 판과 장부가 있어서 손님이 오면 먼저 핑크색 판에 글을 쓰고, 바쁘지 않을 때는 장부를 적는다.

redo log

redo 로그는 이 분홍색 보드입니다. 레코드를 업데이트해야 할 때 InnoDB 엔진은 먼저 해당 레코드를 리두 로그에 기록하고(그리고 메모리를 업데이트합니다) 업데이트가 완료됩니다. 적절한 시기에 이 작업 기록이 디스크에 업데이트되며, 이 업데이트는 가게 주인이 문을 닫은 후 하는 것과 마찬가지로 시스템이 상대적으로 유휴 상태일 때 수행되는 경우가 많습니다.

redo 로그에는 두 가지 기능이 있습니다:

고정 크기, 주기적 쓰기

crash-safe

리두 로그에는 커밋과 준비의 두 단계가 있습니다. "2단계 커밋"을 사용하지 않으면 데이터베이스 상태입니다. 로그에서 복구된 라이브러리의 상태가 일치하지 않습니다. 먼저 여기로 가서 다른 것을 살펴보겠습니다.

버퍼 풀

InnoDB는 캐시도 제공합니다. 버퍼 풀에는 데이터베이스 액세스를 위한 버퍼 역할을 하는 디스크의 일부 데이터 페이지 매핑이 포함되어 있습니다.

데이터를 읽을 때는 먼저 버퍼 풀에서 읽습니다. 버퍼 풀에 없으면 디스크에서 읽어 버퍼 풀에 넣습니다.

데이터를 데이터베이스에 쓸 때 , 먼저 작성됩니다. 버퍼 풀에 들어가면 버퍼 풀에 있는 수정된 데이터가 정기적으로 디스크에 새로 고쳐집니다.

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

그래서 Redo Log에 가입하게 되었습니다. 데이터가 수정되면 버퍼 풀의 데이터가 수정되는 것 외에도 작업이 리두 로그에도 기록됩니다.

트랜잭션이 제출되면 fsync 인터페이스가 호출되어 리두 로그를 플러시합니다.

MySQL이 다운되면 redo 로그의 데이터를 읽고 재시작 시 데이터베이스를 복원할 수 있습니다.

redo 로그는 WAL(미리 쓰기 로깅, 미리 쓰기 로그)을 사용합니다. 모든 수정 사항은 먼저 로그에 기록된 다음 버퍼 풀에 업데이트되므로 MySQL 가동 중지 시간으로 인해 데이터가 손실되지 않으므로 내구성이 충족됩니다. 필요하다. 이렇게 하면 두 가지 이점이 있습니다.

더티 페이지 플러시는 무작위 IO이고, 리두 로그 순차 IO

더티 페이지 플러시는 페이지를 기반으로 하며, 페이지의 전체 수정 페이지를 작성해야 하지만 리두 로그에는 실제로 필요한 잘못된 IO만 포함됩니다. 적어야 합니다.

binlog

이렇게 말하면 쓰기 작업에도 사용되며 데이터 복구에도 사용되는 bin 로그가 있다는 것이 궁금할 것입니다.

레벨: redo 로그는 innoDB 엔진에 고유하며 서버 계층을 binlog(아카이브 로그)라고 합니다

Content: redolog는 "특정 데이터 페이지에서 어떤 수정이 이루어졌는지" 기록하는 물리적 로그입니다. binlog는 논리적입니다. Log는 "ID=2인 행의 c 필드에 1을 추가합니다"와 같은 문의 원래 논리입니다. 쓰기: redolog는 루프로 작성되며 쓰기 기회가 많으며 binlog가 추가됩니다. 트랜잭션이 커밋될 때 기록됩니다

binlog 및 redo log
문용

실행자는 먼저 라인 ID=2를 얻기 위해 엔진을 찾습니다. ID는 기본키이며 트리 검색을 통해 직접 찾을 수 있습니다. ID = 2인 행이 있는 데이터 페이지가 메모리에 있으면 실행기로 직접 반환됩니다. 그렇지 않으면 먼저 디스크에서 메모리로 읽어온 다음 반환해야 합니다.

실행자는 엔진에서 제공한 행 데이터를 가져오고 이 값에 1, N+1을 추가하여 새 데이터 행을 가져온 다음 엔진 인터페이스를 호출하여 이 새 데이터 행을 작성합니다.

엔진은 이 새로운 데이터 행을 메모리에 업데이트하고 업데이트 작업을 리두 로그에 기록합니다. 이때 리두 로그는 준비 상태입니다. 그런 다음 실행이 완료되었으며 언제든지 트랜잭션을 제출할 수 있음을 실행자에게 알립니다.

실행자는 이 작업의 binlog를 생성하고 binlog를 디스크에 기록합니다.

Executor는 엔진의 커밋 트랜잭션 인터페이스를 호출하고, 엔진은 방금 작성된 Redo 로그를 커밋 상태로 변경하고 업데이트가 완료됩니다.

왜 Redo 로그를 먼저 작성하나요?

첫 번째 다시 실행 후 bin: binlog가 손실되고 업데이트 하나가 누락되었으며 복원 후에도 여전히 0입니다.

첫 번째 bin 후 다시 실행: 트랜잭션이 하나 더 있고 복구 후 1입니다.

Consistency

일관성은 트랜잭션이 추구하는 궁극적인 목표입니다. 이전 질문에서 언급한 원자성, 지속성 및 격리성은 실제로 데이터베이스 상태의 일관성을 보장하기 위한 것입니다. 물론 위의 내용은 모두 데이터베이스 수준에서 보장되는 것이며 일관성을 구현하려면 애플리케이션 수준에서도 보장이 필요합니다.

즉, 예를 들어 귀하의 비즈니스에서 구매 작업은 사용자의 잔고만 차감하고 재고를 줄이지 않는 상태를 유지하는 것은 절대 불가능합니다.

요약

우리 모두는 MySQL에 익숙하고 ACID가 무엇인지도 알고 있습니다. 그런데 MySQL의 ACID는 어떻게 구현되나요?

때로는 Undo 로그와 Redo 로그가 있다는 것을 알지만 왜 그것이 있는지 모를 때도 있습니다. 디자인의 목적을 알면 더 명확해질 것입니다.