[MySQL] 정합성과 잠금, 트랜잭션, 격리 수준 간단 정리

업무 중 트랜젝션 내에서 특정 테이블에 insert 한 데이터를 바로 다시 select 하는 비즈니스 로직을 작성하였는데, 이 조회 반환 값이 null이 되는 문제를 마주하게 되었다.

읽기 작업과 쓰기 작업의 DB 커넥션을 분리해서 사용하고 있었고, 커밋되지 않은 데이터였기 때문에 읽기용 커넥션에서 새로 추가한 데이터를 읽어올 수가 없었다.

insert를 하는 커넥션과 동일한 커넥션에서 조회하도록 하여 문제는 해결하였지만, 문제의 원인이 궁금해졌다.

(현재 MySQL InnoDB 스토리지 엔진을 사용 중이다.)

---

데이터 정합성 (Consistency)

데이터가 서로 모순 없이 일관되게 일치해야 함을 의미한다.

예를 들어, 상품 테이블에서 상품번호 1의 이름은 ‘도넛’인데, 주문 테이블에서 상품번호 1의 이름이 ‘떡볶이’ 일 경우, 상품 1의 데이터는 모순되어있으므로 정합성이 훼손되었다고 볼 수 있다.

대표적인 정합성 훼손(=부정합) 문제

dirty read

• 하나의 트랜잭션에서 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상
• 내가 시도했던 로직이 더티 리드였고, MySQL의 기본 격리 수준인 REPEATABLE READ 설정으로 인해 조회가 잠긴 것 같다.

non-repeatable read

• 하나의 트랜잭션 내에서 똑같은 select 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 repeatable read가 불가능한 상태를 의미
• ex) A 트랜잭션에서 입출금 반복 처리 중에 B트랜잭션에서 입금액 총합을 조회할 경우 select 실행 때마다 다른 결과를 가져올 수 있다

phantom read (= phantom row)

• 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안보였다 하는 현상
• 언두 레코드에는 잠금을 걸 수 없어 select … for update , select … lock in share mode로 조회 시 언두 영역의 변경 전 데이터가 아닌 현재 값을 가져오게 된다.
• InnoDB는 갭 락과 넥스트 키 락 덕분에 repeatable read에서도 phantom read 발생하지 않는다.

---

잠금(Lock)과 트랜잭션(Transaction)

잠금과 트랜잭션은 서로 비슷한 개념 같지만 서로의 목적이 다르다.

• 잠금 : 다른 사용자의 접근을 막아 동시성을 제어하기 위한 기능
  • MySQL에서 제공하는 잠금
    • 글로벌 락, 테이블 락, 네임드 락, 메타데이터 락
  • InnoDB에서 제공하는 잠금 : 레코드 기반 잠금 방식. 인덱스에 잠금을 건다
    • 레코드 락 (Record Lock), 넥스트 키 락(Next Key Lock), 갭 락(Gap Lock), 자동 증가 락(Auto increment lock)
• 트랜잭션 : 데이터의 정합성을 보장하기 위한 기능
  • InnoDB 스토리지 엔진에서 제공한다.
  • MyISAM과 MEMORY에서는 트랜잭션을 지원하지 않는다.

---

격리 수준 (Isolation Level)

관계형 데이터베이스는 격리 수준을 조정하여 일관성을 제어할 수 있다

• READ UNCOMMITTED
  • 데이터가 나타났다 사라졌다 하는 현상을 초래하고 더티리드를 유발한다. 개발자와 사용자를 혼란스럽게 만들 수 있어 사용을 지양한다.
• READ COMMITTED
  • 오라클 DBMS의 기본 격리 수준. 온라인 서비스에서 가장 많이 선택되는 격리 수준.
  • A 사용자가 데이터 update 하는 트랜잭션 도중에 B 사용자가 데이터 조회 시, 변경 전 데이터(=언두 로그 데이터)를 반환
• REPEATABLE READ
  • MySQL InnoDB 스토리지 엔진의 기본 격리 수준
  • (기본적으로 select 쿼리 문장도 트랜잭션 범위 내에서만 작동)
  • B사용자의 트랜잭션이 끝나기 전에 A 사용자가 데이터를 변경하고 커밋을 했더라도 B사용자는 A 사용자의 변경 전 데이터만 반환받는다
• SERIALIZABLE
  • 가장 단순하고 가장 엄격한 경리 수준
  • 동시 처리 성능이 타 격리수준 대비 떨어짐
  • 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서 절대 접근할 수 없음

 

격리 수준 확인법

select @@GLOBAL.transaction_isolation;
select @@SESSION.transaction_isolation;

 

격리 수준용으로 제공하는 변수 tx_isolation이 MySQL 8.0부터 deprecated 되었다.

MySQL 5.7.2 이하 : tx_isolation 사용 가능

MySQL 8.0 이상 : transaction_isolation 사용

---

정리하자면, 

내가 짰던 로직은 트랜잭션 도중에 다른 커넥션에서 데이터에 접근, 즉 Dirty Read를 시도한 것이고

현재 DB의 격리 수준이 REPEATABLE READ였기 때문에 커밋 전 데이터인 null이 반환된 것이라고 볼 수 있을 것 같다.

 

 

---

자료 출처

• Real MySQL
• https://luran.me/325