RealMySQL 05장 트랜잭션과 잠금

트랜잭션은 작업의 완전성을 보장해주는 것이다. 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 것이다.

헷갈릴 수 있지만 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.
잠금은 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다. 격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지 결정하는 레벨을 의미한다.

트랜잭션

insert into table_myisam (fdpk) values (3);
insert into table_myisam (fdpk) values (1), (2), (3);

두 테이블의 레코드를 조회해보면 MyISAM 테이블에는 PRIMARY KEY 중복 오류가 오류가 발생했음에도 '1'고ㅏ '2'은 Insert된 상태로 남아있는 것을 확일할 수 있다. MyISAM 테이블에서 실행되는 쿼리는 이미 Insert 된 '1'과 '2'를 그대로 두고 쿼리 실행을 종료해 버린다. MyISAM 테이블에서 발생하는 이러한 현상을 **부분 업데이트(Partial Update)라고 표현한다. 이런 부분 업데이트 현상은 데이터의 정합성을 맞추는데 상당히 어려운 문제를 발생시킨다.
하지만 inno db 테이블의 트랜잭션에서 위와 같은 일이 발생했다면 테이블에는 아무런 데이터가 남아있지 않는다.

주의사항

트랜잭션을 사용할 때는 범위를 최소화하는게 좋다. 트랜잭션이 길어지면 하나의 세션이 커넥션을 소유하는 시간이 길어지기 때문이다. 특히 메일 전송과 같은 I/O 작업이 있을 때는 트랜잭션을 분리하는 것이 적합하다.

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간에 상호 영향을 미치지 않는다.

글로벌 락

글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있다. MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다. 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며 모든 테이블에 대한 잠금을 동시에 획득할 수 있다.

FLUSH TABLES WITH READ LOCK 명령은 테이블에 읽기 잠금을 걸기 전에 먼저 테이블을 플러시해야 하기 때문에 테이블에 실행 중인 모든 종류의 쿼리가 완료돼야 한다. 장시간 실행되는 쿼리와 FLUSH TABLES WITH READ LOCK 명령이 최악의 케이스로 실행되면 MySQL 서버의 모든 테이블에 대한 INSERT, UPDATE, DELETE 문장이 대기 상태로 남는다.

• Q. FLUSH TABLES WITH READ LOCK 명령 이후의 모든 쿼리가 FLUSH TABLES WITH READ LOCK이 완전히 대기 후 실행 완료될 때까지 대기하는지?

백업 락

스키마 변경이 실행되면 현재 데이터에 대한 백업이 실패하게 된다.(데이터 누락은 있어도 되지만 테이블 구조가 변경되면 백업 자체가 불가능하다는 뜻으로 이해했다.) 테이블 락은 너무 비용이 크므로 백업만을 위해서 데이터 스키마 변경만 막는 백업락이 등장했다.

테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 테이블의 락을 획득 가능하다. LOCK TABLES table_name [ READ | WRITE ] 명령으로 테이블 락을 획득할 수 있다. 묵시적인 테이블 락은 MyISAM이나 Memory 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

네임드 락

네임드 락(Named Lock)은 GET_LOCK() 함수를 사용해서 획득할 수 있다. 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납하는 잠금이다. MySQL 8.0 버전부터는 네임드 락을 중첩해서 사용할 수 있게 됐으며, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됐다.

SELECT GET_LOCK('my_lock_1', 10);
-- // mylock_1에 대한 작업을 실행
SELECT RELEASE_LOCK('my_lock_2', 20);
-- // mylock_2에 대한 작업을 실행
 
SELECT RELEASE_LOCK('my_lock_1'); -- // mylock_1에 대한 작업을 실행
SELECT RELEASE_LOCK('my_lock_2'); -- // mylock_2에 대한 작업을 실행
 
SELECT RELEASE_ALL_LOCKS(); -- // 현재 세션에서 획득한 네임드 락을 모두 해제

메타데이터 락

메타데이터 락은 데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 메타데이터 락은 명시적으로 획득하는게 아니라 DDL 문장을 실행하는 경우 묵시적으로 사용되는 것이다.

DDL을 실행할 경우 단일 스레드로 동작하기 때문에 상당히 많은 시간이 소모될 수 있다. 예를 들어서 테이블의 이름 변경의 경우 새로운 테이블을 생성하고 데이터를 멀티 스레드로 복사하는 편이 좀 더 빠를 수 있다.

InnoDB 스토리지 엔진 잠금

InnoDB는 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 통해 트랜잭션과 잠금 상태를 조회할 수 있다. Performance Schema를 이용해서 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됐다.

갭락

레코드 사이의 간격을 잠그는 것을 갭락이라고 한다.

레코드 락

레코드 자체만을 잠그는 것을 레코드 락이라고 한다.

넥스트 키 락

레코드 락이랑 갭락을 합친 것을 넥스트 키 락이라고 한다. REPEATABLE READ 격리 수준에서 사용된다.

자동 증가 락

AUTO_INCREMENT 컬럼에 대해서 사용되는 테이블 수준의 잠금이다.
INSERT 문장이나 REPLACE 문장에서 AUTO_INCREMENT 컬럼에 대한 값을 가져올 때만 사용되고 가져온 후에는 바로 해제된다. AUTO_INCREMENT LOCK을 적용하는 방식을 옵션으로 선택할 수 있다.

• innodb_autoinc_lock_mode=0
  • 기본 동작 방식
• innodb_autoinc_lock_mode=1
  • 단순히 한 건 또는 여러 건의 레코드를 INSERT하는 SQL 중에서 레코드의 건수를 정확히 예측할 수 있을 때 예측된 범위만큼의 AUTO_INCREMENT 값을 한 번에 가져온다.
  • Consecutive Mode라고도 부른다.
• innodb_autoinc_lock_mode=2
  • 경량화된 래치(뮤텍스)를 사용한는 방식, 반드시 연속적인 값이 나오는걸 보장하진 않지만 유니크한 값임을 보장한다.
  • Interleaved mode라고도 한다.

MySQL 8.0 버전부터는 innodb_autoinc_lock_mode 옵션의 기본값이 2로 변경됐다. 하지만 ROW 포맷이 아니라 STATEMENT 포맷을 사용하는 경우 2가 아닌 1로 변경해서 사용할 것을 권장한다.

인덱스와 잠금

레코드 락에서 잠금다는 의미는 결국 인덱스를 잠근다는 뜻이다. 만약 인덱스 범위가 없다면 테이블 락이 걸릴 수 있다.

격리수준

REPEATABLE READ

기본으로 사용되는 격리 수준이다.
REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 버전을 보여줄 것인지에 대한 차이이다. 언두 영역에 백언된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 그리고 언두 영역의 백언된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.

사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.

MySQL REPEATABLE READ에서 Phanthom Read가 발생하지 않는 이유

select for update 쿼리는 select 하는 레코드에 쓰기 잠금을 걸어야 하는데 언두 레코드에는 잠금을 걸 수 없다. 그래서 select for update나 select lock in share mode로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져온다. 이 때 lock의 방식에 따라 pathom read가 발생하나 안하냐가 결정이 되는데 MySQL에서는 Next Key Lock을 사용하기 때문에 PHANTOM READ가 발생하지 않는다.