[Real MySQL] 트랜잭션과 잠금

MyISAM 스토리지 엔진은 트랜잭션을 지원하지 않는다.

InnoDB는 스토리지 엔진 내부에서 레코드 기반의 잠금 방식이 있다.

5.2 MySQL 엔진의 잠금

1. 스토리지 엔진 레벨

스토리지 엔진 간 상호 영향을 미치지 않는다.

2. MySQL 엔진 레벨

MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미친다.

• 테이블 락: 테이블 데이터 동기화를 위한 락
• 메타데이터락: 테이블 구조를 잠근다.
• 네임드 락: 사용자의 필요에 맞게 사용할 수 있는 락

5.2.1 글로벌 락

글로벌락

• SELECT를 제외한 DDL, DML 문장을 실행할 경우 글로벌 락이 해제될 때까지 대기상태로 남는다.
• MySQL 서버 전체에 영향을 미친다.
  • 작업 대상 테이블이나 데이터베이스가 달라도 영향을 미친다.
• MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야할 때 사용한다.

백업락

• MySQL 8.0에서 도입되었다. (InnoDB를 사용할 때 사용할 수 있다.)
• Xtrabackup이나 Enterprise Backup과 같은 백업 툴의 안정적인 실행을 지원하기 위해 도입되었습니다.
• 특정 세션에서 백업 락을 획득하면 모든 세션에서 테이블 스키마나 사용자 인증 관련 정보를 변경할 수 없다.
• 테이블의 데이터 변경은 허용된다.

5.2.2 테이블 락

명시적, 묵시적으로 획득할 수 있다.

MyISAM에서의 테이블 락

• DML 쿼리의 경우 테이블 락이 걸린다.

InnoDB에서의 테이블 락

• InnoDB는 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 떄문에 데이터 변경 쿼리로는 묵시적 테이블 락이 생성되지 않는다.
• DDL 쿼리의 경우에만 테이블 락이 걸린다.

5.2.3 네임드 락

사용자 정의 잠금기능이다.

복잡한 작업이나 외부 프로세스와의 동기화를 위해 사용된다.

사용 예시

상상해봅시다. 여러분의 웹 애플리케이션은 매일 자정에 특정 작업을 수행하는 배치 작업을 실행합니다. 이 배치 작업은 사용자들의 활동을 분석하여, 일일 보상을 계산하고 지급하는 작업입니다. 이 배치 작업은 한 번만 실행되어야 합니다. 그러나 여러분의 인프라에는 다양한 원인(예: 중복 스케쥴링, 오류 복구 등)으로 이 배치 작업이 여러 번 실행될 가능성이 있습니다. 이러한 상황에서 네임드 락을 사용하여 배치 작업의 동시 실행을 방지할 수 있습니다.

-- 배치 작업을 시작하기 전에 락을 시도합니다.
SELECT GET_LOCK('daily_reward_calculation', 10);

-- 락을 성공적으로 획득한 경우에만 배치 작업을 실행합니다.
-- ... (배치 작업 로직)

-- 배치 작업이 끝난 후에는 락을 해제합니다.
SELECT RELEASE_LOCK('daily_reward_calculation');

위의 코드에서 GET_LOCK 함수는 'daily_reward_calculation'이라는 이름의 락을 최대 10초 동안 시도합니다. 만약 다른 세션에서 이미 이 락을 보유하고 있다면, 해당 세션은 10초 동안 대기한 다음 실패합니다.
따라서, 네임드 락을 사용하면 동시에 여러 번 실행되는 배치 작업의 문제를 방지하고, 한 번만 실행되도록 보장할 수 있습니다.

5.2.4 메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.

명시적으로 획득할 수 없다. 테이블의 이름을 변경하는 경우 자동으로 획득된다.

5.3 InnoDB 스토리지 엔진 잠금

5.3.1 InnoDB 스토리지 엔진 잠금

5.3.1.1 레코드 락

레코드 자체가 아니라 클러스터 인덱스의 레코드를 잠근다.

프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 레코드 자체에만 락을 건다.

5.3.1.2 갭 락

레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠근다.

레코드와 레코드 사이의 새로운 레코드가 생성(INSERT)되는 것을 제어한다.

넥스트키락의 일부로 자주 사용된다.

5.3.1.3 넥스트 키 락

넥스트 키 락 = 레코드 락 + 갭 락

갭 락이나 넥스트 키 락은 바이버리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.

5.3.1.4 자동 증가 락

AUTO_INCREMENT 칼럼 속성을 위해 사용된다.

새로운 레코드를 저장하는 쿼리에서 사용된다.

innodb_autoinc_lock_mode 라는 시스템 변수를 사용한다.

• innodb_autoinc_lock_mode=0
  • 모든 INSERT 문장은 자동 증가 락을 사용한다.
• innodb_autoinc_lock_mode=1
  • INSERT되는 레코드를 정확하게 예측할 수 있을 때는 래치(뮤텍스) 사용
  • 아닐 때는 자동 증가 락 사용
• innodb_autoinc_lock_mode=2
  • 항상 래치(뮤텍스)를 사용한다.
  • 하나의 INSERT 문장으로 INSERT되는 레코드라고 하더라도 연속된 자동 증가 값을 보장하지 않는다.
  • STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있다.

5.3.2 인덱스와 잠금

InnoDB 잠금은 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락으로 걸어야 한다.

• 예
  • employees 테이블에 first_name 칼럼만 멤버로 담긴 ix_firstname이라는 인덱스가 있다. first_name = ‘Georgi’ 인 사원이 253명 있고 first_name = ‘Georgi’ last_name=Klassen이 1명이다. Update employees set hire_date=now() where first_name=‘Georgi’ and last_name=‘Klassen’을 할 때 253개의 레코드가 모두 잠긴다.
  • 이 테이블에 인덱스가 하나도 없다면, 테이블을 풀스캔하게 된다. 따라서 테이블 전체의 레코드에 잠금을 건다.