5장. 트랜잭션과 잠금

5.1 트랜잭션

5.1.1 MySQL 에서의 트랜잭션

트랜잭션은 실행하는 쿼리 개수가 중요한 게 아니다.

논리적인 작업 셋 자체가 100% 적용될지 되지 말아야 할지를 보장하는 것이 중요하다.

 

• MyISAM : 트랜잭션 지원 X
  • Partial Update 가능 -> 실패에 대한 재처리가 복잡해지고, 테이블 데이터의 정합성 맞추기 어려워짐
• InnoDB : 트랜잭션 지원 O
  • 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다. -> 실패 처리가 간단함

 

5.1.2 주의사항

트랜잭션 범위는 최소한으로 설정해야 한다.

• DBMS 작업이 시작될 때 트랜잭션 시작
• IO. Network 통신 작업은 트랜잭션에서 반드시 제외
• 필요 작업들끼리 트랜잭션으로 묶고, 단순 조회 작업은 트랜잭션이 불필요함.

 

5.2 MySQL 엔진의 잠금

MySQL 엔진: MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분

• MySQL 엔진 레벨의 락 : 모든 스토리지 엔진에 영향을 줌
• 스토리지 엔진 레벨의 락 : 스토리지 엔진 간 상호 영향을 미치지 않음

 

MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블 구조를 잠그는 메타데이터 락, 사용자의 필요에 맞게 사용 가능한 네임드 락을 제공한다.

 

5.2.1 글로벌 락

명시적 획득 방법: FLUSH TABLES WITH READ LOCK

 

MySQL 에서 제공하는 잠금 가운데 가장 범위가 크다.

• SELECT 를 제외한 대부분의 DDL, DML 에 적용
• MySQL 서버 전체에 영향
• 작업 대상 테이블이나 데이터베이스가 달라도 동일한 영향

FLUSH TABLES WITH READ LOCK   명령은 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 건다.

 

글로벌 락은 MySQL 서버의 모든 테이블에 큰 영향을 미치기 때문에 웹 서비스용으로 사용되는 MySQL 서버에서는 가급적 사용하지 않는 것이 좋다.

 

MySQL 8.0 부터 InnoDB 가 기본 스토리지 엔진으로 채택되면서, 조금 더 가벼운 글로벌 락의 필요성이 생겼다.

따라서 Xtrabackup, Enterprise Backup 과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.

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

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

백업 락은 일반적인 테이블의 데이터 변경은 허용된다.

일반적으로 MySQL 서버는 소스 서버와 레플리카 서버로 구성되며 주로 레플리카 서버에서 백업이 실행된다.

백업이 글로벌 락을 획득하면 소스 서버 -> 레플리카 서버로의 복제는 백업 시간만큼 지연된다. 만약 레플리카 서버에서 백업 중 소스 서버에 문제가 생기면 레플리카 서버의 데이터가 최신 상태가 될 때 까지 멈춰야 할 수도 있다.

물론 백업 툴들은 모두 복제가 진행되는 상태에서도 일관된 백업을 만들 수 있는데, 만약 스키마 변경이 실행되면 백업은 실패하게 된다.

백업 락은 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 하기 위해 도입되었다.

 

5.2.2 테이블 락

개별 테이블 단위로 설정되는 잠금으로, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

명시적 획득 방법: LOCK TABLES table_name [READ | WRITE]

 

명시적으로 획득한 잠근은 UNLOCK TABLE 명령으로 잠금을 반납(해제)할 수 있다.

 

그러나 명시적 테이블 락도 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없다. 온라인 작업에 상당한 영향을 미치기 때문이다.

 

묵시적인 테이블 락은 MyISAM 이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후 즉시 잠금을 해제하는 형태로 사용된다.

즉, 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제된다.

 

InnoDB 테이블에서는 스토리지 엔진 차원에서 레코드 기반 잠금을 제공하기 때문에, 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다. 스키마 변경 쿼리(DDL) 의 경우에만 테이블 락이 걸리고, 대부분의 데이터 변경 쿼리(DML) 에서는 무시된다.

 

5.2.3 네임드 락

명시적 획득 방법: GET_LOCK()

• 대상이 테이블이나 레코드 또는 AUTO_INCREMENT 같은 데이터베이스 객체가 아니다.
• 사용자가 지정한 문자열에 대해 획득하고 반납(해제)하는 잠금

즉, 동일 데이터 변경 or 참조하는 프로그램끼리 특정 문자열 형태로 락을 거는 것

 

 

5.2.4 메타데이터 락

데이터베이스 객체(테이블, 뷰) 의 이름이나 구조 변경 시 획득하는 잠금 -> 명시적 획득/해제 불가

 

e.g. RENAME TABLE 명령
: 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금 설정

e.g. 테이블 구조 변경 요건 발생
MySQL 서버의 DDL 은 단일 스레드로 작동하기 때문에 상당히 많은 시간이 소모된다. 따라서 아래와 같이 수행
- 특정 범위의 데이터를 새 테이블에 PK 기준으로 여러 개의 스레드로 빠르게 옮기기
- 나머지 부분은 메타 데이터 락을 걸고 옮긴 후 테이블 변경 완료

"남은 데이터 복사" 하는 시간 동안은 테이블의 잠금으로 인해 INSERT 가 불가능 그래서 가능하면 미리 아주 최근 데이터까지 복사해둬야 잠금 시간을 최소화해서 서비스에 미치는 영향을 줄일 수 있다.

-> Q. 그럼 그냥 전부 스레드로 나눠서 빠르게 옮기면 되지 않나? 굳이 메타 데이터 락을 걸어야 하는가..

 

 

5.3 InnoDB 스토리지 엔진 잠금

• InnoDB 스토리지 엔진 내부에 레코드 기반 잠금방식 O
• 덕분에 MyISAM 보다 훨씬 뛰어난 동시성 처리 제공
• 이원화된 잠금 처리 탓에 잠금 정보에 대해 MySQL 명령으로 접근하기 까다로움

최근 버전에서는 InnoDB 의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됌

MySQL 서버의 Performance Schema 를 이용해 InnoDB 스토리지 엔진의 내부 잠금(세마포어) 모니터링도 가능해짐

 

5.3.1 InnoDB 스토리지 엔진의 잠금

잠금 정보가 상당히 작은 공간으로 관리되어 락 에스컬레이션 (레코드락 -> 테이블락 or 페이지락) 이 발생하지 않는다. 

InnoDB 스토리지 엔진에서는 레코드 락과 레코드 간 간격을 잠그는 갭 락이 있다.

 

5.3.1.1 레코드 락

레코드 자체만을 잠그는 것

InnoDB 스토리지 엔진은 레코드 자체가 아니라, 인덱스의 레코드를 잠근다.

인덱스가 없는 테이블은 내부에 자동 생성된 클러스터 인덱스를 이용해 잠근다.

• 보조 인덱스를 사용하는 변경 작업: 넥스트 키 락, 갭 락 사용
• 프라이머리 키 or 유니크 인덱스 사용하는 변경 작업 : 레코드 락 사용 (갭 락 X)

 

5.3.1.2 갭 락

레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미하며, 다른 DBMS 와 구별되는 점이다. 갭락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것

 

5.3.1.3 넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금.

innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화 되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.

 

InnoDB 의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행 시 소스 서버에서 만들어낸 결과와 동일하도록 보장하는 것이 주 목적이다.

 

그런데 의외로 넥스트 키락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다.

가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

 

5.3.1.4 자동 증가 락

자동 증가 숫자 값을 추출하기 위해 AUTO_INCREMENT 칼럼 속성을 제공 사용.

이 AUTO_INCREMENT  컬럼이 사용되는 테이블에 동시에 레코드가 여러 개 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 함.

이를 위해 내부적으로 AUTO_INCREMENT 락 이라고 하는 테이블 수준의 잠금을 사용한다.

 

INSERT, REPLACE 와 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며, UPDATE, DELETE 쿼리에서는 사용 X

또한 트랜잭션과 관계없이 INSERT, REPLACE 문장에서 자동 증가 숫자값을 가져오는 순간만 락이 걸렸다가 즉시 해제됨

해당 컬럼에 명시적으로 값을 설정하더라도 자동 증가 락이 걸린다.

 

AUTO_INCREMENT 락을 명시적으로 획득하고 해제하는 방법은 없다. 아주 짧은 시간동안 걸렸다가 해제되는 잠금이라서 대부분 문제가 되지 않는다.

 

MySQL 5.1 이상부터는 innodb_autoinc_lock_mode 라는 시스템 변수를 이용해 자등 증가 락의 작동 방식을 변경 가능

• innodb_autoinc_lock_mode = 0
  • 모든 INSERT 문장은 자동 증가 락 사용
• innodb_autoinc_lock_mode = 1
  • INSERT 되는 레코드 건수를 정확히 예측 가능하면 자동 증가 락을 사용하는 대신 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리 -> 아주 짧은 시간동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 잠금 해제.
  • 예측 불가능하면 모든 INSERT 문장에 자동 증가 락 사용. 
  • 대량 INSERT 수행 시 InnoDB 스토리지 엔진은 자동 증가 값을 여러 개를 한 번에 할당받아서 사용
  • 연속 모드: 할당받은 것 중에 남은 자동 증가 값은 폐기 처리 -> 연속되지 않은 값이 발생할 수 있음
• innodb_autoinc_lock_mode = 2
  • 인터리빙 모드: 자동 증가 락을 절대 걸지 않고 경량화된 래치(뮤텍스)만 사용 -> 연속된 자동 증가값을 보장 X
  • 대량 INSERT 시에도 INSERT 수행이 가능해서 동시 처리 성능이 좋다.

 

5.3.2 인덱스와 잠금

InnoDB 의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.

즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

 

UPDATE 문장을 위해 적절히 인덱스가 준비되어 있지 않다면 테이블 풀 스캔이 발생하여 동시성이 상당히 떨어진다.

(한 세션에서 UPDATE 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 함)

 

 이것이 MySQL 의 방식이며, MySQL 의 InnoDB 에서 인덱스 설계가 중요한 이유이다.

 

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

테이블 잠금은 잠금의 대상이 테이블 자체여서 쉽게 문제 원인 발견 / 해결 가능

그러나 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다. 

 

MySQL 8.0 부터는 Performance Schema 의 data_locks, data_lock_waits 테이블을 통해 잠금과 잠금 대기 순서를 확인할 수 있다.

 

5.4 MySQL 의 격리 수준

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

READ UNCOMMITTED -> READ COMMITTED -> REPEATABLE READ -> SERIALIZABLE

 

4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 높아지며 동시 처리 성능도 떨어진다.

그러나 SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.

-> READ COMMITTED 와 REPEATABLE READ 는 성능 차이가 별로 나지 않음

 

DBMS 별 주 사용 격리 수준

• MySQL : REPEATABLE READ
• Oracle : READ COMMITTED

격리 수준 별로 세 가지 부정합 문제가 발생할 수도 있고, 발생하지 않을 수 있다.

 

5.3.1 READ UNCOMMITED

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

 

Dirty Read : 어떤 트랜잭션에서 처리를 완료하지 않아도 다른 트랜잭션에서 볼 수 있는 현상

 

예를 들어 어떤 트랜잭션에서 INSERT 했다가 롤백하더라도 다른 트랜잭션이 그 틈에 조회를 할 수 있다

 

트랜잭션 격리 수준으로 인정하지 않을 정도로 정합성에 많은 격리 수준으로, MySQL 에서는 최소 READ COMMITTED 이상의 격리 수준을 권장한다.

 

5.3.2 READ COMMITED

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

어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT 이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있음

-> Dirty Read 가 발생하지 않음

최종적으로 사용자 A 가 변경된 내용을 커밋하면 그때부터 다른 트랜잭션에서도 백업된 언두 레코드("Lara") 가 아니라 새롭게 변경된 "Toto" 라는 값을 참조할 수 있게 된다.

 

그러나 해당 격리 수준에서는 NON-REPEATABLE 부정합이 발생할 수 있다.

 

즉, 사용자 B 가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져오지 못한다.

이는 트랜잭션 내/외부에서 실행하는 SELECT 의 결과가 동일해지는 결과가 된다.

 

이러한 부정합 현상은 일반적인 웹 프로그램에서는 크게 문제가 되지 않지만, 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 있다.

예를 들어 한 트랜잭션에서 입/출금 처리가 계속될 때, 다른 트랜잭션에서는 금액의 총합이 계속 달라지게 된다.

 

5.4.3 REPEATABLE READ

MySQL 의 InnoDB 스토리지 엔진에서 기본적으로 사용하는 격리 수준.

바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.

 

해당 격리 수준에서는 NON_REPEATABLE READ 부정합이 발생하지 않는다.

InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경한다 -> MVCC 방식

 

REPEATABLE READ 는 이 MVCC 를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션에서는 동일한 결과를 보여줄 수 있게 보장한다. (READ COMMITTED 도 MVCC 를 이용해 COMMIT 되기 전 데이터를 보여줌)

 

REPEATABLE READ 와 READ COMMITTED 의 차이는, 언두 영역에 백업된 레코드의 여러 버전 중 몇 번째 이전 버전까지 찾아들어가야 하느냐에 있다.

 

모든 InnoDB 의 트랜잭션은 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼있다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제된다. 

 

REPEATABLE READ 격리 수준에서는 MVCC 를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다. 즉, 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터는 보존돼야 한다.

 

 

사용자 B 가 트랜잭션을 시작하면 10번 이라는 트랜잭션 번호를 부여받았는데, 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.

 

만약 한 사용자가 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수 있다. 이렇게 언두에 백업된 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다.

 

 

그러나 해당 격리 수준에서는 새로운 레코드가 추가되는 경우 다시 SELECT 했을 때 새로운 레코드가 생겨나는 PHANTOM READ 라는 부정합이 발생할 수 있다. 

 

5.4.4 SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준으로, 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다

 

InnoDB 테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다.

-> Non-locking consistent read

 

하지만 트랜잭션 격리 수준이 SERIALIZABLE 로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.

즉, 한 트랜잭션이 읽고 쓰는 레코드를 다른 트랜잭션에서 절대 접근할 수 없다.

 

일반적인 DBMS 에서 발생하는 PHANTOM READ 부정합 문제가 발생하지 않는다.

 

그러나 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ 가 발생하지 않아서, 굳이 SERIALIZABLE 격리 수준을 사용할 필요는 없다.