서론
1. 트랜잭션
- 작업의 완전성 보장
- 논리적인 작업 셋을 모두 완벽하게 처리 or 처리하지 못할 경우 원 상태로 복구해 작업의 일부만 적용되는 현상 방지
- 데이터의 정합성을 보장하기 위한 기능
2. 잠금(Lock)
- 동시성을 제어하기 위한 기능
- 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블) 요청할 경우 => 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해 주는 역할
3. 격리 수준
- 하나의 트랜잭션 내에서 or 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지 결정하는 레벨 의미

5.1 트랜잭션

트랜잭션의 이점에 대해 살펴보자 (MyISAM VS InnoDB)

5.1.1 MySQL에서의 트랜잭션

트랜잭션

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

InnoDB의 경우 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙에 의해 부분 업데이트 현상이 발생하는 걸 막아 줌 => 트랜잭션이란 그만큼 애플리케이션 개발에서 고민해야 할 문제를 줄여 주는 아주 필수적인 DBMS 기능임

5.1.2 주의사항

트랜잭션은 꼭 필요한 최소의 코드에만 적용하는 것이 좋음
=> 프로그램 코드에서 트랜잭션의 범위를 최소화하라는 의미

트랜잭션이 너무 길면 사용 가능한 여유 connection 수가 줄어든다

프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램 범위를 최소화해야 함
또한 프로그램의 코드에서 라인 수는 한두 줄이라고 해도 네트워크 작업이 있는 경우 반드시 트랜잭션에서 배제해야 함 <- 이런 실수로 인해 DBMS 서버가 높은 부하 상태로 빠지거나 위험한 상태에 빠지는 경우가 빈번히 발생

5.2 MySQL 엔진의 잠금

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

5.2.1 글로벌 락

글로벌 락(GLOBAL LOCK)

• FLUSH TABLES WITH READ LOCK 명령으로 획득 가능
• MySQL 제공 잠금 중 가장 범위가 큼 => MySQL 서버 전체에 영향 미침
• 작업 대상 테이블 or 데이터베이스가 다르더라도 동일하게 영향 미침
• 일단 한 세션에서 글로벌 락 획득 => 다른 세션에서 SELECT를 제외한 대부분의 DDL or DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남음
  => InnoDB가 일반화되면서 InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없어짐

백업 락

조금 더 가벼운 글로벌 락의 필요성이 생김 => MySQL 8.0부터 Xtrabackup이나 Enterprise Backup 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됨

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

특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 됨

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

백업 락은 일반적인 테이블의 데이터 변경은 허용됨
일반적인 MySQL 서버의 구성은 소스 서버와 레플리카 서버로 구성
주로 백업은 레플리카에서
-> 백업이 FLUSH TABLES WITH READ LOCK 명령을 이용해 글로벌 락 획득 => 복제는 백업 시간만큼 지연
레플리카 서버에서 백업을 실행하는 도중 소스 서버에 문제가 생기면 레플리카 서버 데이터가 최신 상태가 될 때까지 서비스를 멈춰야 할 수 있음

• XtraBackup이나 Enterprise Backup 툴들은 모두 복제가 진행되는 상황에서도 일관된 백업을 만들 수 있음
  -> 근데 툴 실행 중에 스키마 변경이 실행되면 백업이 실패됨
  MySQL 서버의 백업 락은 이런 목적으로 도입되었으며, 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 함

5.2.2 테이블 락

테이블 락(Table Lock)

• 개별 테이블 단위로 설정되는 잠금
• 명시적 or 묵시적으로 특정 테이블의 락 획득 가능

명시적

• 명시적으로 LOCK TABLES table_name [ READ | WRITE ] 명령으로 특정 테이블 락 획득 가능
• UNLOCK TABLES 명령으로 잠금 반납(해제) 가능
• 명시적으로 테이블을 잠그는 건 온라인 작업에 상당한 영향을 미치기 때문에, 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없음

묵시적

• MyISAM, MEMORY 테이블에 데이터 변경 쿼리 실행 시 발생 => MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후 즉시 잠금을 해제하는 형태로 사용
  => 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득했다가 쿼리가 완료된 후 자동 해제됨
  InnoDB 테이블의 경우 스토리지 엔진 차원에서 레콛 기반 잠금을 제공해 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않음
• InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 DML 쿼리에서는 무시되고 DDL의 경우에만 영향을 미침

5.2.3 네임드 락

네임드 락(Named Lock)

• GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금 설정 가능
• 대상이 테이블, 레코드, 데이터베이스 객체가 아니라는 점이 특징
• 네임드 락은 단순하 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금
• 네임드 락은 자주 사용되지 않음
• Ex) 데이터베이스 서버 1대에 5대의 웹 서버가 접속해서 서비스해야 하는 상황에 5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 어떤 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락 사용
• 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있음
• 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되는데 => 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 해결 가능

5.2.4 메테데이터 락

메타데이터 락(Metadata Lock)

• 데이터베이스 객체(대표적이로 테이블이나 뷰)의 이름이나 구조를 변경하는 경우 획득하는 잠금
• 명시적으로 획득 or 해제 불가 -> "RENAME TABLE tab_a TO tab_b"처럼 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금
• RENAME TABLE 명령의 경우 원본 이름, 변경될 이름 두 개 모두 함꺼번에 잠금을 설정
• Ex) 실시간으로 테이블을 바꿔야하는 요건이 배치 프로그램에서 발생할 때

1. 배치 프로그램에서 별도의 임시 테이블(rank_new)에 서비스용 랭킹 데이터를 생성
2. 랭킹 배치 완료 => 현재 서비스용 랭킹 테이블(rank)을 rank_backup으로 백업하고
3. 새로 만들어진 랭킹 테이블(rank_new)를 시비스용으로 대체하고자 하는 경우
mysql> RENAME TABLE rank TO rank_backup, rank_new TO rank;

• 하나의 RENAME TABLES 명령문에 두 개의 RENAME 작업을 한꺼번에 실행하면 실제 애플리케이션에서는 "Table not found rank' 같은 상황을 발생시키지 않고 적용하는 것이 가능해짐

5.3 InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 mYsql에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있음
InnoDB는 레코드 기반의 잠금 방식 때문에 MyISAM보다 뛰어난 동시성 처리 제공
하지만 이원화된 잠금 처리 때문에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다로움

최근 버전에서 InnoDB의 트랜잭션, 잠금, 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 조회됨
information_schema 데이터베이스에 존재하는 INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS 테이블을 조인해서 조회 => 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있음

5.3.1 InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지락이나 테이블 락으로 레벨업되는 경우가 없음

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

5.3.1.1 레코드 락

• 레코드 자체만을 잠그는 것
• 다른 DBMS 레코드락과 동일한 역할을 하지만 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 차이가 있음
• 인덱스가 하나도 없는 테이블도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정함
• InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락, 갭 락 사용
• 프라이머리 키 or 유니크 인덱스에 의한 변경 작업에서는 갭에 대해 잠그지 않고 레코드 자체에 대해서만 락을 걸게 됨

5.3.1.2 갭 락

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

5.3.1.3 넥스트 키 락

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

5.3.1.4 자동 증가 락

MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공

• 이 속성이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우 저장되는 각 레코드는 중복되지 않고 저장도니 순서대로 증가하는 일련번호 값을 가져와야 함
• InnoDB 스토리지 엔진에 서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금 사용

5.3.2 인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 게 아니라 인덱스를 잠그는 방식으로 처리 => 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락 걸어야 함

(여기 이해가 잘 안 됨)

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

(여기도 일단 보류)

5.4 MySQL의 격리 수준

트랜잭션의 격리 수준(isolation level)

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

격리 수준 4가지

• READ UNCOMMITTED (DIRTY READ)
  -일반적인 데이터베이스에서는 거의 사용하지 않음
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE
  - 동시성이 중요한 데이터베이스에서는 거의 사용하지 않음

• 뒤로 갈수록 격리(고립) 수준 높아지고, 동시 처리 성능 떨어짐
• 격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 생각하는 사용자가 많지만 SERIALIZABLE 격리 수준이 아니면 크게 성능의 개선이나 저하가 발생하지는 않음

격리 수준에 따른 세 가지 부정합의 문제점

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

• InnoDB의 독특한 특성 때문에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않음
• 일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITTED와 REPEATABLE READ 중 하나를 사용함
• 오라클 같은 DBMS는 주로 READ COMMITTED 수준을 많이 사용
• MySQL에서는 REPEATABLE READ 주로 사용
• 여기 SQL 예제는 모두 AUTOCOMMIT이 OFF인 상태에서만 테스트 가능

5.4.1 READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보임

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

• 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED => 정합성에 문제가 많은 격리 수준이므로 MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용하자

(얘도 더 찾아봐야 할 듯)

5.4.2 READ COMMITTED

READ COMMITTED는 오라클 DBMS에서 기본으로 사용되는 격리 수준이고, 온라인 서비스에서 가장 많이 선택되는 격리 수준임

• 어떤 트랜잭션이 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문에 더티 리드는 발생하지 않음
• 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없음 <- 데이터는 언두 영역에서 백업된 레코드에서 가져옴
• 최종적으로 커밋하게 되면 그때부터는 다른 트랜잭션에서도 백업된 언두 레코드가 아닌 새롭게 변경된 값을 참조할 수 있게 됨

(여기 다 이해 안 됨)
NON-REPEATABLE READ: "REPEATABLE READ'가 불가능하다는 부정합의 문제가 존재

• 사용자 B가 똑같은 SELECT를 날렸는데 결과가 다른 경우 의미
• 일반적인 웹 프로그램에선 문제가 없을 수 있지만 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금적적인 처리와 연결되면 문제될 수 있음
• REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동함 => 이런 문제로 데이터의 정합성이 깨지고 애플리케이션에 버그가 발생하면 찾아내기 쉽지 않음

5.4.3 REPEATABLE READ

REPEATABLE READ는 MySQL InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준

• "NON-REPEATABLE READ" 부정합이 발생하지 않는 단계
• InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경함
  => 이 방식을 MVCC라고 하는데 REPEATABLE READ는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여 줄 수 있게 보장함
• 사실 READ COMMITTED도 MVCC를 이용해 COMMIT되기 전 데이터를 보여 주는데, 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있음

5.4.4 SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준인데 -> 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어짐
InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않아 굳이 SERIALIZABLE 사용할 필요가 없음