[Real MySQL 8.0 1] 05 트랜잭션과 잠금

1. 트랜잭션

트랜잭션

• 작업의 완전성 보장
• 논리적인 작업 셋 모두 완벽하게 처리 OR 처리 못할 경우에는 원 상태로 복구
  => 작업의 일부만 적용되는 현상(Partial update)이 발생하지 않게 만들어주는 기능

 

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

ex. 하나의 회원 정보 레코드 -> 여러 커넥션에서 동시에 변경하려고 함

잠금 X -> 하나의 데이터를 여러 커넥션에서 동시에 변경할 수 있게 됨

=> 해당 레코드 값은 예측할 수 없는 상태 됨

 

잠금: 여러 커넥션에서 동시에 동일한 자원(레코드/테이블) 요청 -> 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할

격리 수준: 하나의 트랜잭션 내에서 or 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨 

---

1) MySQL에서의 트랜잭션

트랜잭션

• 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념 X
• 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계 X
  • 논리적인 작업 셋 자체가 100% 적용(`COMMIT`을 실행했을 때)
  • 아무것도 적용되지 않아야(`ROLLBACK` or 트랜잭션을 `ROLLBACK` 시키는 오류 발생했을 때) 함

 

InnoDB 테이블 vs. MyISAM 테이블

테이블에 레코드 1건씩 저장 

CREATE TABLE tab_myisam (
	fdpk INT NOT NULL, 
    PRIMARY KEY (fdpk)
) ENGINE=MyISAM;

INSERT INTO tab_myisam (fdpk) VALUES (3);

CREATE TABLE tab_innodb (
	fdpk INT NOT NULL, 
    PRIMARY KEY (fdpk)
) ENGINE=INNODB;

INSERT INTO tab_innodb (fdpk) VALUES (3);

 

`AUTO-COMMIT` 모드에서 각각 쿼리 실행

-- AUTO-COMMIT 활성화
SET autocommit=ON;

INSERT INTO tab_myisam (fdpk) VALUES (1),(2),(3);
INSERT INTO tab_innodb (fdpk) VALUES (1),(2),(3);

 

SELECT * FROM tab_myisam;
SELECT * FROM tab_innodb;

=> 두 `INSERT` 문장 보두 프라이머리 키 중복으로 실패

But, MyISAM 테이블에는 오류가 발생했음에도 '1', '2'가 `INSERT`된 상태로 남아 있음

=> MyISAM 테이블에 `INSERT` 문장이 실행되면서 차례로 '1', '2'를 저장하고, '3'을 저장하려는 순간 중복 키 오류

-> 이미 `INSERT`는 그대로 두고 쿼리 실행 종료

= 부분 업데이트(Partial Update) 

-> 정합성 맞추는데 어려움

 

MEMORY 스토리지 엔진 사용하는 테이블도 동일하게 작동

But, InnoDB는 쿼리 중 일부라도 오류 발생 -> 전체를 원 상태로 만든다는 트랜잭션 원칙대로 `INSERT` 실행하기 전 상태로 복구

---

2) 주의사항

꼭 필요한 최소의 코드에만 적용(~= DBMS의 커넥션)

== 프로그램 코드에서 트랜잭션의 범위 최소화

 

사용자가 게시판에 게시물을 작성 -> 저장 버튼 클릭했을 때 서버에서 처리하는 내용

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

 

DBMS의 트랜잭션 처리에 좋지 않은 영향을 미치는 부분 

• 데이터베이스의 커넥션 생성(or 커넥션 풀에서 가져오는)하는 코드를 1번과 2번 사이에 구현
  + `START TRANSACTION` 명령으로 트랜잭션 시작
  / 9번과 10번 사이에 트랜잭션을 `COMMIT`하고 커넥션을 종료(or 커넥션 풀로 반납)
  • 실제로 DBMS에서 데이터를 저장하는 작업(트랜잭션): 5번부터 시작
    => 2번과 3번, 4번의 절차가 아무리 빨리 처리돼도 DBMS의 트랜잭션에 포함시킬 필요 X
  • 데이터베이스 커넥션 개수 제한
    • 각 단위 프로그램 커넥션 소유하는 시간 ↑ -> 사용 가능한 여유 커넥션 개수 ↓
    • 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수 있음

• 더 큰 위험: 8번
  메일 전송/FTP 파일 전송 작업/네트워크 ~> 원격 서버와 통신하는 등 작업 => DBMS 트랜잭션 내에서 제거하는 것이 좋음
  
  • 프로그램이 실행되는 동안 메일 서버와 통신할 수 X 상황 발생 -> 웹 서버 + DBMS 서버까지 위험해지는 상황 발생
• DBMS의 작업이 크게 4가지
  • 사용자가 입력한 정보를 저장하는 5번과 6번 -> 하나의 트랜잭션
  • 7번 작업: 저장된 데이터의 단순 확인 및 조회 -> 트랜잭션에 포함될 필요 X
  • 9번 작업: 성격 다름 -> 이전 트랜잭션(5번과 6번)에 함께 묶지 않아도 됨 -> 별도 트랜잭션으로 분리

 

보완한 처리 절차

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

=> 프로그램의 코드가 데잍어베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화해야 함

+ 네트워크 작업이 있는 경우 반드시 트랜잭션에서 배제해야 함

---

2. MySQL 엔진의 잠금

잠금

• 스토리지 엔진 레벨: 스토리지 엔진 간 상호 영향 미치지 X
• MySQL 엔진 레벨: 모든 스토리지 엔진에 영향 미침 O
  • 테이블 락: 테이블 데이터 동기화
  • 메타데이터 락(Metadata Lock): 테이블의 구조 잠금
  • 네임드 락(Named Lock): 사용자의 필요에 맞게 사용할 수 있음

---

1) 글로벌 락(GLOBAL LOCK)

• `FLUSH TABLES WITH READ LOCK` 명령으로 획득할 수 있음
• MySQL에서 제공하는 잠금 중 가장 범위가 큼
• 한 세션에서 글로벌 락 획득
  , 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장/DML 문장 실행 -> 글로벌 락이 해제될 때까지 대기 상태
• 영향을 미치는 범위: MySQL 서버 전체, 작업 대상 테이블/데이터베이스 달라도 동일하게 영향 미침
• 여러 데이터베이스에 존재하는 MyISAM or MEMORY 테이블에 대해 mysqldump로 일관된 백업 받아야할 때 사용

주의

`FLUSH TABLES WITH READ LOCK` 명령은 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금
명령이 실행되기 전에 테이블이나 레코드에 쓰기 잠금을 거는 SQL 실행
-> 해당 테이블의 읽기 잠금을 걸기 위해 먼저 실행된 SQL, 그 트랜잭션이 완료될 때까지 기다려야 함

테이블에 읽기 잠금을 걸기 전에 먼저 테이블을 플러시 해야 함
-> 테이블에 실행 중인 모든 종류의 쿼리가 완료돼야 함
=> 장시간 `SELECT` 쿼리가 실행되고 있을 때 종료될 때까지 기다려야 함
-> 최악의 케이스로 실행되면 MySQL 서버의 모든 테이블에 대한 `INSERT`, `UPDATE`, `DELETE` 쿼리가 오랜 시간 동안 실행되지 못하고 기다릴 수 있음

모든 테이블에 영향 ↑ -> 가급적 사용 X
mysqldump 같은 백업 프로그램은 명령을 내부적으로 실행하고 백업할 때가 있음
-> 옵션에 따라 서버에 어떤 잠금을 걸게 되는지 확인하는 것이 좋음

 

MySQL 서버의 모든 변경 작업을 멈춤

But, 서버가 업그레이드되면서 MyISAM or MEMORY 스토리지 엔진보다 InnoDB 스토리지 엔진 사용이 일반화

InnoDB 스토리지 엔진: 트랜잭션 지원 -> 일관된 데이터 상태를 위해 모든 데이터 변경 작업 멈출 필요 X

조금 더 가벼운 글로벌 락의 필요성 생김 -> Xtrabackup or Enterprise Backup과 같은 백업 툴들의 안정적인 실행 위해 백업 락 도입

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

 

특정 세션에서 백업 락 획득 -> 테이블의 스키마 or 사용자의 인증 관련 정보 변경 X

• 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
• `REPAIR TABLE`과 `OPTIMIZE TABLE` 명령
• 사용자 관리 및 비밀번호 변경

But, 백업 락은 일반적인 테이블의 데이터 변경 허용 O

MySQL 서버 구성: 소스 서버(Source Server) + 레플리카 서버(Replica Server)

백업은 레플리카 서버에서 실행

But, 백업이 `FLUSH TABLES WITH READ LOCK` 명령 이용해 글로벌 학 획득 -> 복제는 백업 시간만큼 지연

백업 실행 중 소스 서버에 문제 -> 레플리카 서버의 데이터가 최신 상태가 될 때까지 서비스를 멈춰야 할 수도 있음

XtraBackup or Enterprise Backup 툴들은 모두 복제가 진행되는 상태에서도 일관된 백업 만들 수 있음

But, 툴이 실행되는 도중에 스키마 변경 실행 -> 백업 실패

 

=> 백업 락: 정상적으로 복제는 실행되지만, 백업의 실패를 막기 위해 DDL 명령 실행되면 복제 일시 중지

---

2) 테이블 락(TABLE LOCK)

• 개별 테이블 단위로 설정되는 잠금
• 명시적/묵시적으로 특정 테이블의 락을 획득할 수 있음
  • 명시적 `LOCK TALBES table_name [ READ | WRITE ]`
• MyISAM, InnoDB 스토리지 엔진 사용하는 테이블도 동일하게 설정할 수 있음
• 명시적으로 획득한 잠금 -> `UNLOCK TABLES` 명령으로 잠금을 반납(해제)할 수 있음
• 자주 사용 X
  • 글로벌 락과 동일하게 온라인 작업에 영향 ↑
• MyISAM or MEMORY 테이블에 데이터를 변경하는 쿼리 실행 -> 묵시적인 락 발생
  • 서버가 데이터가 변경되는 테이블에 잠금 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용
  • 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제
  • But, InnoDB 테이블 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공
    -> 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락 설정 X
  • => InnoDB 테이블에도 테이블 락 설정
    But, 대부분의 데이터 변경(DML) 쿼리에서는 무시, 스키마를 변경하는 쿼리(DDL) 경우에만 영향 미침

---

3) 네임드 락(Named Lock)

• `GET_LOCK()` 함수 이용 -> 임의의 문자열에 대해 잠금 설정할 수 있음
• 대상이 테이블/레코드/`AUTO_INCREMENT`와 같은 데이터베이스 객체 X 
• 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금
• 자주 사용 X
  • ex. 데이터베이스 서버 1대에 5대의 웹 서버 접속해서 서비스하는 상황
  • 여러 클라이언트가 상호 동기화 처리해야 할 때 사용(5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건)

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

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

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

-- 3개 함수 모두 정상적으로 락을 획득/해제 -> 1, 아니면 Null/0

• 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에서 유용하게 사용
  • ex. 배치 프로그램
  • 한꺼번에 많은 레코드를 변경하는 쿼리 -> 자주 데드락의 원인이 됨
    => 각 프로그램의 실행 시간을 분산 or 프로그램의 코드 수정해 데드락 최소화
    But, 간단 X 완전한 해결책 X
    => (해결) 동일 데이터를 변경 or 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리 실행

 

8.0 버전부터 네임드 락을 중첩해서 사용 O, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가

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

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

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

---

4) 메타데이터 락(Metadata Lock)

• 데이터베이스 객체(테이블/뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금
• 명시적으로 획득하거나 해제 X
  • `RENAME TABLE tab_a TO tab_b` 같이 테이블의 이름을 변경하는 경우 자동으로 획득
    • `RENAME TABLE`: 원본 이름과 변경될 이름 2개 모두 한꺼번에 잠금 설정 
• 실시간으로 테이블을 바꿔야 하는 요건이 배치 프로그램에서 자주 발생

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

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

 

하나의 RENAME TABLE 명령문에 2개의 `RENAME` 작업 한꺼번에 실행

-> 실제 애플리케이션에서 "Table not found 'rank"'같은 상황 발생시키지 않고 적용하는 것이 가능 

But, 2개로 나눠 실행하면 rank 테이블이 존재하지 않는 순간 생기고 그 순간에 실행되는 쿼리는 "Table not found 'rank"' 오류 발생

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

 

메타데이터 잠금과 InnoDB의 트랜잭션을 동시에 사용해야 하는 경우

ex. `INSERT`만 실행되는 로그 테이블 가정

웹 서버의 액세스(접근) 로그를 저장만 -> `UPDATE`, `DELETE`가 없음

CREATE TABLE access_log (
id BIGINT NOT NULL AUTO_INCREMENT,
client_id INT UNSIGNED,
access_dttm TIMESTAMP,
...
PRIMARY KEY(id)
);

 

테이블 구조를 변경해야 할 요건 발생

Online DDL 이용할 수 있지만 시간이 오래 걸리는 경우라면 로그 ↑, 누적된 Online DDL 버퍼의 크기 등 고민

+ MySQL 서버의 DDL은 단일 스레드로 작동 -> ↑ 시간 소모

=> (해결) 새로운 구조의 테이블을 생성

, 최근(1시간 직전 or 하루 전)의 데이터까지는 프라이머리 키인 id 값을 범위별로 나눠서  여러 개의 스레드로 빠르게 복사

-- 테이블의 압축 적용 -> KEY_BLOCK_SIZE=4 옵션 추가해 신규 테이블 생성 
CREATE TALBE access_log_new (
) KEY_BLOCK_SIZE=4;

-- 4개의 스레드를 이용해 id 범위별로 레코드를 신규 테이블로 복사
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 AND id<40000;

 

나머지 데이터는 트랜잭션과 테이블 잠금, `RENAME TABLE` 명령 -> 응용 프로그램의 중단 없이 실행할 수 있음

-- 트랜잭션을 autocommit 으로 실행(BEGIN or START TRANSACTION으로 실행 X)
SET autocommit=0;

-- 작업 대상 테이블 2개에 대해 테이블 쓰기 락을 획득
LOCK TABLES access_loc WRITE, access_log_new WRITE;

-- 남은 데이터를 복사
SELECT MAX(id) as @MAX_ID FROM access_log;
INSERT INTO access_log_new SELECT * FROM access_log WHERE pk>@MAX_ID;
COMMIT;

-- 새로운 테이블로 데이터 복사 완료 -> RENAME 명령으로 새로운 테이블을 서비스로 투입
RENAME TABLE access_log TO access_log_old, access_log_new TO access_log;
UNLOCK TABLES;

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

---

3. InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식 탑재

-> MyISAM보다 훨씬 뛰어난 동시성 처리 제공

But, 이원화된 잠금 처리 -> 잠금에 대한 정보는 MySQL 명령 이용해 접근하기 까다로움

 

• 예전 버전: `lock_monitor`, `SHOW ENGINE INNODB STATUS` 명령
  • `innodb_lock_monitor`라는 이름의 InnoDB 테이블 생성 -> InnoDB의 잠금 정보를 덤프하는 방법
• 최근 버전: InnoDB의 트랜잭션과 잠금, 잠금 대기 중인 트랜잭션의 목록을 조회하는 방법 도입
  • `information_schema` 데이터베이스에 존재하는 `INNODB_TRX`, `INNODB_LOCKS`, `INNODB_LOCK_WAITS`라는 테이블 조인해서 조회 
    • 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인, 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료
  • InnoDB의 잠금에 대한 모니터링 강화
    • `Performance Schema` 이용 -> InnoDB 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법 추가

---

1) InnoDB 스토리지 엔진의 잠금

레코드 기반의 잠금 기능 제공

잠금 정보가 상당히 작은 공간으로 관리 

=> 레코드 락이 페이지 락 or 테이블 락으로 레벨업되는 경우(락 에스컬레이션) X

 

레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락 존재

레코드 락(Record lock, Record only lock)

• 레코드 자체만을 잠그는 것
• 레코드 자체 X 인덱스의 레코드 잠금 O (-> 중요한 차이)
  • 보조 인덱스를 이용한 변경 작업: 넥스트 키 락 or 갭 락
  • 프라이머리 키 or 유니크 인덱스 의한 변경 작업: 갭 X 레코드 자체에서만 락 O
• 인덱스가 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스 이용해 잠금 설정

 

갭 락(Gap lock)

• 레코드 자체 X 레코드와 인접한 레코드 사이의 간격만을 잠그는 것
• 레코드와 레코드 사이의 간격에 새로운 레코드 생성(`INSERT`)되는 것을 제어하는 것
• 넥스트 키 락의 일부로 자주 사용

 

넥스트 키 락(Next key lock)

• 레코드 락 + 갭 락
• STATEMENT 포맷의 바이너리 로그를 사용하는 서버에서는 REPETABLE READ 격리 수준 사용해야 함
• `innodb_locks_unsafe_for_binlog` 시스템 변수 비활성화(0으로 설정)
  -> 변경을 위해 검색하는 레코드에 잠금
• 갭 락/넥스트 키 락 - 주목적: 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것
  • But, 갭 락/넥스트 키 락으로 인한 데드락 발생 or 다른 트랜잭션을 기다리게 만드는 일 자주 발생
  • 바이너리 로그 포맷 -> ROW 형태로 바꿈 => 갭 락/넥스트 키 락 ↓ 

 

자동 증가 락(Auto increment lock)

`AUTO_INCREMENT` 칼럼이 사용된 테이블에 동시에 여러 레코드 `INSERT`

-> 저장되는 각 레코드 중복 X 저장된 순서대로 증가하는 일련번호 값을 가져야 함

 

5.0 이하 버전

• 새로운 레코드를 저장하는 쿼리(`INSERT`, `REPLACE` 쿼리 문장)에서만 필요
  • `UPDATE`, `DELETE` 등의 쿼리 X
• 트랜잭션과 관계 X INSERT, REPLACE 문장에서 `AUTO_INCREMENT` 값을 가져오는 순간만 락이 걸렸다 즉시 해제
• 테이블에 단 하나만 존재
  • 2개의 INSERT 쿼리가 동시에 실행 -> 하나의 쿼리가 AUTO_INCREMENT 락 걸면 나머지 쿼리 기다려야 함
  • (`AUTO_INCREMENT` 칼럼에 명시적으로 값을 설정하더라도 자동 증가 락을 걸게 됨)
• 명시적으로 획득하고 해제하는 방법 X
  • 아주 ↓ 시간 동안 걸렸다가 해제되는 잠금 -> 대부분의 경우 문제 X

 

5.1 이상 버전 

`innodb_autoinc_lock_mode` 시스템 변수 이용 -> 자동 증가 락의 작동 방식 변경할 수 있음

• `innodb_autoinc_lock_mode=0`
  • 5.0과 동일한 방식으로 모든 INSERT 문장은 자동 증가 락 사용
• `innodb_autoinc_lock_mode=1`: 연속 설정 모드(Consecutive mode)
  (5.7 버전까지 기본)
  
  • 단순히 한 건 or 여러 건의 레코드 `INSERT` -> 레코드 건수 정확히 예측 O -> 자동 증가 락 사용 X, 래치(뮤텍스) 이용해 처리
    • 개선된 래치: 아주 ↓ 시간 동안만 락, 필요한 자동 증가 값 가져오면 즉시 해제
  • But, `INSERT ... SELECT` 같이 서버가 건수 (쿼리 실행하기 전) -> 예측 X -> 자동 증가 락 사용 O
    • `INSERT` 문장이 완료되기 전까지 자동 락 해제 X -> 다른 커넥션에서 `INSERT` 대기
    • 대량 `INSERT` 수행될 때 여러 개의 자동 증 값 한 번에 할당받아 `INSERT`되는 레코드에 사용
      -> 자동 증가 값 누락 X 연속되게 `INSERT`
      But, 한 번에 할당 받은 자동 증가 값이 남아서 사용 X -> 폐기
      => 대량 `INSERT` 문장 실행 이후에 `INSERT` 되는 레코드의 자동 증가 값 연속 X 누락한 값 발생할 수 있음
• `innodb_autoinc_lock_mode=2`: 인터리빙 모드(Interleaved mode)
  (8.0 버전부터 기본 - 로그 포맷 (STATEMENT -> ROW)
  • 자동 증가 락 X 경량화된 래치(뮤텍스) 사용
  • But, 하나의 `INSERT` 문장으로 `INSERT` 되는 레코드여도 연속된 자동 증가 값 보장 X
  • `INSERT ... SELECT` 대량 `INSERT` 문장이 실행되는 중에도 다른 커넥션에서 `INSERT` 수행 O -> 동시 처리 성능 ↑
    • But, 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장
    • STATEMENT 포맷의 바이너리 로그를 사용하는 복제: (소스 서버 != 레플리카 서버 자동 증가 값) 가능 O

---

2) 인덱스와 잠금

레코드 잠금 X 인덱스를 잠드는 방식 O

= 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락 걸어야 함

-- employees 테이블에 first_name 칼럼만 멤버로 담긴 ix_firstname 인덱스
-- KEY ix_firstname(first_name)

-- first_name='Georgi': 253명
SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
-- first_name='Georgi', last_name='Klassen': 1명
SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';

-- 입사 일자 오늘로 변경하는 쿼리
UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

Q. 1건의 업데이트 -> 몇 개의 레코드에 락을 걸어야 할까?
A. UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건: first_name='Georgi'
last_name은 인덱스 X -> 253건의 레코드가 모두 잠김

 

`UPDATE` 문장을 위해 적절히 인덱스 준비 X -> 각 클라이언트 간 동시성 ↓

=> 한 세션에서 `UPDATE` 작업을 하는 중 다른 클라이언트는 그 테이블 업데이트 X 대기

---

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

복잡도: 테이블의 레코드 수준 잠금 > 테이블 수준의 잠금

• 테이블 잠금: 테이블 자체에 잠금 -> 쉽게 문제의 원인 발견, 해결
• 레코드 수준의 잠금: 테이블의 레코드에 각각 잠금 -> 레코드 자주 사용 X -> 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견 X

 

5.1 부터 레코드 잠금과 잠금 대기에 대한 조회 가능

강제로 잠금 해제: `KILL` 명령 -> MySQL 서버의 프로세스 강제 종료

 

시나리오 가정

• 5.1 버전부터 `information_schema`라는 DB에 `INNODB_TRX`, `INNODB_LOCKS`, `INNODB_WAITS` 테이블 ~> 확인 가능
• 8.0 버전부터 `information_schema`의 정보 조금식 제거(Deprecated)
  , `performace_schema`의 `data_locks`, `data_lock_waits` 테이블로 대체되고 있음

UPDATE 명령 3개가 실행된 상태의 프로세스 목록 조회

17번 스레드: 아무것도 하지 않고 있지만, 트랜잭션을 시작하고 `UPDATE` 명령이 실행 완료 된 것 
But, `COMMIT` 실행 X 상태 -> 업데이트한 레코드의 잠금을 그대로 가지고 있는 상태
18번 스레드: 그 다음 `UPDATE` -> 19번 스레드: `UPDATE`
=> 18번과 19번 스레드: 잠금 대기 -> 아직 `UPDATE` 실행 중인 것으로 표시

`performance_schema`의 `data_locks` 테이블과 `data_lock_waits` 테이블 조인 -> 잠금 대기 순서 확인

현재 대기 중인 스레드: 18번과 19번
18번 스레드: 17번 스레드 기다리고 있음/ 19번 스레드: 17, 8 번 스레드 기다리고 있음
=> 잠금 대기 큐의 내용 그대로 보여줌
17번: 가지고 있는 잠금 해제 -> (18번 스레드: 잠금 획득 -> `UPDATE` -> 잠금 해제) -> 19번 스레드: `UPDATE` 실행

17번 스레드가 어떤 잠금을 가지고 있는지 상세히 확인

employees 테이블에 대해 IX 잠금(Internal Exclusive)
employees 테이블의 특정 레코드에 대해 쓰기 잠금 가지고 있음
`REC_NOT_GAP`: 레코드 잠금은 갭이 포함되지 X 순수 레코드에 대해서만 잠금 가지고 있음

 

17번 스레드 강제 종료 -> 나머지 `UPDATE` 명령 진행 => 잠금 경합 끝

KILL 17;

---

4. MySQL의 격리 수준

격리 수준(isolation level)

• 여러 트랜잭션이 동시에 처리 -> 특정 트랜잭션이 다른 트랜잭션에서 변경/조회 데이터를 볼 수 있게 허용할지/말지 결정하는 것
• READ UNCOMMITED/ READ COMMITED/ REPEATABLE READ/ SERIALIZABLE
  • READ UNCOMMITED(DIRTY READ)/ SERIALIZABLE 거의 사용 X
  • 뒤로 갈수록 격리(고립) 정도 ↑/ 동시 처리 성능 ↓
    • SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하 발생 X

 

격리 수준에 따른 부정합 문제

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITED	O	O	O
READ COMMITED	X	O	O
REPEATABLE READ	X	X	O (InnoDB는 X)
SERIALIZABLE	X	X	X

 

1) READ UNCOMMITED

트랜잭션의 변경 내용 -> `COMMIT`/`ROLLBACK` 여부 상관 X 다른 트랜잭션에서 보임

 

다른 트랜잭션 사용자 B가 실행하는 `SELECT` 쿼리의 결과가 어떤 영향을 미치는지 

 

A: emp_no: 500000, first_name='Lara'인 새로운 사원 `INSERT`
B: 변경된 내용 커밋 전 emp_no=500000 사원 검색 But, 커밋되지 X 상태에서도 조회 O

(문제) A: 처리 도중 알 수 없는 문제 발생 -> `INSERT` 롤백 But, B: Lara 정상적인 사원으로 생각하고 계속 처리

 

=> 더티 리드(Dirty read): 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상

-> 데이터가 나타났다가 사라졌다 하는 현상 초래 

 

=> 권장: 최소한 READ COMMITED 이상의 격리 수준 사용할 것

---

2) READ COMMITED

• 오라클 DBMS에서 기본으로 사용하는 격리 수준
• 온라인 서비스에서 가장 ↑ 선택
• 어떤 트랜잭션에서 데이터 변경했더라도 `COMMIT`이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있음
  • 더티 리드 현상 발생 X

 

사용자 A가 변경한 내용이 사용자 B에게 어떻게 조회되는지

A: emp_no=500000인 사원의 first_name='Lara' -> 'Toto'
=> Toto는 즉시 기록, Lara는 언두 영역으로 백업
B: 커밋 전 emp_no=500000인 사원 SELECT -> Lara로 조회 (employees 테이블 X 언두 영역에 백업된 레코드에서 가져옴)
/ 커밋 후 -> Toto 참조할 수 있게 됨

 

NON-REPEATABLE READ(REPETABLE READ가 불가능)라는 부정합 문제

 

NON-REPEATABLE READ 왜 발생하고 어떤 문제를 만들어낼 수 있는지

B: `BEGIN` 명령으로 트랜잭션 시작, first_name='Toto'인 사용자 검색 -> 일치 결과 X
A: emp_no=500000인 사원 이름 Toto로 변경, 커밋 실행 -> B: 검색 => 1건 조회

 

=> 하나의 트랜잭션 내에 똑같은 `SELECT` 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋

 

하나의 트랜잭션에서 동일 데이터 여러 번 읽고 변경하는 작업 -> 금전적인 처리와 연결되면 문제가 될 수 있음

ex. 다른 트랜잭션에서 입금, 출금 처리 계속 진행될 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회

-> REPEATABLE READ 보장 X => 총합을 계산하는 `SELECT` 쿼리는 실행될 때마다 다른 결과 가져옴

 

사용 중인 트랜잭션의 격리 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지 정확히 예측할 수 있어야 함

-> 각 트랜잭션의 격리 수준이 어떻게 작동하는지 알아야 함

 

트랜잭션 내에서 실행되는 `SELECT` 문장 vs. 트랜잭션 X 실행되는 `SELECT` 문장

• READ COMMITED 격리 수준: 트랜잭션 내에서 차이 별로 X
• REPEATABLE READ 격리 수준: 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동
  • `START TRANSACTION`(or `BEGIN`) 명령으로 트랜잭션을 시작한 상태에서 반복 실행하면 동일한 결과 보게 됨 
    (다른 트랜잭션에서 그 데이터 변경하고 `COMMIT` 실행한다고 해도)

---

3) REPEATABLE READ

• MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준
  • 바이너리 로그 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 함
• READ COMMITED 격리 수준에서 발생하는 NON-REPEATABLE READ 부정합 발생 X
• InnoDB 스토리지 엔진 - MVCC(Multi Version Concurrency Control)
  • 트랜잭션이 `ROLLBACK`될 가능성에 대비 -> 변경되기 전 레코드를 언두(Undo) 공간에 백업, 실제 레코드 값 변경
  • 언두 영역에 백업된 이전 데이터 이용 -> 동일 트랜잭션 내에서는 동일한 결과 보여줄 수 있게 보장
  • READ COMMITED도 MVCC 이용 -> `COMMIT`되기 전의 데이터 보여줌 
    • 언두 영역에 백업된 레코드의 여러 버전 중 몇 번째 이전 버전까지 찾아 들어가야 하느냐
  • 고유한 트랜잭션 번호(순차적으로 증가하는 값) 가짐,
    언두 영역에 백업된 레코드: 변경을 발생시킨 트랜잭션 번호 포함, InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제
    • 실행 중인 트랜잭션 중 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터 삭제 X
      (가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터 필요 X)
    • 특정 트랜잭션 번호의 구간 내 백업된 언두 데이터가 보존돼야 함

 

REPEATABLE READ 격리 수준이 작동하는 방식

• employees 테이블은 번호가 6인 트랜잭션에 의해 `INSERT`됐다고 가정
• A: emp_no=500000인 사원의 이름을 변경하는 과정에서 B가 emp_no=500000인 사원을 `SELECT`할 때 어떤 과정을 거쳐서 처리되는지

A: 트랜잭션 번호 12 / B: 트랜잭션 번호 10

A: 사원의 이름 Toto로 변경하고 커밋 수행 
But, B: emp_no=500000인 사원을 A 트랜잭션 변경 전후 각각 한 번씩 `SELECT` -> Lara
=> B가 BEGIN 명령으로 트랜잭션 시작하면서 10번 번호 부여받은 뒤 10번 트랜잭션 안에서 실행되는 모든 `SELECT` 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보게 됨

 

한 사용자가 `BEGIN`으로 트랜잭션을 시작하고 장시간 트랜잭션 종료 X -> 언두 영역이 백업된 데이터로 무한정 커질 수 있음

-> 언두에 백업된 레코드 ↑ -> MySQL 서버의 처리 성능 ↓

 

REPEATABLE READ 격리 수준에서 발생하는 부정합

사용자 A가 employees 테이블에 `INSERT`를 실행하는 도중에 사용자 B가 `SELECT ... FOR UPDATE` 쿼리로 employees 테이블을 조회했을 때 어떤 결과를 가져오는지

B: BEGIN 명령으로 트랜잭션을 시작한 후 SELECT 수행
=> 2번의 SELECT 쿼리 똑같아야 함 But, 결과가 다름

 

PHANTOM READ(PHANTOM ROW): 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안보였다 하는 현상

 

`SELECT ... FOR UPDATE` 쿼리는 `SELECT` 하는 레코드에 쓰기 잠금 걸어야 하는데, 언두 레코드에 잠금 X

=> `SELECT ... FOR UPDATE` or `SELECT ... LOCK IN SHARE MODE`로 조회하는 레코드

: 언두 영역의 변경 전 데이터 X 현재 영역의 레코드 값을 가져옴

---

4) SERIALIZABLE

• 가장 단순한 격리, 가장 엄격한 격리 수준
• 동시 처리 성능 다른 트랜잭션 격리 수준보다 ↓
• 순수한 `SELECT` 작업(`INSERT ... SELECT ...` or `CREATE TABLE ... AS SELECT ...`가 아닌): 레코드 잠금 설정 X 실행
  • Non-locking consistent read(잠금이 필요 없는 일관된 읽기)
  • 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 함 + 다른 트랜잭션은 그러한 레코드 변경 X
    == 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서 절대 접근 X
• 일반적인 DBMS에서 일어나는 PHANTOM READ 문제 발생 X
  • But, InnoDB 스토리지 엔진에서는 갭 락, 넥스트 키 락 -> REPEATBALE READ 격리 수준에서도 PHANTOM READ 발생 X

---

출처

GitHub - wikibook/realmysql80: 《Real MySQL 8.0》 예제 파일