트랜잭션과 잠금 - Youngwon Seo

트랜잭션은 작업의 완정성을 보장해주는것이다. 잠금과 비슷한 개념같지만 다음과 같은 차이가 있다.

트랜잭션: 데이터 정합성을 보장하기 위한 기능
잠금: 동시성을 제어하기 위한 기능

그럼 완정성이란 무엇일까? 완정성이란 쿼리가 하나이든 여러개이든 하나의 논리적인 작업셋이 100% 적용되거나(COMMIT을 실행했을 때) 아무것도 적용되지 않는것(ROLLBACK 또는 트랜잭션을 ROLLBACK시키는 오류가 발생했을때)을 의미한다.

스토리지 엔진 MyISAM, MEMORY InnoDB

트랜잭션

MySQL 엔진의 잠금

MySQL에서 사용하는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 구분할 수 있다. MySQL 엔진은 MySQL 서버의 스토리지 엔진을 제외한 나머지 부분으로 MySQL 엔진 레벨 잠금은 모든 스토리지 엔진에 영향을 미치지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진간 상로 영향을 미치지 않는다.

글로벌 락
- 범위(MySQL 서버 전체)가 가장 큰 잠금
  - 다른 테이블은 물론 다른 데이터베이스에도 영향
- 한 세션에서 클로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL/DML 수행시 대기
- FLUSH TABLES WITH READ LOCK 으로 획득
- MyISAM이나 MEMORY 테이블의 일관된 백업을 위해 클로벌 락 사용필요
- InnoDB (MySQL8 부터 기본 스토리지) 에서는 조금더 가벼운 백업락이 도입됨
  - LOCK INSTANCE FOR BACKUP, UNLOCK INSTANCE
  - 한 세션에서 백업락을 획득하면 모든 세션에서 일반적인 데이터 변경은 허용하나 테이블의 스키마나 사용자 인증 관련 정보를 수정할 수 없음
테이블 락
- 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 락을 획득할 수 있음
- (명시적) LOCK TABLES table name [READ | WRITE] 으로 획득
- MyISAM뿐 아니라 InnoDB에서도 사용가능
- (묵시적) MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생
- InnoDB는 스토리지 엔진 차원에서 레코드 기반 잠금을 제공
  - 정확히는 DML에서는 무시되고 DDL만 영향을 미침
네임드 락
- GET_LOCK()함수를 이용해 임의의 문자열에 대해 잠금 설정
- 이 잠금의 특징은 잠금 대상이 테이블, 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라 사용자가 지정한 문자열이라는 점

메타데이터 락

데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우 획득하는 잠금
명시적으로 획득하는것이아니고 RENAME TABLE tab_a TO tab_b 같이 테이블 이름을 변경하는 경우 자동으로 획득하는 잠금

대용량 데이블 구조 변경

MySQL 서버의 DDL은 단일 스레드로 수행
변경동안 언두로그 증가, 누적된 Online DDL 버퍼 크기등의 문제에 대해 고민 필요

다음과 같이 해결

  // 새로운 테이블 생성
  CRAETE access_log_new (
    ...
  )
  // 멀티스레드로 데이터이전
  ...

  > SET authcommit=0;
  > LOCK TABLES access_log WRITE, access_log_new WRITE;
  // 남은 데이터 복사
  > SELECT MAX(id) as @MAX_ID FROM access_log;
  > INSERT INTO access_log_new SELECT * FROM access_log WHERE id>@MAX_ID;
  > COMMIT;

  > RENAME TABLE access_log TO acess_log_old, access_log_new TO access_log;
  > UNLOCK TABLES;

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진에서는 MySQL의 잠금과 별개로 스토리지 내부에 레코드 기반 잠금방식을 탑재
때문에 MyISAM보다 훨씬 뛰어난 동시성 처리를 제공
하지만 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다로움
최근 버전에서는 트랜잭션의 잠금, 잠금 대기중인 트랜잭션 목록을 조회할 수 있는 방법이 도입됨 -information_schema 데이터베이스의 INNODB_TRX,INNODB_LOCKS, INNODB_LOCK_WAITS
InnoDB 스토리지 엔진의 잠금
인덱스와 잠금
레코드 수준의 잠금 확인 및 해제

트랜잭션 격리 수준

트랜잭션 격리수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼수 있게 허용할 지 말지를 결정하는것이다.

격리 수준	Dirty Read	Non-repeatable Read	Phantom Read
READ UNCOMMITTED	허용됨	허용됨	허용됨
READ COMMITTED	허용되지 않음	허용됨	허용됨
REPEATABLE READ	허용되지 않음	허용되지 않음	허용됨
SERIALIZABLE	허용되지 않음	허용되지 않음	허용되지 않음

용어 설명
- DIRTY READ: 다른 트랜잭션이 커밋하지 않은 데이터를 읽는것
- NON-REPEATABLE READ: 한 트랜잭션에서 같은 데이터를 두 번 읽었을때, 다른 트랜잭션에 의해서 데이터가 변경되어 다른 값이 읽어지는 현상
- PHANTOM READ: 한 트랜잭션에서 같은 조건으로 데이터를 두 번 읽었을때, 새로운 행이 추가되거나 기존행이 삭제되어 결과가 달라지는 현상

일반적인 온라인 서비스용도의 데이터베이스는 READ COMMITED 또는 REPEATABLE READ 중 하나를 사용, MySQL에서는 주로 REPEATABLE READ를 사용

READ UNCOMMITTED
- 각 트랜잭션에서의 변경 내용이 COMMIT 또는 ROLLBACK 여부에 상관없이 다른 트랜잭션에 보임
  - 이를 DIRTY READ 라고함
READ COMMITTED
- 온라인 서비스에서 가장 많이 선택되는 격리수준으로 COMMIT이 완료된 데이터만 조회가능
- 한 트랜잭션에서 데이터를 변경하면 변경전 데이터를 Undo영역에 백업하고 다른 트랜잭션은 Undo영역의 데이터를 조회함
- NON-REPEATABLE READ가 발생
  - 한 트랜잭션에서 특정 값(id=”toto”)을 조회했는데 데이터 없었음 (트랜잭션 유지)
  - 다른 트랜잭션에서 데이터 입력후 커밋
  - 첫번때 트랜잭션에서 특정 값(id=”toto”)를 조회했는데 데이터가 조회됨
  - 한 트랜잭션에서 같은 쿼리를 실행하면 같은 결과를 가져와야한다는 REPEATABLE READ 정합성 위배
  - 일반적인 웹 프로그램에서는 문제되지 않을 수 있지만 금전적인 처리와 연결되면 문제가 될 수 있음
REPEATABLE READ
- InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준
- 커밋되지 않은 데이터는 Undo 영역에 백업된 데이터를 보여줌
  - 이런방식을 MVCC(Multi Version Concurrency Control)라고함
  - READ COMMITTED와 동일한데 차이점은 Undo영역에 백업된 레코드의 여러버전 가운데 몇번째 이전 버전까지 찾아 들어가느냐에 있음
- 트랜잭션 B가 SELECT … FOR UPDATE로 특정 쿼리의 결과를 수행해도 다른 트랜잭션에 의해 레코드가 삽입되어 동일한 쿼리에 대해서 다른 결과를 반환가능
  - 이를 PHANTOM READ라고 함
SERIALIZABLE
- 가장 단순한 격리 수준이면서 가장 엄격한 격리 수준
- 읽기 작성도 공유 잠금(읽기 잠금) 획득이 필요
  - 다른 트랜잭션에서 읽고 있는 레코드 접근 불가능
  - 그만큼 동시 처리 성능이 떨어짐
- InnoDB 스토리지 엔진에서는 갭락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요성은 없어 보임

References

MySQL - Tractional Isolcation Levels