TIL

5.2 MySQL 엔진에서의 잠금

• MySQL에서 잠금은 크게 MySQL 엔진 레벨과 스토리지 엔진 레벨이 있다.
• MySQL 엔진 레벨 잠금
  • 모든 스토리지 엔진에 영향을 미침
  • 테이블 락, 메타데이터 락, 네임드 락 등이 있다.
• 스토리지 엔진 레벨 잠금
  • 스토리지 엔진 간 상호 영향을 미치지는 않는다.

5.2.1 글로벌 락

• FLUSH TABLES WITH READ LOCK 명령으로 획득 가능
• 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 DDL 문장이나 DML이 대기하게 된다.
• 글로벌 락은 MySQL 서버 전체에 영향을 미치며 데이터베이스가 달라도 영향을 미친다.

글로벌 락은 보통 MyISAM이나 MEMORY 스토리지 엔진에서 일관된 백업을 받아야 할 때 사용했다. 하지만 8.0 버전부터 InnoDB의 사용이 일반화 되었다. 트랜잭션의 도움을 받을 수 있어서 데이터의 모든 변경을 멈출 필요가 없어졌다. 그래서 가벼운 글로벌 락의 필요성이 생겼고 MySQL 8.0부터는 Xtrabackup이나 Enterprise Backup 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입 됐다.

백업 락

LOCK INSTANCE FOR BACKUP;
-- // 백업 실행
UNLOCK INSTANCE;

• 특정 세션에서 백업 락을 획득하면 다음의 작업을 못하게 된다.
  • 데이터베이스 및 테이블 등 모든 객체 생성 및 변경 삭제
  • REPAIR TABLE과 OPTIMIZE TABLE 명령
  • 사용자 관리 및 비밀번호 변경
• 일반적인 테이블 데이터 변경은 허용된다.
• 백업 락은 백업 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 한다.

5.2.2 테이블 락

• 개별 테이블 단위로 설정되는 잠금이며 명시적, 묵시적으로 락을 획득할 수 있다.
  • 명시적 락 획득: LOCK TABLES table_name [ READ/WRITE ]
  • 특별한 상황이 아니라면 애플리케이션에서 사용할 일이 거의 없다.
• 묵시적 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터 변경 쿼리를 실행하면 발생한다.
• InnoDB에선 테이블 데이터 변경 쿼리를 실행하면 레코드 기반 잠금을 획득한다.
  • InnoDB의 경우도 DDL의 경우엔 묵시적 테이블 락이 발생

5.2.3 네임드 락

• 네임드 락은 GET_LOCK() 함수로 임의의 문자열에 대해 잠금을 설정할 수 있다.
  • 잠금의 대상이 테이블이나 레코드 같은 데이터베이스 객체가 아니다.
• 활용하는 방법
  • 여러 대의 웹 서버가 하나의 데이터베이스 서버를 사용할 때 어떤 정보를 동기화해야 하는 요건인 경우에 활용할 수 있다.
  • 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하다.
    • 위 경우에 네임드 락 없이 사용하면 여러 레코드의 락들이 겹쳐 데드락의 원인이 되곤 한다.

-- // "mylock"이라는 문자열에 대해 잠금을 획득한다.
-- // 이미 잠금을 사용 중이라면 2초 동안 대기한다. (2초 후 자동 잠금 해제)
SELECT GET_LOCK('mylock', 2);

-- // "mylock"이라는 문자열에 대해 잠금이 설정돼 있는지 확인
SELECT IS_FREE_LOCK('mylock');

-- // "mylock"이라는 문자열에 대해 획득했던 잠금을 반납
SELECT RELEASE_LOCK('mylock');

• MySQL 8.0부터는 네임드 락을 중첩해서 사용할 수 있게 됐으며 한 번에 락을 모두 해제할 수도 있다.

SELECT GET_LOCK('mylock_1', 2);
SELECT GET_LOCK('mylock_2', 2);

SELECT RELEASE_AL_LOCKS();

5.2.4 메타데이터 락

• 메타데이터 락은 데이터베이스 객체(테이블이나 뷰)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.
  • 명시적으로 획득하는 것이 아닌 RENAME TABLE tab_a TO tab_b 같은 명령어로 획득할 수 있다.
  • RENAME 명령어의 경우 원본 이름과 변경 이름 모두 한 번에 잠금을 설정한다.
• 실시간으로 테이블을 바꿔야 하는 상황에 사용될 수 있다.

활용 예1

• rank라는 테이블의 데이터를 rank_backup으로 백업하고, 새로 만들어진 랭킹 테이블인 rank_new를 서비스용으로 대체하고자 하는 경우
• 배치 프로그램에서 rank의 데이터를 rank_backup으로 백업하였다.
• 이제 새로 만들어진 rank_new를 서비스용으로 대체해야 한다.

RENAME TABLE rank TO rank_backup;
RENAME TABLE rank_new TO rank;

• 위와 같이 두 RENAME 명령을 따로 실행하면 아주 짧은 시간 동안 rank 테이블이 존재하지 않게 되며 그 때 실행되는 쿼리는 Table not found ‘rank’ 오류가 발생하게 된다.

RENAME TABLE rank TO rank_backup, rank_new TO rank;

• 위와 같이 한 번에 RENAME 명령을 실행하면 오류 없이 적용하는 것이 가능하다.
  • rank라는 이름에 잠금이 걸린 상태에서 이름을 한 번에 바꾸기 때문

활용 예2

• access_log라는 테이블에는 INSERT만 실행된다.
• 테이블 구조를 변경해야하는 요구사항이 생겼다.
  • Online DDL을 사용할 수도 있지만 시간이 너무 오래 걸릴 수도 있다.
  • 오래 걸리면 언두 로그 증가 및 누적된 Online DDL 버퍼 크기 등의 문제가 있다.
  • 가장 큰 문제는 MySQL에서 DDL은 단일 스레드로 동작하기에 시간이 많이 소요된다는 것이다.

Online DDL: DDL 작업을 수행할 때 해당 테이블에 잠금을 수행하거나 다른 트랜잭션이 해당 테이블에 접근하지 못하게 하는 것을 필요하지 않게 하는 기능이다.

• 이를 해결하기 위해 우선 요구사항이 반영된 테이블을 만들고(access_log_new) 최근 데이터까지는 pk 범위 별로 여러 스레드로 빠르게 복사한다.

INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=0 AND ID<10000;
INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=10000 AND ID<20000;
INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=20000 AND ID<30000;
INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=30000;

• 나머지 데이터는 트랜잭션과 테이블 잠금, RENAME 명령으로 운영 환경에서 중단 없이 실행할 수 있다.
  • 아래와 같은 작업에서 데이터 복사하는 시간 동안 운영 쿼리는 막히기 때문에 가능하면 아주 최근 데이터까지 복사해서 잠금 시간을 최소화해야 한다.

-- 트랜잭션 시작. (BEGIN이나 START TRANSACTION으로 하면 안 됨)
SET autocommit=0;

-- 작업 대상 테이블 2개에 쓰기 락 획득
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 access_log_old, access_log_new TO access_log;
UNLOCK TABLES;

-- 불필요한 테이블 삭제
DROP TABLE access_log_old;