[스터디] 09. 트랜잭션과 잠금

목차

 

 

이번 장에서는 MySQL의 동시성에 영향을 미치는 잠금(Lock)과 트랜잭션, 트랜잭션의 격리 수준 (Isolation level)을 학습합니다.

5. 트랜잭션과 잠금

• 트랜잭션: 데이터베이스에서 하나의 논리적 작업 단위로, 여러 데이터베이스 작업이 하나의 단위로 묶여 실행되는 것. 작업의 안전성과 데이터의 정합성을 보장함.
  1. 트랜잭션의 특징
     • 원자성(Atomicity): 트랜잭션 내의 모든 작업이 완벽히 수행되거나, 전혀 수행되지 않아야 함. 예) 은행에서 계좌 간 이체 작업은 출금과 입금이 동시에 이루어져야 하며, 둘 중 하나라도 실패할 경우 전체 작업이 취소됨.
     • 일관성(Consistency): 트랜잭션이 완료되면 데이터베이스가 일관된 상태를 유지해야 함. 예) 은행 계좌의 잔액이 음수로 남아 있는 경우는 일관성이 없는 상태임.
     • 독립성(Isolation): 여러 트랜잭션이 동시에 실행될 때, 서로의 작업에 영향을 미치지 않아야 함.
     • 지속성(Durability): 성공적으로 완료된 트랜잭션의 결과는 영구적으로 데이터베이스에 반영됨.
  2. 트랜잭션 상태
     • 활성(Active): 트랜잭션이 시작되어 작업이 진행 중인 상태
     • 부분 완료(Partially Committed): 트랜잭션의 최종 명령이 실행된 후, 커밋 전 상태
     • 완료(Committed): 트랜잭션이 성공적으로 완료되어 변경 사항이 데이터베이스에 영구적으로 반영된 상태
     • 실패(Failed): 트랜잭션 도중 오류가 발생하여 실패한 상태
     • 철회(Aborted): 트랜잭션이 실패하여 모든 변경 사항이 취소된 상태
  3. 트랜잭션 명령어
     • START TRANSACTION 또는 BEGIN: 트랜잭션을 시작함.
     • COMMIT: 트랜잭션을 성공적으로 완료하고, 모든 변경 사항을 데이터베이스에 영구적으로 반영함.
     • ROLLBACK: 트랜잭션 수행 중 오류가 발생한 경우, 모든 변경 사항을 이전 상태로 되돌림.
     • SAVEPOINT: 트랜잭션 내 특정 지점을 설정하여, ROLLBACK이 해당 지점까지 되돌림.
     • RELEASE SAVEPOINT: 설정된 SAVEPOINT를 삭제함.
     • SET TRANSACTION: 트랜잭션의 격리 수준을 설정함.

• 잠금(Lock): 동시성을 제어하기 위한 기능.
• 예) 하나의 회원 정보 레코드를 여러 커넥션에서 동시에 변경하려고 하는데, 잠금이 없다면 하나의 데이터를 여러 커넥션에서 동시에 변경할 수 있음. 해당 레코드의 값은 예측할 수 없는 상태가 됨. 잠금은 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 함.
• 격리 수준: 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨

5.1 트랜잭션

트랜잭션을 지원하지 않는 MyISAM과 트랜잭션을 지원하는 InnoDB의 처리 방식 차이를 비교한다.

5.1.1 MySQL에서의 트랜잭션

• 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나(COMMIT을 실행했을 때) 아무것도 적용되지 않아야(ROLLBACK 또는 트랜잭션을 ROLLBACK시키는 오류가 발생했을 때)함을 보장함.

-- 트랜잭션 관점에서 InnoDB 테이블과 MyISAM 테이블의 차이 예제

mysql> CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM;
mysql> INSERT INTO tab_myisam (fdpk) VALUES (3);

mysql> CREATE TABLE tab_innodb ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=INNODB;
mysql> INSERT INTO tab_innodb (fdpk) VALUES (3);

-- // AUTO-COMMIT 활성화
mysql> SET autocommit=ON;

mysql> INSERT INTO tab_myisam (fdpk) VALUES (1),(2),(3);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

mysql> INSERT INTO tab_innodb (fdpk) VALUES (1),(2),(3);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

mysql> SELECT * FROM tab_myisam;
+------+
| fdpk |
+------+
| 1    |
| 2    |
| 3    |
+------+

mysql> SELECT * FROM tab_innodb;
+------+
| fdpk |
+------+
| 3    |
+------+

• 위와 같이 테스트용 테이블에 각각 레코드를 1건씩 저장한 후, AUTO-COMMIT 모드에서 다음 쿼리 문장을 InnoDB 테이블과 MyISAM 테이블에서 각각 실행함. 두 개의 스토리지 엔진의 테스트 결과는 다음과 같음.
  
  • 두 INSERT 문장 모두 프라이머리 키 중복 오류로 쿼리가 실패함.
  • 두 테이블의 레코드를 조회해 보면 MyISAM 테이블에는 오류가 발생했음에도 '1'과 '2'는 INSERT된 상태로 남아 있음. 즉, MyISAM 테이블에 INSERT 문장이 실행되면서 차례대로 '1'과 '2'를 저장하고, 그다음 '3'을 저장하는 순간 중복 키 오류(이미 '3'이 있기 때문)가 발생함. MyISAM 테이블에서 실행되는 쿼리는 이미 INSERT된 '1'과 '2'를 그대로 두고 쿼리 실행을 종료함.
• MEMORY 스토리지 엔진을 사용하는 테이블도 MyISAM 테이블과 동일하게 작동함. 하지만 InnoDB는 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문장을 실행하기 전 상태로 그대로 복구함.
• MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트(Partial Update)라고 하며, 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추기 어렵게 함. 부분 업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요할 수 있음.

5.1.2 주의사항

• 트랜잭션 또한 DBMS의 커넥션과 동일하게 필요한 최소의 코드에만 적용하는 것이 좋음. 프로그램 코드에서 트랜잭션의 범위를 최소화하라는 의미임.

사용자가 게시판에 게시물을 작성한 후 저장 버튼을 클릭했을 때 서버에서 처리하는 내용
1) 처리 시작
   ==> 데이터베이스 커넥션 생성
   ==> 트랜잭션 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
5) 사용자의 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
9) 알림 메일 발송 이력을 DBMS에 저장
   <== 트랜잭션 종료(COMMIT)
   <== 데이터베이스 커넥션 반납
10) 처리 완료

• 위 처리 절차 중에서 DBMS의 트랜잭션 처리에 좋지 않은 영향을 미치는 부분을 발견함.
  
  1. 실제로 많은 개발자가 데이터베이스의 커넥션을 생성(또는 커넥션 풀에서 가져오는)하는 코드를 1번과 2번 사이에 구현하며, START TRANSACTION 명령으로 트랜잭션을 시작함. 그리고 9번과 10번 사이에서 트랜잭션을 COMMIT하고 커넥션을 종료(또는 커넥션 풀로 반납)함. 실제 DBMS에 데이터를 저장하는 작업(트랜잭션)은 5번부터 시작되므로 2번과 3번, 4번의 절차가 아무리 빨리 처리되도 DBMS의 트랜잭션에 포함시킬 필요는 없음. 각 단위 프로그램이 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수도 있기 때문.
  2. 8번 작업은 위험함. 메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 어떻게 해서든 DBMS의 트랜잭션 내에서 제거하는 것이 좋음. 프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생하면 웹 서버뿐 아니라 DBMS 서버까지 위험할 수 있음.
  3. DBMS의 작업이 크게 4개가 있음. 사용자가 입력한 정보를 저장하는 5번과 6번 작업은 반드시 하나의 트랜잭션으로 묶어야 하며, 7번 작업은 저장된 데이터의 단순 확인 및 조회이므로 트랜잭션에 포함할 필요 없음. 9번 작업은 성격이 다르기 때문에 이전 트랜잭션(5번과 6번 작업)에 함께 묶지 않고 별도의 트랜잭션으로 분리하는 것이 좋음. 7번 작업은 단순 조회라고 본다면 별도로 트랜잭션을 사용하지 않아도 무방함.

문제 세 가지를 보완한 처리 절차
1) 처리 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 발생 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
   ==> 데이터베이스 커넥션 생성(또는 커넥션 풀에서 가져오기)
   ==> 트랜잭션 시작
5) 사용자의 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
   <== 트랜잭션 종료(COMMIT)
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
   ==> 트랜잭션 시작
9) 알림 메일 발송 이력을 DBMS에 저장
   <== 트랜잭션 종료(COMMIT)
   <== 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납)
10) 처리 완료

• 예제가 최적의 트랜잭션 설계는 아닐 수 있으며, 구현하고자 하는 업무의 특성에 따라 크게 달라질 수 있음. 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화되어 있는 프로그램의 범위를 최소화해야 함.
• 그리고 프로그램의 코드에서 라인 수는 한두 줄이라고 하더라도 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 함. 이런 실수로 인해 DBMS 서버가 높은 부하 상태로 빠지거나 위험한 상태에 빠지는 경우가 빈번히 발생함.

5.2 MySQL 엔진의 잠금

• MySQL에서 사용되는 잠금은 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있음.
• MySQL 엔진: MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분
• MySQL 엔진 레벨의 잠금: 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않음. MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블의 구조를 잠그는 메타데이터 락(Metadata Lock), 사용자의 필요에 맞게 사용할 수 있는 네임드 락(Named Lock)이라는 잠금 기능도 제공함.

5.2.1 글로벌 락

• 글로벌 락(GLOBAL LOCK): FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있음.
• 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남음.
• 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체임. 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 함.
• FLUSH TABLES WITH READ LOCK 명령을 이용한 글로벌 락은 MySQL 서버의 모든 변경 작업을 멈춤. InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없음.
• MySQL 8.0 버전부터는 Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됨.

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

• 특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없음.
  • 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
  • REPAIR TABLE과 OPTIMIZE TABLE 명령
  • 사용자 관리 및 비밀번호 변경
• 백업 락은 일반적인 테이블의 데이터 변경은 허용됨.
  • 일반적인 MySQL 서버의 구성은 소스 서버(Source server)와 레플리카 서버(Replica server)로 구성됨.
  • 백업은 주로 레플리카 서버에서 실행됨. 백업이 FLUSH TABLES WITH READ LOCK 명령을 이용해 글로벌 락을 획득하면 복제는 백업 시간만큼 지연됨. 레플리카 서버에서 백업을 실행하는 도중에 소스 서버에 문제가 생기면 레플리카 서버의 데이터가 최신 상태가 될 때까지 서비스를 멈춰야 할 수도 있음. 백업 락은 이런 이유로 도입됨.

5.2.2 테이블 락

• 테이블 락(Table Lock): 개별 테이블 단위로 설정되는 잠금. 명시적(LOCK TABLES table_name [ READ | WRITE ] 명령으로) 또는 묵시적으로 특정 테이블의 락을 획득할 수 있음.
• 테이블 락은 MyISAM뿐 아니라 InnoDB 스토리지 엔진을 사용하는 테이블도 동일하게 설정할 수 있음. 명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 반납(해제)함. 명시적인 테이블 락은 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없음.
• 묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생함. MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용됨. 즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제됨.

5.2.3 네임드 락

• 네임드 락(Named Lock): GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있음.
• 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아님. 네임드 락은 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금임.
• 네임드 락은 자주 사용되지는 않음. 예) 데이터베이스 서버 1대에 5대의 웹 서버가 접속해서 서비스하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 이용해 쉽게 해결함.

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

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

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

-- // 3개 함수 모두 정상적으로 락을 획득하거나 해제한 경우에는 1을,
-- // 아니면 NULL이나 0을 반환한다.

• 네임드 락은 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 사용할 수 있음. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 됨. 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 간단히 해결할 수 있음.
• MySQL 8.0 버전부터는 다음과 같이 네임드 락을 중첩해서 사용할 수 있으며, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됨.

mysql> SELECT GET_LOCK('mylock_1',10);
-- // mylock_1에 대한 작업 실행
mysql> SELECT GET_LOCK('mylock_2',10);
-- // mylock_1과 mylock_2에 대한 작업 실행

mysql> SELECT RELEASE_LOCK('mylock_2');
mysql> SELECT RELEASE_LOCK('mylock_1');

-- // mylock_1과 mylock_2를 동시에 모두 해제하고자 한다면 RELEASE_ALL_LOCKS() 함수 사용
mysql> SELECT RELEASE_ALL_LOCKS();

5.2.4 메타데이터 락

• 메타데이터 락(Metadata Lock): 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금.
• 명시적으로 획득하거나 해제할 수 있는 것이 아니고 RENAME TABLE tab_a TO tab_b 같이 테이블의 이름을 변경하는 경우 자동으로 획득함. RENAME TABLE 명령은 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정함. 또한 실시간으로 테이블을 바꿔야 하는 요건이 배치 프로그램에서 자주 발생함.

-- // 배치 프로그램에서 별도의 임시 테이블(rank_new)에 서비스용 랭킹 데이터를 생성

-- // 랭킹 배치가 완료되면 현재 서비스용 랭킹 테이블(rank)을 rank_backup으로 백업하고
-- // 새로 만들어진 랭킹 테이블(rank_new)을 서비스용으로 대체하고자 하는 경우
mysql> RENAME TABLE rank TO rank_backup , rank_new TO rank;

-- 위 문장을 2개로 나눠서 실행하면
-- 아주 짧은 시간이지만 rank 테이블이 존재하지 않는 순간이 생기며
-- 그 순간에 실행되는 쿼리는 "Table not found 'rank'" 오류 발생

mysql> RENAME TABLE rank TO rank_backup;
mysql> RENAME TABLE rank_new TO rank;

5.3 InnoDB 스토리지 엔진 잠금

• InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재함.
• 예전 버전의 MySQL 서버에서 InnoDB의 잠금 정보를 진단할 수 있는 도구는 lock_monitor(innodb_lock_monitor라는 이름의 InnoDB 테이블을 생성해서 InnoDB의 잠금 정보를 덤프하는 방법)와 SHOW ENGINE INNODB STATUS 명령이었음.
• 최근 버전에서는 InnoDB의 트랜잭션과 잠금, 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됨.
• MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_ TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면, 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있음.
• 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수도 있음.
• Performance Schema를 이용해 InnoDB 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됨.

5.3.1 InnoDB 스토리지 엔진의 잠금

• InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공함. 잠금 정보가 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업되는 경우(락 에스컬레이션)는 없음. InnoDB 스토리지 엔진에서는 레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재함.

5.3.1.1 레코드 락

• 레코드 락(Record lock, Record only lock): 레코드 자체만을 잠그는 것. 다른 상용 DBMS의 레코드 락과 동일한 역할을 함.
• InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠금. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정함.
• InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 이어서 설명할 넥스트 키 락(Next key lock) 또는 갭 락(Gap lock)을 사용하지만 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭(Gap, 간격)에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 걺.

5.3.1.2 갭 락

• 갭 락(Gap lock): 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것임. 갭 락은 그 자체보다는 넥스트 키 락의 일부로 자주 사용됨.

5.3.1.3 넥스트 키 락

• 넥스트 키 락(Next key lock): 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금.
• STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 함. 또한 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화되면(0으로 설정되면) 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸림.
• InnoDB의 갭 락이나 넥스트키 락의 주 목적은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것임. 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생하므로, 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋음.

5.3.1.4 자동 증가 락

• MySQL에서는 자동 증가하는 숫자 값을 추출(채번)하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공함. AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 함.
• InnoDB 스토리지 엔진에 서는 이를 위해 내부적으로 AUTO_INCREMENT 락(Auto increment lock)이라고 하는 테이블 수준의 잠금을 사용함.
• INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE나 DELETE 등의 쿼리에서는 걸리지 않음.
• InnoDB의 다른 잠금(레코드 락이나 넥스트 키락)과는 달리 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제됨.
• 테이블에 단 하나만 존재하기 때문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO_INCREMENT 락을 걸면 나머지 쿼리는 AUTO_INCREMENT 락을 기다림.
  AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없음. 이전까지의 설명은 MySQL 5.0 이하 버전에서 사용되던 방식임.
• MySQL 5.1 이상부터는 innodb_autoinc_ lock_mode라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있음.
  • innodb_autoinc_lock_mode=0: MySQL 5.0과 동일한 잠금 방식으로 모든 INSERT 문장은 자동 증가 락을 사용함.
  • innodb_autoinc_lock_mode=1: 단순히 한 건 또는 여러 건의 레코드를 INSERT하는 SQL 중에서 MySQL 서버가 INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락(Auto increment lock)을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리함. 연속 모드(Consecutive mode)라고도 함.
  • innodb_autoinc_lock_mode=2: InnoDB 스토리지 엔진은 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용함. 인터리빙 모드(Interleaved mode)라고도 함.
• MySQL 5.7 버전까지는 innodb_autoinc_lock_mode의 기본값이 1이었지만, MySQL 8.0 버전부터는 innodb_autoinc_lock_mode의 기본값이 2로 바뀜. MySQL 8.0부터 바이너리 로그 포맷이 STATEMENT 가 아니라 ROW 포맷이 기본값이 되었기 때문.

5.3.2 인덱스와 잠금

• InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리됨. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 함.

-- // 예제 데이터베이스의 employees 테이블에는 아래와 같이 first_name 칼럼만
-- // 멤버로 담긴 ix_firstname이라는 인덱스가 준비돼 있다.
-- // KEY ix_firstname (first_name)
-- // employees 테이블에서 first_name='Georgi'인 사원은 전체 253명이 있으며,
-- // first_name='Georgi'이고 last_name='Klassen'인 사원은 딱 1명만 있는 것을 아래 쿼리로
-- // 확인할 수 있다.
mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
+----------+
| 253      |
+----------+

mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
+----------+
| 1        |
+----------+

-- // employees 테이블에서 first_name='Georgi'이고 last_name='Klassen'인 사원의
-- // 입사 일자를 오늘로 변경하는 쿼리를 실행해보자.
mysql> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

• UPDATE 문장이 실행되면 1건의 레코드가 업데이트됨. 1건의 업데이트를 위해 몇 개의 레코드에 락을 걸어야 할까? 이 UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi'이며, last_name 칼럼은 인덱스에 없기 때문에 first_name='Georgi'인 레코드 253건의 레코드가 모두 잠김.
• UPDATE 문장을 위해 적절히 인덱스가 준비되어 있지 않으면, 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 UPDATE 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생할 것임.

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

• InnoDB 스토리지 엔진을 사용하는 테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 복잡함.
  • 테이블 잠금에서는 잠금의 대상이 테이블 자체이므로 쉽게 문제의 원인이 발견되고 해결될수 있음.
  • 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않음.
• 예전 버전의 MySQL 서버에서는 레코드 잠금에 대한 메타 정보(딕셔너리 테이블)를 제공하지 않기 때문에 어려움. MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해 보면 잠금과 잠금 대기를 바로 확인할 수 있음.
• 강제로 잠금을 해제하려면 KILL 명령을 이용해 MySQL 서버의 프로세스를 강제로 종료하면 됨.

5.4 MySQL의 격리 수준

• 트랜잭션의 격리 수준(isolation level): 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것.
• "READ UNCOMMITTED", "READ COMMITTED", "REPEATABLE READ", "SERIALIZABLE"의 4가지로 나뉨.
• “DIRTY READ”라고도 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서는 거의 사용하지 않고, SERIALIZABLE 또한 동시성이 중요한 데이터베이 스에서는 거의 사용되지 않음.
• 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시 처리 성능도 떨어짐. SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않음.
• 데이터베이스의 격리 수준을 이야기하면 같이 언급되는 3가지 부정합의 문제점이 있음. 이 3가지는 격리 수준의 레벨에 따라 발생할 수도 있고 발생하지 않을 수도 있음.

	DIRTY READ	NON-REPEATABLE  READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생 (InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

• 일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITTED와 REPEATABLE READ 중 하나를 사용함. 오라클 같은 DBMS에서는 주로 READ COMMITTED 수준을 많이 사용하며, MySQL에서는 REPEATABLE READ를 주로 사용함.

5.4.1 READ UNCOMMITTED

• READ UNCOMMITTED 격리 수준: 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보임.
• 더티 리드(Dirty read): 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상. 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED임.
• 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 애플리케이션 개발자와 사용자를 상당히 혼란스럽게 만듦. 더티 리드를 유발하는 READ UNCOMMITTED는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준임.
• 따라서 MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장함.

5.4.2 READ COMMITTED

• READ COMMITTED: 오라클 DBMS에서 기본으로 사용되는 격리 수준. 온라인 서비스에서 가장 많이 선택됨.
• 더티 리드(Dirty read) 같은 현상은 발생하지 않음. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문.
• READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없음.
• READ COMMITTED 격리 수준에서도 "NON-REPEATABLE READ"("REPEATABLE READ"가 불가능하다)라는 부정합의 문제가 있음.
• 가끔 사용자 중에서 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 없이 실행되는 SELECT 문장의 차이를 혼동하는 경우가 있음.
  • READ COMMITTED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없음.
  • REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동함. START TRANSACTION(또는 BEGIN) 명령으로 트랜잭션을 시작한 상태에서 동일한 쿼리를 반복해서 실행해도 동일한 결과만 보게 됨(아무리 다른 트랜잭션에서 그 데이터를 변경하고 COMMIT을 실행한다고 하더라).
  • 이런 문제로 데이터의 정합성이 깨지고 그로 인해 애플리케이션에 버그가 발생하면 찾아내기가 쉽지 않음.

5.4.3 REPEATABLE READ

• REPEATABLE READ: MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준. 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 함.
• 이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 "NON-REPEATABLE READ" 부정합이 발생하지 않음.
• InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경함. 이러한 변경 방식을 MVCC라고 함.
• REPEATABLE READ는 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장힘. READ COMMITTED도 MVCC를 이용해 COMMIT되기 전의 데이터를 보여줌.
• REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐임.

5.4.4 SERIALIZABLE

• SERIALIZABLE: 가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준. 동시 처리 성능도 다른 트랜 잭션 격리 수준보다 떨어짐.
• InnoDB 테이블에서 기본적으로 순수한 SELECT 작업(INSERT ... SELECT ... 또는 CREATE TABLE ... AS SELECT ...가 아닌)은 아무런 레코드 잠금도 설정하지 않고 실행됨. InnoDB 매뉴얼에서 자주 나타나는 "Non-locking consistent read(잠금이 필요 없는 일관된 읽기)" 라는 말이 이것을 의미함.
• 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 됨. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없음.
• SERIALIZABLE 격리 수준에서는 일반적인 DBMS에서 일어나는 "PHANTOM READ"라는 문제가 발생하지 않음. 하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락으로 인해 REPEATABLE READ 격리 수준에서도 이미 "PHANTOM READ"가 발생하지 않음.

참고자료

1) 인파_, "[MYSQL] 📚 트랜잭션(Transaction) 개념 & 사용 💯 완벽 정리", 2022.01.13, https://inpa.tistory.com/entry/MYSQL-📚-트랜잭션Transaction-이란-💯-정리
 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.