본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.
5.1 트랜잭션
5.1.1 MySQL에서의 트랜잭션
트랜잭션은 하나의 논리적인 작업 셋에 쿼리의 개수와 관계없이 논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야 함을 보장해 주는 것이다.
InnoDB와 달리 MyISAM, MEMORY 스토리지 엔진의 경우 트랜잭션을 지원하지 않는다. 따라서 쿼리 중 일부 오류가 발생하는 경우 이전 정상적으로 작동한 부분은 업데이트 되며, 오류가 발생한 부분만 업데이트 되지 않는 부분 업데이트(Partial Update) 현상이 발생하며, 이는 테이블 데이터 정합성을 맞추는데 상당히 어려운 문제를 만들어 낸다.
트랜잭션을 상당히 복잡하고 어려운 기능으로 생각할 수 있지만, 그만큼 애플리케이션 개발에서 고민해야 할 문제를 줄여주는 아주 필수적인 DBMS의 기능이다.
5.1.2 주의사항
트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하여 범위를 최소화하는 것이 좋다. 또한 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 한다. 이는 DBMS 서버 과부하 혹은 위험한 상태로 빠지게 할 수 있다.
5.2 MySQL 엔진의 잠금
MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 된다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않는다.
글로벌 락
글로벌 락(GLOBAL LOCK)은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다. 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.
InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 데이터 변경 작업을 멈출 필요는 없으며, MySQL 8.0부터는 InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겨 Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.
특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 되지만 일반적인 테이블의 데이터 변경은 허용된다.
- 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
REPAIR TABLE과OPTIMIZE TABLE명령- 사용자 관리 및 비밀번호 변경
5.2.2 테이블 락
테이블 락(Table Lock)은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다. 명시적으로는 LOCK TABLES table_name [ READ | WRITE ] 명령으로 특정 테이블의 락을 획득할 수 있으며, MyISAM, InnoDB 스토리지 엔진을 사용하는 테이블 모두 동일하게 설정할 수 있다. 또한 UNLOCK TABLES 명령으로 잠금을 반납할 수 있다. 명시적으로 테이블을 잠그는 작업을 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치므로 특별한 상황이 아닌 경우 거의 사용하지 않는다.
묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제된다. 하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다. 정확히는 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.
5.2.3 네임드 락
네임드 락(Named Lock)은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 네임드 락은 자주 사용되지는 않으며, 여러 클라이언트가 상호 동기화를 처리해야 하는 상황 혹은 많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다. MySQL 8.0 버전부터는 네임드 락을 중첩해서 사용할 수 있게 됐으며, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됐다.
5.2.4 메타데이터 락
메타데이터 락(Metadata Lock)은 데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 메타데이터 락은 명시적으로 획득하거나 해제하는 것이 아닌 RENAME TABLE tab_a TO tab_b 같이 테이블 이름을 변경하는 경우 자동으로 획득하는 잠금이다. RENAME TABLE 명령의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다. 이는 기존 데이터 구조 변경 등 실시간으로 테이블을 바꿔야 하는 경우에 사용된다.
5.3 InnoDB 스토리지 엔진 잠금
InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있어 MyISAM보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다. 이전에는 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보를 확인하기 어려웠으나 최근 버전에서는 MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 조인해서 조회해 InnoDB의 트랜잭션과 잠금, 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입되었으며 Performance Schema를 이용해 InnoDB 스토리지 엔진의 내부 잠금에 대한 모니터링 방법도 추가됐다.
5.3.1 InnoDB 스토리지 엔진의 잠금
InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 레벨업되는 경우(락 에스컬레이션)는 없다. 일반 상용 DBMS와 다르게 InnoDB 스토리지 엔진에서는 레코드 락뿐 아니라 레코드와 레코드 사이 간격을 잠그는 갭(GAP) 락이라는 것이 존재한다.
레코드 락
레코드 자체만을 잠그는 것을 레코드 락(Record lock)이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 하지만, InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 것이 중요한 차이점이다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생선된 클러스터 인덱스를 이용해 잠금을 생성한다.
갭 락
갭 락(Gap lock)은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다.
넥스트 키 락
레코드 락과 갭 락을 합쳐놓은 형태의 잠금을 넥스트 키 락(Next key lock)이라고 한다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이나 이로 인한 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.
자동 증가 락
MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공한다. 해당 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져와야 하는데 이를 위해 InnoDB 스토리지 엔진은 내부적으로 AUTO_INCREMENT 락(Auto increment lock)이라고 하는 테이블 수준의 잠금을 사용한다.
AUTO_INCREMENT락은 INSERT,REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서만 필요하다. InnoDB의 다른 잠금과는 달리 AUTO_INCREMENT락은 트랜잭션과 관계없이 INSERT와 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제되며, 테이블에 하나만 존재하는 락으로 두 개의 INSERT 쿼리가 동시에 실행되는 경우 하나의 쿼리가 AUTO_INCREMENT락을 걸면 나머지 쿼리는 이를 기다려야 한다.
5.3.2 인덱스와 잠금
InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리되므로 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.
만약 employees 테이블에 first_name 칼럼만 멤버로 담긴 ix_firstname 인덱스만 존재할 때 first_name='GEORGI'이고 last_name='Klassen'인 사원의 입사 일자를 오늘로 변경하는 쿼리를 실행한다고 가정하자.
mysql> UPDATE employees SET hire_date=NOW() WHERE firts_name='Georgi' AND last_name='Klassen';
위 이미지는 UPDATE 문장이 실행되었을 때 잠기는 레코드를 나타낸다. 해당 UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi'이며 last_name 칼럼은 인덱스에 없기 때문에 first_name='Georgi'인 레코드는 모두 잠기게 된다.
만약 위 예제의 인덱스가 하나도 없는 경우, 테이블을 풀 스캔하면서 UPDATE 작업을 하며 테이블에 있는 모든 레코드를 잠그게 된다. 이것이 MySQL의 방식이며, MySQL의 InnoDB에서 인덱스 설계가 중요한 이유이다.
5.3.3 레코드 수준의 잠금 확인 및 해제
InnoDB 스토리지 엔진을 사용하는 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다. 예전 버전의 MySQL 서버에서는 레코드 잠금에 대한 메타 정보를 제공하지 않아 더더욱 어려운 부분이다. 하지만 MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해 보면 잠금과 잠금 대기를 바로 확인할 수 있다. MySQL 8.0 버전에서는 performance_schema의 data_locks와 data_lock_waits 테이블을 통해 잠금과 잠금 대기 순서와 같은 메타 정보를 확인할 수 있다.
5.4 MySQL의 격리 수준
트랜잭션의 격리 수준(isolation level)이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다. 격리 수준은 4가지가 존재하며, 격리 정도가 높아질 수록 동시 처리 성능도 떨어지는 것이 일반적이다.
데이터베이스의 격리 수준을 이야기하면 항상 함께 언급되는 세 가지 부정합의 문제점이 있다. 이 세 가지 부정합의 문제는 격리 수준의 레벨에 따라 발생할 수도 있고 발생하지 않을 수도 있다.
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
|---|---|---|---|
| READ UNCOMMITTED | 발생 | 발생 | 발생 |
| READ COMMITTED | 없음 | 발생 | 발생 |
| REPEATABLE READ | 없음 | 없음 | 발생(InnoDB는 없음) |
| SERIALIZABLE | 없음 | 없음 | 없음 |
5.4.1 READ UNCOMMITTED
READ UNCOMMITTED 격리 수준에서는 아래 이미지와 같이 각 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다. 아래 이미지는 다른 트랜잭션이 사용자 B가 실행하는 SELECT 쿼리의 결과에 어떤 영향을 미치는지를 보여주는 예제다.
위 이미지미에서 사용자 A는 emp_no가 500000이고 first_name이 "Lara"인 새로운 사원을 INSERT한다. 사용자 B가 변경된 내용을 커밋하기 전에 emp_no=500000인 사원을 검색하고 있으며 이를 조회할 수 있다. 만약 사용자 A가 처리 도중 롤백한다고 하더라도 사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 계속 처리할 것이다.
이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty Read)라 하고, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED다. 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래해 혼란스럽게 하고, RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준으로 MySQL을 사용한다면 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다.
5.4.2 READ COMMITTED
READ COMMITTED는 오라클 DBMS에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준으로, COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있으므로 더티 리드 같은 현상은 발생하지 않는다.
사용자 A는 emp_no=500000인 사원의 first_name을 "Lara"에서 "Toto"로 변경했는데, 이때 새로운 값인 "Toto"는 employees 테이블에 즉시 기록되고 이전 값인 "Lara"는 언두 영역으로 백업된다. 사용자 A가 커밋을 수행하기 전 사용자 B가 emp_no=500000인 사원을 SELECT하면 조회된 결과는 언두 영역에 백업된 레코드의 first_name 칼럼의 값인 "Lara"이다.
READ COMMITTED 격리 수준에서는 어떤 트랜잭션에 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없기 때문이며, 커밋이 된 이후에는 다른 트랜잭션에서도 백업된 언두 레코드가 아닌 새롭게 변경된 "Toto"라는 값을 참조할 수 있게 된다.
READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ 부정합 문제가 존재하며, 아래 이미지는 해당 문제가 왜 발생하며 어떤 문제를 만들어낼 수 있는지 보여준다.
처음 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하고 first_name이 "Toto"인 사용자를 검색했는데, 일치하는 결과가 없었다. 하지만 사용자 A가 사원 번호가 500000인 사원이 이름을 "Toto"로 변경하고 커밋을 실행한 뒤 사용자 B가 똑같은 쿼리로 조회하면 이번에는 결과가 1건이 조회된다. 이는 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋난다.
READ COMMITTED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없으나 REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동한다. 즉 트랜잭션을 시작한 상태에서 온종일 동일하 쿼리를 반복해서 실행해봐도 동일한 결과만 보게 된다.
5.4.3 REPEATABLE READ
REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다. REPEATABLE READ는 InnoDB가 제공하는 방식인 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다. READ COMMITTED도 MVCC를 이용해 COMMIT되기 전의 데이터를 보여주지만 둘의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.
모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다. REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다. 모든 언두 데이터가 필요한 것은 아니며, 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.
위 이미지는 REPEATABLE READ 격리 수준이 작동하는 방식을 보여준다. 사용자 A의 트랜잭션 번호는 12, 사용자 B의 트랜잭션 번호는 10이었다. 이때 사용자 A는 사원의 이름을 "Toto"로 변경하고 커밋을 수행했으나 사용자 B가 emp_no=500000인 사원을 A 트랜잭션 변경 전후 각각 한 번씩 SELECT했는데 결과는 항상 "Lara"라는 값을 가져온다. 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하며 10번이라는 부여받은 트랜잭션 내부에서 실행되는 모든 SELECT쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경하느 것만 보게 된다.
이미지에서는 언두 영역에 백업된 데이터가 하나지만 이는 얼마든지 늘어날 수 있으며 한 사용자가 BEGIN으로 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수도 있어 MySQL 서버의 처리 성능이 저하될 수 있다.
위 이미지는 REPEATABLE READ 격리 수준에서 발생할 수 있는 부정합 문제를 보여준다. 사용자 B는 BEGIN 명령으로 트랜잭션을 시작한 후 SELECT를 수행하므로 두 번의 SELECT 쿼리 결과는 동일해야 하지만 서로 다르게 조회된다. 이처럼 트랜잭션에서 수행된 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ(PHANTOM ROW)라고 한다. SELECT ... FOR UPDATE 쿼리는 SELECT하는 쿼리에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없어 SELECT ... FOR UPDATE나 SELECT ... LOCK IN SHAPE MODE로 조회되는 쿼리는 언두 영역의 변경 전 데이터를 가져오는 것이 아닌 현재 레코드의 값을 가져오게 된다.
5.4.4 SERIALIZABLE
SERIALIZABLE 격리 수준은 가장 단순하면서 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 격리 수준보다 떨어진다. InnoDB 테이블에서 기본적으로 순수한 SELECT 작업(INSERT ... SELECT ... 또는 CREATE TABLE ... AS SELECT ...가 아닌)은 아무런 레코드 잠금도 설정하지 않고 실행되나 SERIALIZABLE 격리 수준에서는 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 한다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다. SERIALIZABLE 격리 수준에서는 PHANTOM READ문제가 발생하지는 않으나, InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ 문제가 발생하지 않으므로 굳이 사용할 필요성은 없다.
