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

트랜잭션 : 작업의 완전성 보장

• 논리적인 작업 셋을 모두 완벽하게 처리
• 처리하지 못할 경우, 원 상태로 복구
  • 작업의 일부만 적용되는 현상(Partial update) X
• 데이터의 정합성을 보장하기 위한 기능

잠금 : 동시성을 제어하기 위한 기능

• 한 시점에서 하나의 커넥션만 자원 변경 가능

📌  트랜잭션

논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야 함을 보장

• 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다.

1) 처리 시작
	- 데이터베이스 커넥션 생성
	- 트랜잭션 시작
2) 로그인 여부 확인
3) 글쓰기 내용 오류 확인
4) 입력 내용 DBMS에 저장
5) 첨부 파일 정보 DBMS에 저장
6) 저장된 내용 또는 기타 정보 DBMS에서 조회
7) 게시글 등록에 대한 알림 메일 발송
8) 알림 메일 발송 이력을 DBMS에 저장
	- 트랜잭션 종료
	- 커넥션 반납

• 프로그램 코드에서 트랜잭션의 범위는 최소화해야 한다.
  • DB 커넥션 개수는 제한적이어서 소유 시간이 길수록 여유 커넥션의 수는 줄어든다.
  • 메일 전송이나 원격 서버와 통신하는 등의 작업은 트랜잭션 내에서 제거하는게 좋다.

1) 처리 시작
2) 로그인 여부 확인
3) 글쓰기 내용 오류 확인
4) 입력 내용 DBMS에 저장
	- 데이터베이스 커넥션 생성
	- 트랜잭션 시작
5) 첨부 파일 정보 DBMS에 저장
	- 트랜잭션 종료
6) 저장된 내용 또는 기타 정보 DBMS에서 조회
7) 게시글 등록에 대한 알림 메일 발송
	- 트랜잭션 시작
8) 알림 메일 발송 이력을 DBMS에 저장
	- 트랜잭션 종료
	- 커넥션 반납

📌  MySQL 엔진의 잠금

• MySQL에서 사용되는 잠금
  • 스토리지 엔진 레벨
    • 스토리지 엔진 간 상호 영향 X
  • MySQL 엔진 레벨
    • MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분
    • 모든 스토리지 엔진에 영향
    • 테이블 락, 메타데이터 락, 네임드 락

글로벌 락

• MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
  • MySQL 서버 전체
• SELECT를 제외한 대부분의 DDL, DML 문장이 대기
• 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향
• InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터를 위해 모든 데이터 변경 작업을 멈출 필요 X ⇒ 더 가벼운 글로벌 락 필요성

백업 락

• MySQL 8.0부터 백업 툴들의 안정적 실행을 위해 백업 락 도입
• 테이블의 스키마나 사용자의 인증 관련 정보 변경 불가
  • 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
  • REPAIR TABLE과 OPTIMIZE TABLE 명령
  • 사용자 관리 및 비밀번호 변경
• 일반적인 테이블의 데이터 변경은 허용
• 백업은 소스 서버와 레플리카 서버 중 레플리카 서버에서 진행

테이블 락

• 개별 테이블 단위로 설정되는 잠금
• 묵시적 테이블 락
  • 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금 해제
  • 쿼리가 실행되는 동안 자동으로 획득하였다가 쿼리가 완료된 후 자동 해제
  • InnoDB 테이블의 경우, 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 데이터 변경 쿼리로 인해 묵시적 테이블 락이 설정되지 않음 (DDL의 경우에만 테이블 락 O)

네임드 락

• 임의의 문자열에 대해 잠금 설정
  • 사용자가 지정한 문자열에 대해 획득하고 해제하는 잠금
• 여러 클라이언트가 상호 동기화를 처리해야 할 때 이용
  • DB 서버 1대에 5대의 웹 서버가 접속해서 서비스하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화하는 상황
  • 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리 실행

메타데이터 락

• 데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금
• 명시적으로 획득하거나 해제할 수 없고 자동으로 획득하는 잠금
• 양이 너무 많다면, 프라이머리 키 범위별로 나눠 여러 개의 스레드로 복사하는 것이 좋다.

📌  InnoDB 스토리지 엔진 잠금

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

InnoDB 스토리지 엔진의 잠금

• 레코드 기반의 잠금 기능
• 상당히 작은 공간으로 잠금 정보가 관리되기 때문에 레코드 락이 페이지 락/테이블 락으로 레벨업되는 경우는 없다.

레코드 락

• 레코드 자체만을 잠금
• 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
  • 인덱스가 없더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금 설정

갭 락

• 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠금
• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어

넥스트 키 락

• 레코드 락 + 갭 락
• 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장
• 넥스트 키락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생
  • 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락 줄이는 것 추천

자동 증가 락

• AUTO_INCREMENT시, 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가지게하는 테이블 수준의 잠금
• 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 해제

인덱스와 잠금

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

UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';

# first_name이 Georgi인 사람은 253명, first_name이 Georgi고 last_name이 Klassen인 사람은 1명이다.

• 1건의 업데이트를 위해 253건의 레코드를 잠근다.
• 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE 작업 수행
• MySQL의 InnoDB에서 인덱스 설계는 중요하다.

📌  MySQL의 격리 수준

트랜잭션 격리 수준 : 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지 결정

• READ UNCOMMITTED는 일반적인 데이터베이스에서 사용 X
• SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서 거의 사용 X
• 뒤로 갈수록 격리 정도가 높아지며, 동시 처리 성능이 떨어진다.
• SERIALIZABLE이 아니라면 크게 성능의 개선이나 저하는 발생 X
• MySQL에서는 REPEATABLE READ를 주로 사용

READ UNCOMITTED

• 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부와 상관없이 다른 트랜잭션에서 보인다.

• 사용자 A가 처리 도중 알 수 없는 문제가 발생해 INSERT된 내용을 롤백한다고 하더라도 사용자 B는 Lara가 정상적인 사원이라고 생각하고 처리
• DIRTY READ
  • 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상

READ COMMITTED

• 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회 가능
• DIRTY READ X

• 사용자 B의 SELECT 쿼리 결과는 언두 영역에 백업된 레코드에서 가져온다.
• 사용자 A가 변경된 내용을 커밋하면 그때부터는 다른 트랜잭션에서도 Toto를 참조할 수 있다.
• NON-REPEATABLE READ 라는 부정합 문제 존재

• 사용자 B는 하나의 트랜잭션 내에서 SELECT 쿼리의 결과가 다르다.
  • REPEATABLE READ 정합성에 어긋남
• READ COMMITED 격리 수준에서는 트랜잭션 내 SELECT와 외부 SELECT의 차이는 없음

REPEATABLE READ

• MySQL InnoDB 스토리지 엔진의 기본 격리 수준
• MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보장
• READ COMMITTED와의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가느냐의 차이다.
  • REPEATABLE READ에서는 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다.
  • 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존되어야 한다.

• 사용자 B는 A 트랜잭션의 변경 전후 각각 SELECT 했는데 결과는 같다.
• 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경한 것만 보이게 된다.
• 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수 있다.
• PHANTOM READ 부정합 발생

• SLECT … FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다.
  • 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다.

SERIALIZABLE

• 읽기 작업도 공유 잠금을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경할 수 없다.
• 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다.
• InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않는다.