Review 2. 5_트랜잭션과 잠금 (5.3, 5.4)

[DaehunGwak]

진도

5.3 InnoDB 스토리지 엔진 잠금 5.4 MySQL의 격리 수준

일시

• 2022.06.19.(일) 23:00

[minkukjo]

InnoDB의 락

• InnoDB는 레코드 락 기반의 잠금을 사용한다.
• 특이하게 Lock 사이에 Gap Lock이라는걸 건다.

Gap Lock이 필요한 이유?

• 갭락을 왜 거는지 궁금해졌다.
• 책에는 넥스트 키락과 관련이 있다고 하는데 이해가 잘 안가서 따로 찾아보았다.
• 갭락은 실제 레코드에 락을 거는게 아닌, 범위를 지정하기 위해 인덱스 레코드 사이에 범위(Gap) 락을 거는 방식이다.
• SELECT id FROM t WHERE id BETWEEN 10 and 20 FOR UPDATE 다음과 같은 쿼리가 있을 때, 10과 20사이에 Exclusive Lock이 걸리게 되고 만약 이 때 다른 트랜잭션에서 id 15를 insert 하려고 할 때 해당 트랜잭션은 대기상태가 된다.
• 그래서인지 이걸로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 꽤 자주 발생한다고 한다. ( 데드락의 원흉 )

Auto Increment Lock

• InnoDB 스토리지 엔진에서 특정 칼럼이 Auto Increment 속성인 경우 자동 증가를 위해 사용하는 테이블 수준의 락이다.

MySQL 격리 수준

• 면접에 자주 나오기도 하고 까먹지 않을겸 적어봄

Read Uncommitted

• 가장 무식한 방식.
• 잠금을 아예 안한다.
• A라는 트랜잭션이 Insert를 수행하면 이 트랜잭션이 끝나기도 전에 다른 트랜잭션이 A의 결과를 받아볼 수 있게 되는 Dirty Read 현상이 발생함.

Read Committed

• 앞서 배웠던 MVCC에서 undo log에 대한 내용이 나왔었다.
• 이 Read Committed에서는 변경전 데이터를 언두 로그에 백업해두고 다른 트랜잭션의 요청이 오면 언두 영역에 있는 데이터를 보여줌으로써 Dirty Read 문제를 해결한다.
• 그런데 만약 A라는 트랜잭션이 실행된 이후 B라는 트랜잭션에서 특정 레코드의 값을 변경한 후 커밋하면 A 트랜잭션에서 B 트랜잭션의 변경 값을 받아옴으로써 Repeatable Read 정합성이 깨지게 된다.
• 이를 Non-Repeatable Read라고 부른다.

Repeatable Read

• MySQL의 기본 격리 수준.
• 앞서 발생하는 문제를 트랜잭션 id를 통해 먼저 들어온 트랜잭션은 이후 들어오는 트랜잭션의 변경 사항 따위 궁금해하지 않는다는 원칙을 갖고 전개해나간다.
• 그러나 이 역시도 여전히 다른 트랜잭션에서 쓰기를 실행하는 경우 레코드가 보였다 안보였다 하는 Phantom Read (또는 Phantom Row` 현상이 발생하게 된다.

Serializable

• 모든 격리 수준의 최고봉.
• 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.
• 당연히 Phantom Read 현상도 발생하지 않는다.
• 그러나 InnoDB에서는 갭락과 넥스트 키 락 덕분에 Repeatable Read에서도 Phantom Read가 발생하지 않아서 굳이 사용할 필요는 없다.
• 물론 select for update 같은 쿼리의 경우 repeatable read 격리 수준에서도 팬텀 리드 현상이 발생할 수는 있지만, 레코드의 변경 이력에 잠금을 걸 수는 없으므로, 이는 예외적인 쿼리로 보는게 맞다.

갭락, 넥스트키락과 데드락

• 결국 갭락, 넥스트키락으로 인해 데드락이 걸릴 수 있다는 생각이 들었다.
• 그리고 인덱스 기반의 잠금 방식 역시 범위에 해당하는 인덱스들을 모두 잠그게 되므로 이 역시도 잠재적인 데드락을 유발할 수 있는 원인이 될 수 있을 것 같다.
• 그러니 최대한 쿼리를 짤 때는 이 쿼리가 최소한의 인덱스 레코드 잠금을 하는지를 면밀히 살펴보는게 좋을 듯 하다.

[tmxkqotnl]

갭 락? 넥스트 키 락?

갭락

• 갭 락은 보조 인덱스에 대해서 보조 인덱스가 가질 수 있는 범위에 대한 락을 건다.
• 관련 레코드들 사이, 그리고 전후(위아래)의 레코드 사이에도 락을 건다.

넥스트 키 락

• 레코드 락 + 갭 락

REPEATABLE READ 격리수준에서

• 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)을 가진다.

  • 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션 번호가 포함돼 있다.

• 언두 영역의 백업된 데이터는 스토리지 엔진이 필요한 시점까지만 가지고 있다가 주기적으로 삭제한다.

• repeatable read 격리 수준에서는 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.

  • MVCC를 보장하기 위함

• (한 트랜잭션 내에서) 다른 트랜잭션에서 수행한 작업에 의해서 레코드가 보였다가 안보였다가 하는 현상을 phantom read(row)라고 한다.

Binary Log?

• DDL, DML 등의 작업을 수행할 때, 그 이벤트를 기록하는 이진 파일
  • 조회 작업은 제외
• 바이너리 로그를 이용해
  • 마스터-슬레이브 간의 레플리케이션을 수행함
  • 특정 시점으로 복구시킴
• MySQL에서는 총 세 가지의 작성 포멧을 제공한다.
  • STATEMENT 방식은 가장 전통적인 방식으로 실행한 SQL을 그대로 바이너리 로그에 작성함
  • Row-based 방식은 행 기반의 방식으로 마스터 서버는 개별 테이블 행이 어떻게 영향을 받는지에 대한 이벤트를 로그에 작성함
  • 결과값을 저장함
  • Mix-logging 방식은 전통적인 방식을 기본으로 하되, 특정 경우에 행 기반의 방식으로 변경됨

[Edwin222]

락과 인덱스

• 레코드 락은 레코드 자체가 아니라 레코드의 인덱스를 잠근다.

• 반대로 말하면 인덱스가 없을 경우 풀 스캔을 하게 되고 풀 스캔시 모든 레코드를 전부 잠그게 된다는 것이다.

• 가령 특정 레코드를 update하기 위해서 where로 검색하는 조건에 인덱스가 없는 경우 테이블의 모든 레코드를 다 잠그게 된다.

• where some_col="red" and other_col="blue"이고 some_col만 인덱스가 있다면 두 조건 모두를 만족하는 칼럼의 갯수와 상관없이, some_col == "red"를 만족하는 모든 레코드를 잠그게 된다.

접근 단계에 따른 오작동 증상

DIRTY READ

• 어떤 트랜잭션이 완료되지 않은 상태인데도 다른 트랜잭션에서 해당 트랜잭션에 의한 변경을 관측할 수 있는 경우이다.

• DIRTY한 값 (IO가 일어나지 않아 더티 페이지에 있고 정합성이 맞지 않을 데이터)을 읽는다고 해서 더티 리드 인듯.

• 더티 리드가 일어나면 당연히 데이터가 있었는데요 없었습니다 하게 된다. 트랜잭션의 결과가 다른 트랜잭션의 영향을 받게 되어 고립성이 깨짐.

• 원리상 READ UNCOMMITED 접근 수준에서 발생.

NON REPEATABLE READ

• 하나의 트랜잭션에서 똑같은 SELECT 쿼리 문을 실행했는데 그 결과가 다른 경우이다.

  • 동일한 row인데 그 내용물의 값이 다른 경우를 일컫는다.
  • update에 의해 발생한다.

• 동일한 동작의 읽기를 여러번 했는데 그 결과가 모두 다를 수 있어, 반복된 읽기에서 정합성을 유지하는게 불가능하므로 논-리피터블이다.

• 가령 트랜잭션 초기에 A라는 테이블에 데이터 조회를 하고 나서, 다른 일 하다가, 아직 트랜잭션을 닫지 않고 다시 동일하게 데이터 조회를 한다고 하자.

  • 여기서 다른 사람이 A 테이블에 모종의 변경을 가했다면, 아까전과는 다른 조회 결과를 얻게 될 수 있다.
  • 내가 트랜잭션을 닫진 않았지만 다른 사람의 트랜잭션에 의해서 테이블 내용이 바뀌었기 때문이다.

• READ COMMITED에서 발생한다.

  • 누가 커밋하면 내가 보던 테이블이 바뀌기 때문이다.

• UNCOMMITED에서도 물론 발생한다.

  • 여긴 커밋도 안했는데 테이블이 바뀌므로 당연히 발생한다.

PHANTOM READ

• 하나의 트랜잭션에서 똑같은 SELECT 쿼리 문을 실행했는데 그 결과가 다른 경우이다.

  • 있던 row가 없어지거나, 없던 row가 생기는 경우이다.
  • insert / delete에 의해 발생한다.

• 저자는 SELECT ~~FOR UPDATE는 언두 레코드에 쓰기 잠금을 걸 수 없다는 무슨 얘긴가?

  • 애초에 언두 로그랄 게 없다 (새로 생성된 경우)
  • 로우가 삭제될 때 언두 로그가 같이 날아가는걸 저걸로도 막을수가 없다
  • 그래서 쓰기 잠금을 거는 SELECT ~~~ FOR UPDATE을 쓰더라도 팬텀 리드가 발생한다는 의미로 서술한 것이다.
  • SELECT ~~~ FOR UPDATE를 써서 발생했다는 뜻이 아니다! 그냥 SELECT로 해도 발생 한다!

• 레퍼런스

  • https://stackoverflow.com/questions/11043712/what-is-the-difference-between-non-repeatable-read-and-phantom-read
  • https://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads