[Real MySQL] 05. 트랜잭션과 잠금

• 트랜잭션(Transaction)은 데이터의 정합성을 보장하기 위한 기능
  • 작업의 완전성을 보장해주는 것
  • 논리적인 작업 셋을 모두 완벽하게 처리 or 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상(Partial update)가 발생하지 않게 만들어주는 기능
• 잠금(Lock)은 동시성을 제어하기 위한 기능

트랜잭션

• MyISAM, MEMORY 스토리지 엔진 👉 트랜잭션 지원 X
• InnoDB 스토리지 엔진 👉 트랜잭션 지원 O

MySQL에서의 트랜잭션

• 트랜잭션은 1개의 논리적인 작업 셋에 1개의 쿼리가 있든 2개 이상의 쿼리가 있든 관계없이
  • 논리적인 작업 셋 자체가 100% 적용(COMMIT) or 아무것도 적용 X(ROLLBACK) 보장해주는 것
• MyISAM, MEMORY는 트랜잭션을 지원하지 않기 때문에 테이블이 부분 업데이트되는 현상이 발생할 수 있음
  • 데이터 정합성을 맞추는데 상당히 어려운 문제를 만들어냄
• InnoDB는 트랜잭션을 지원하기 때문에 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만듦

주의사항

• 프로그램 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 범위를 최소화해야 함
• 프로그램 코드에서 1-2줄만 차지한다 하더라도 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 함
  • DBMS 서버가 높은 부하 상태 or 위험한 상태에 빠지는 경우가 발생함

MySQL 엔진의 잠금

• MySQL 엔진 레벨 잠금
  • MySQL 서버에서 스토리지 엔진을 제외하고 나머지 부분
  • 모든 스토리지 엔진에 영향을 미침
• 스토리지 엔진 레벨 잠금
  • 스토리지 엔진간 상호 영향 X

글로벌 락

• FLUSH TABLES WITH READ LOCK 명령
• MySQL 서버 전체에 대해 잠금 설정
  • MySQL에서 제공하는 잠금 중 범위가 가장 큼
  • 작업 대상 테이블 or 데이터베이스가 다르더라도 동일하게 영향을 미침
  • e.g. 한 세션에서 글로벌 락을 획득하면, 다른 세션에서 SELECT을 제외한 대부분의 DDL/DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남음
• 여러 데이터베이스에 존재하는 MyISAM or MEMORY 테이블에 대해서 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 함
• ⚠️ 글로벌 락은 MySQL 서버의 모든 테이블에 영향을 주기 때문에 웹 서비스용으로 사용되는 MySQL 서버에는 가급적 사용하지 않는 게 좋음
  • 장시간 실행되는 쿼리와 글로벌 락 명령이 최악의 케이스로 실행되면, MySQL 서버의 모든 테이블에 대한 INSERT, UPDATE, DELETE 쿼리가 아주 오랫동안 실행되지 못하고 기다릴 수 있음
• InnoDB 스토리지 엔진은 트랜잭션 지원
  • 따라서, 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 X
• MySQL 8.0부터 InnoDB가 기본 스토리지 엔진으로 채택되며, 좀 더 가벼운 글로벌 락의 필요성이 생김
  • 백업 락 도입
    • 🟢 일반적인 테이블의 데이터 변경(DML) - 허용 O
    • 🔴 데이터베이스 및 테이블 등 모든 객체를 생성/변경/삭제(DDL) - 허용 X
    • 🔴 REPAIR TABLE 및 OPTIMIZE TABLE
    • 🔴 사용자 관리 및 비밀번호 변경
  • 소스 서버(Source Server) - 복제
  • 레플리카 서버(Replica Server) - 백업
  • e.g. 복제를 수행하다 DDL 명령이 실행되면, 백업 실패를 막기 위해 복제를 일시 중지함

테이블 락

• LOCK TABLES {table_name} [ READ | WRITE ], UNLOCK TABLES 명령
• 개별 테이블에 대해 잠금 설정
• 명시적인 테이블 락
  • 특별한 상황이 아니면, 애플리케이션에서 사용할 필요가 거의 X
• 묵시적인 테이블 락
  • 쿼리가 실행되는 동안 자동으로 획득, 쿼리가 완료되면 자동으로 해제
  • InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공
    • 따라서, 단순 데이터 변경 쿼리로는 테이블 락이 설정되지 X
    • 대부분의 DML 쿼리의 경우 무시되고, DDL 쿼리의 경우 영향을 미침

네임드 락

• GET_LOCK(), RELEASE_LOCK(), RELEASE_ALL_LOCKS() 함수
• 임의의 문자열에 대해 잠금 설정

메타데이터 락

• 데이터베이스 객체(e.g. 테이블 or 뷰)에 대해 이름이나 구조를 변경할 때 잠금 설정
• 명시적인 획득 or 해제 X
• 자동적인 획득 or 해제 O
  • e.g. RENAME TABLE {one_table_name} TO {other_table_name}

InnoDB 스토리지 엔진 잠금

• 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있음
  • MySQL에서 제공하는 잠금과는 별개
  • 레코드 기반의 잠금 방식 덕분에 MyISAM보다 훨씬 뛰어난 동시성 처리를 제공할 수 있음

InnoDB 스토리지 엔진의 잠금

• 잠금 정보는 상당히 작은 공간에서 관리 O
  • 따라서, 레코드 락 👉 페이지 락 or 테이블 락으로 레벨업(락 에스컬레이션)되는 경우 X

레코드 락

• 레코드 자체만을 잠금
• InnoDB 스토리지 엔진은 레코드 자체를 잠금 X, 인덱스의 레코드를 잠금 O

갭 락

• 레코드와 바로 인접한 레코드 사이의 간격만을 잠금
• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 역할

넥스트 키 락

• 레코드 락 + 갭 락
• 소스 서버에서 만든 결과 == 레플리카 서버에서 바이너리 로그(STATEMENT 포맷)에 기록되는 쿼리가 실행되고 결과를 보장하는 것이 주목적
• ⚠️ 의외로 갭 락 or 넥스트 키 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 하는 일이 발생함
  • 따라서, 바이너리 로그를 STATEMENT 포맷 👉 ROW 포맷으로 변경해서 갭 락 or 넥스트 키 락을 줄이는 게 좋음

자동 증가 락

• 테이블 수준의 잠금
• 명시적인 획득 or 해제 X
• 아주 짧은 시간 동안 걸렸다가 해제되는 잠금
  • 따라서, 대부분의 경우 문제가 되지 X
• AUTO_INCREMENT 값이 한번 증가하면 절대 줄어들지 않는 이유?
  • 자동 증가 락을 최소화하기 위함
  • e.g. INSERT 쿼리 실패 👉 AUTO_INCREMENT 값 이미 증가 👉 AUTO_INCREMENT 값은 줄어들지 않고 그대로 남음

인덱스와 잠금

• InnoDB의 잠금과 인덱스는 중요한 연관관계가 O
• 인덱스를 적절하게 설정해야 함
  • 그렇지 않으면, 클라이언트간 동시성이 상당히 떨어짐
  • e.g. 한 세션에서 UPDATE 작업을 하는 도중에는 다른 세션에서 UPDATE 작업을 하지 못하고 기다려야 하는 상황 발생
• 테이블에 인덱스가 하나도 없다면?
  • 테이블을 풀 스캔함
  • e.g. 테이블에 30여만 건의 레코드가 있으면, 해당 레코드를 모두 잠금
• ⚠️ MySQL의 InnoDB에서 인덱스 설계는 중요함

레코드 수준의 잠금 확인 및 해제

• 트랜잭션이 어떤 잠금을 기다리고 있는지, 어떤 트랜잭션이 기다리고 있는 잠금을 가지고 있는지 👉 메타 정보를 통해 조회 가능
• MySQL 5.1부터 information_schema에서 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS를 통해 확인 가능 👉 Deprecated
• MySQL 8.0부터 performance_schema에서 DATA_LOCKS, DATA_LOCK_WAITS를 통해 확인 가능

MySQL의 격리 수준

• 일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITED or REPEATABLE READ를 사용

READ UNCOMMITED

• RDBMS 표준에서 트랜잭션 격리 수준으로 인정하지 않을 정도로 데이터 정합성에 문제가 많은 격리 수준
• MySQL을 사용한다면, 최소한 READ COMMITED 이상의 격리 수준을 사용해야 함
• DIRTY READ
  • 어떤 트랜잭션에서 처리하는 작업이 완료되지 않았는데, 다른 트랜잭션에서 볼 수 있는 현상

READ COMMITED

• Oracle에서 기본으로 사용하는 격리 수준
• 온라인 서비스에서 가장 많이 선택하는 격리 수준
• NON-REPEATABLE READ
  • 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했지만, 항상 같은 결과를 가져오지 않는 현상
  • 금전적인 처리와 연결되면 문제가 될 수 있음

REPEATABLE READ

• MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용하는 격리 수준
• MVCC(Multi Version Concurrency Control)
  • InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK되는 상황에 대비하여 레코드를 언두(Undo) 공간에 백업하고, 레코드 값을 변경함
• InnoDB의 모든 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가)를 가지고 있음
• 테이블과 언두 로그의 모든 레코드에는 변경을 발생시킨 트랜잭션 번호가 포함되어 있음
• 1개의 레코드에 1개 이상의 백업 데이터가 존재할 수 있음
  • 사용자가 트랜잭션을 시작하고, 트랜잭션을 장시간 종료하지 않으면?
    • 언두 로그가 백업 데이터로 무한정 커질 수 있음
    • MySQL 서버의 처리 성능이 떨어질 수 있음
• PHANTOM READ
  • 다른 트랜잭션에서 수행하는 변경 작업에 의해 레코드가 보였다, 안 보였다하는 현상

SERIALIZABLE

• 가장 단순한 격리 수준 + 가장 엄격한 격리 수준
• 다른 트랜잭션 격리 수준보다 동시 처리 성능이 떨어짐
• 한 트랜잭션에서 읽기 or 쓰기 작업 중인 레코드는 다른 트랜잭션에서 절대 접근 X