SQL

MySQL을 통해 SQL 파헤치기 -2- 트랜잭션과 락 그리고 격리 수준

hj.choi 2022. 3. 7. 16:24

Overview

저번 문서에서는 MySQL의 아키텍처에 대해서 알아보았다. 내용이 깊고 방대하여 Real MySQL 8.0 서적에서 나온 모든 내용을 정리하진 못했고, 서비스 개발에 필요하다고 생각하는 부분 위주로 정리를 했었다. 만약 이 글을 읽고 있는 사람 중에서 좀 더 깊고 다양하게 알고 싶으신 분들은 해당 서적을 읽어보는 것을 추천한다.

이번 문서에선, 트랜잭션과 락 그리고 격리 수준(isolation level)에 대해서 알아보겠다. spring에서는 보통 @Transactional을 통해 aop proxy를 활용하여, transactionManager를 직관적으로 사용할 수 있게 해준다.(물론 transactionManager를 직접 DI 하여 사용하는 경우도 있다.)

클라이언트 요청이 한번에 하나 씩 질서 정연하게 서버에 요청을 한다면, 사실 트랜잭션의 기본적인 기능만 믿고 사용해도 무난하겠다. 하지만, 수많은 클라이언트들이 다양한 상황으로 다양한 요청을 하고, 그렇게 발생하는 트랜잭션을 최대한 짧은 시간에 많이 처리하여, 높은 TPS를 확보하는 것이 중요하다.(처리 속도가 느리면, 그 상황을 서비스 오류로 생각하는 사용자도 많기 때문에...)

이번에 데이터베이스의 트랜잭션과 락과 격리 수준을 잘 이해하여, 한참 개발중인 프로젝트의 성능을 최대한 떨어트리지 않으면서, 최대한 많은 tps를 소화하는 애플리케이션을 구현하는데 참고해보면 좋을 것 같아 이렇게 글을 쓰게 되었다.

서론이 길었는데, 혹시라도 나의 비슷한 생각을 갖고 이 글을 보게된 사람이 있다면, 많이 참고해볼 수 있는 글이 된다면 정말 좋겠다.

1.0 트랜잭션

  • 트랜잭션은 작업의 완전성을 보장하는 개념이다. 'All or Nothing'이라고도 한다.
    • 경우에 따라서, Partial update을 받아들여야 하는 상황도 있지만, 데이터베이스의 트랜잭션에 적용한다면, 쿼리가 매우 복잡해진다. SQL 서버와 같은 데이터 레이어의 서버는 편의상 확장(scale-out)이 버겁다. 따라서, SQL 서버의 부하를 최소화해야 하는 상황에서 쿼리를 복잡하게 한다는 것은 좋은 선택이 아닌 듯하다.
    • 따라서, 차라리 트랜잭션을 적용하여, 완전성을 보장하고, 오류가 발생하여, 전부 rollback 되었을 때, 애플리케이션 코드로 후속 처리를 하는 것이 더 좋아 보인다.(애플리케이션 레이어의 서버가 비교적 scale-out이 편하기 때문이다.)

1.1 MySQL에서의 트랜잭션

  • 트랜잭션은 꼭 여러 개의 쿼리로 이루어져야만 적용되는 작업이 아니다. 쿼리의 개수에 상관없이 의도한 작업이 전부 완료되면 COMMIT 그렇지 않으면 ROLLBACK을 한다는 과정을 보장하면 된다.

1.2 주의사항

일련의 작업을 트랜잭션에 포함하였을 때, 다음과 같은 경우를 주의해야 한다.

  • Read(SELECT), Write(INSERT, UPDATE, DELETE)의 작업은 될 수 있으면, 같은 트랜잭션에 넣지 않는 게 좋다.
    • 할당 가능한 커넥션의 개수는 제한적이다. 즉, 커넥션을 보유하는 시간이 길어지면, 다른 트랜잭션에 병목이 생길 확률이 크다. 따라서, read-only 트랜잭션을 활용하여, 커넥션을 소유하는 시간을 최대한 줄이는 것이 좋다.
  • FTP나 메일 전송과 같은 다른 네트워크를 통해 통신하는 작업은 분리하는 것이 좋다.
    • 네트워크 IO는 일반적인 디스크 IO보다 100배 정도 느린 것으로 알고 있다. 느린 작업을 하나의 트랜잭션에 포함한다면, 해당 커넥션을 너무 오래 점유하는 현상이 일어난다.

2.0 MySQL 엔진의 잠금

MySQL에서 사용되는 락은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. 각 레벨마다 관리하는 락의 종류가 다른데, 먼저 MySQL 엔진 레벨의 락의 종류로는 '글로벌 락', '테이블 락', '네임드 락', '메타데이터 락'이 있다.

2.1 글로벌 락

  • 락의 범위는 용어에서 유추해볼 수 있듯이, 글로벌 락은 작업의 종류와 상관없이 락의 범위가 MySQL 서버 전체이다.
  • 따라서, MySQL 서버의 모든 변경 작업을 멈춘다.
  • 하지만, InnoDB의 경우 트랜잭션을 지원하기 때문에, 일관된 데이터 상태를 유지하기 위해 글로벌 락을 걸 필요가 없다. 그래서, InnoDB의 경우 글로벌 락 대신, 백업 락을 건다.
    • 백업 락은 스키마나, 사용자의 인증 관련 정보를 변경할 수 없지만, 일반적인 테이블의 데이터 변경(DML)은 허용한다.
    • 백업 락의 목적은 '백업' 도중에 DDL이 발생하는 것을 막기 위함이다.

2.2 테이블 락

  • 개별 테이블 단위로 설정되는 락으로, 명시적, 혹은 묵시적으로 테이블 락을 획득할 수 있다.
    • 명시적 테이블 락은 LOCK TABLES [ READ | WRITE ]와 같은 명령어로 명시적으로 락을 지정하는 것이고
    • 묵시적 테이블 락은 DML 실행 시, 조용하게 락을 걸었다가 작업이 처리된 후, 자동 해제되는 락인데, 사실 InnoDB를 사용한다면, 스토리지 엔진 레벨의 락에서 레코드 기반의 락을 제공하기 때문에, InnoDB에서는 DML에는 적용되지 않고, DDL의 경우에만 테이블 락이 걸린다.

2.3 네임드 락

  • GET_LOCK() 함수를 이용해 임의의 문자열에 대해 락을 설정할 수 있다.
  • 다만 AUTO_INCREMENT와 같은 데이터베이스 객체를 대상으로 거는 락이 아니다.
  • 자주 사용되는 락은 아니지만, DB서버 1대를 3-4대 정도의 was가 접속해서 서비스하고 있는 상황에서 해당 was들이 상호 동기화를 해야 하는 상황에서 네임드 락을 활용해볼 수 있다.
  • 또한 배치 프로그램을 통해 한꺼번에 많은 레코드를 변경해야 할 때, 네임드 락을 통해 레코드 변경 범위를 나눠서 차근차근 처리하는 방식을 채택할 수 있다.

2.4 메타 데이터 락

  • 테이블, 뷰와 같은 데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득할 수 있는 락이다.
  • 새로운 테이블을 만들고, 기존 테이블의 레코드들을 그대로 옮겨야 할 때, 해당 락을 활용할 수 있다.

3.0 InnoDB 스토리지 엔진 잠금

  • MySQL에서 제공하는 락과는 별개로 레코드 기반의 락을 제공한다. 아래 내용에 더 자세히 설명하겠지만, 레코드 기반의 락은 뛰어난 동시성 처리를 제공한다. 하지만, 모니터링이 어렵다는 단점을 가졌는데, 이는 점점 더 개선되고 있는 추세이다. 현재는 내부 락(세마포어)에 대한 모니터링 방법도 추가됐다.

3.1.1 레코드 락

  • 레코드 자체에 락을 걸며, 기본 메커니즘은 다른 DBMS에서 제공하는 레코드 락과 동일한 역할을 한다.
  • 다만 차이점이 있다면, InnoDB의 레코드 락은 인덱스의 레코드에 락을 건다.
  • InnoDB에서 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 활용하지만, PK 혹은 유니크 인덱스에 의한 변경 작업은 갭 락은 사용하지 않고 레코드 자체에 대해서만 락을 건다.

3.1.2 갭 락

  • 갭 락은 타겟 레코드와 인접한 레코드 사이의 간격에 락을 거는 형태의 락이다.
  • 갭 락의 역할은 레코드 사이의 간격에 새로운 레코드가 INSERT 되는 것을 막는 것이다.
  • 갭 락은 갭 락 자체만을 사용하진 않고, 넥스트 키 락의 일부로 자주 활용된다.

3.1.3 넥스트 키 락

  • 넥스트 키 락은 쉽게 말하자면 레코드 락 + 갭 락의 조합이라고 볼 수 있겠다.
  • InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만드는 것이 목적이다.
  • 넥스트 키 락과 갭 락은 데드락 발생 혹은, 다른 트랜잭션을 기다리게 하는 경우가 많다. 따라서 바이너리 로그 포맷은 ROW로 설정하여, 최대한 해당 락을 줄이는 것이 좋다.(8.0에선 ROW가 기본)

3.1.4 자동 증가 락

  • AUTO_INCREMENT 칼럼의 경우 여러 레코드가 INSERT, REPLACE 될 때, 해당 컬럼이 중복되는 경우를 피해야 한다.
  • 자동 증가 락은 위와 같은 상황에서 AUTO_INCREMENT의 값을 가져오는 순간에 잠깐 테이블에 락을 걸었다 푸는 방식으로 작동하며, 명시적으로 획득했다가 해제하는 방법은 없다.
  • innodb_autoinc_lock을 조작하여, 자동 증가 락 정책을 바꿀 수 있다.
    • 8.0의 경우는 바이너리 로그 기본 포맷이 ROW이므로, 연속된 자동 증가 값(INSERT로 인해 자동 증가 컬럼이 꼭 일정한 값으로 증감하지는 않는다는 것으로 이해하면 좋을 것 같다.)을 보장하진 않지만, 유니크한 값이 생성되는 것을 보장하는 Interleaved mode를 채택한다.
    • Interleaved mode의 경우 auto_increment 컬럼이 일정하게 증감하는 것을 보장하진 않지만, 동시성 처리 성능이 다른 정책에 비해 뛰어나며, 유니크한 값을 생성하는 것을 보장한다.

3.2 인덱스와 락

위에서 잠깐 언급했듯이, InnoDB의 레코드 락은 레코드가 아닌 인덱스에 락을 건다. 따라서...

  • UPDATE와 같은 벌크 연산이 자주 있을 수 있는 종류의 쿼리의 경우, WHERE 절에 인덱스 범위를 지정을 신중하게 해야 한다.
    • 하나의 레코드를 update 하기 위해 지정한 인덱스 키워드로 인해 수백, 수만 건의 인덱스에 락을 걸어서 다른 트랜잭션이 접근을 못하여 기다리는 경우가 생기기 때문이다.

3.3 MySQL의 격리 수준

여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 보는 것에 대한 정책을 설정하는 항목이다. 격리 수준에 따라, 세 가지의 부정합의 문제점이 있다. 격리 수준에 따라, 설명하고 발생할 수 있는 부정합의 문제점을 짚어보는 식으로 내용을 전개해보겠다. 부정합의 문제점은 우선 키워드만으로 먼저 풀어내자면 다음과 같은 문제점이 있다.

  • DIRTY READ
  • NON REPEATABLE READ
  • PHANTOM READ

3.4.1 READ UNCOMMITTED

  • 가장 느슨한 격리 수준
  • 커밋 여부에 상관없이 다른 트랜잭션에서 가장 마지막에 업데이트된 칼럼의 내용을 read 하는 것을 허용한다.(DIRTY_READ 발생)

3.4.2 READ COMMITTED

  • 커밋된 내용만 read를 허용한다.
  • 다른 트랜잭션의 쿼리 구조에 따라, NON-REPEATABLE-READ 문제가 발생할 수 있다.
    • NON-REPEATABLE_READ 문제는 커밋 여부에 따라, 같은 쿼리문으로 리턴하는 결과가 다른 경우를 이야기한다.
      • 검색 쿼리가 WHERE name = 'tom'과 같은 조건절을 가진 SELECT 쿼리의 경우, 0건의 레코드가 계속 리턴되어야 하는 데, 특정 레코드의 name 컬럼이 tom으로 UPDATE 되었을 때, 1건의 레코드가 리턴되는 경우를 NON-REPEATABLE_READ 문제라 할 수 있다.

3.4.3 REPEATABLE READ

  • 트랜잭션 간에는 id 역할을 하는 번호가 있다.
    • REPEATABLE READ 격리 수준에서는 이러한 트랜잭션 id를 식별하고, MVCC를 활용하여, 쿼리의 결과를 변경 내역이 적용된 테이블에서 가져오거나 , 언두 로그의 컬럼을 가져와서, REPEATABLE READ를 구현하였다.
    • 하지만, 언두 로그 레코드에는 락을 걸 수 없기 때문에, 새로운 레코드가 INSERT 혹은 DELETE 되면서 특정 레코드가 검색될 수도 있고 그렇지 않을 수도 있는데, 이러한 현상을 PHANTOM_READ라고 한다.

3.4.4 SERIALIZABLE

  • 위에서 이야기한 PHANTOM_READ를 방지하기 위해 사용하는 락이다.
  • 가장 엄격한 격리 수준을 가졌고, 가장 낮은 동시성 처리 성능을 보여준다.
  • InnoDB의 경우, 갭 락과 넥스트 키락을 지원하기 때문에 이 정도의 격리 수준은 없어도 된다.

마무리

보통 멀티 프로세스 소프트웨어와 멀티 스레드 소프트웨어의 차이를 context switching 비용과 공유자원에 대한 접근 편의성, 그리고 경합에 의한 동시성 처리 비용의 trade-off를 비교하는 경우가 많다.

멀티 스레드 소프트웨어의 경우, 개발자를 가장 힘들게 하는 요소로 '동시성' 처리라고 하는데, MySQL의 작동 원리와 트랜잭션 락 관리 메커니즘(정확히는 InnoDB의 락 메커니즘)을 보면, 멀티 스레드 소프트웨어의 장점(자원 공유의 편의성과 낮은 context switching 비용)을 최대한 살리면서 단점(자원 분리와 그로 인한 동시성 처리의 어려움)을 어떻게 극복했는지에 대한 힘든 과정을 엿볼 수 있었던 것 같다.

또한, 트랜잭션을 그저 일련의 '서비스 로직의 흐름 단위'로만 보아서, 그냥 무지성으로 트랜잭션에 데이터 액세스 로직을 때려 넣곤 했었는데, 그 방식이 얼마나 위험한지 알게 되었다. 그리고 왜 비동기 프로그래밍이 필요하고, 트랜잭션의 길이를 길게 방치하면 안 되는지에 대한 이유를 구체적으로 알아볼 수 있는 과정이 되었던 것 같다.