목차
· 트랜잭션
· MySQL 엔진의 잠금
· InnoDB 스토리지 엔진 잠금
· MySQL 격리 수준
트랜잭션
· 논리적인 작업 셋 자체가 100% 적용되거나, 아무것도 적용되지 않아야함을 보장해 주는 것이다.
- Partial update: 일부만 적용되는 현상. 트랜잭션이 적용되지 않을 때 발생함.
· 목적: 데이터의 정합성을 보장
MySQL에서의 트랜잭션
· InnoDB 스토리지엔진는 트랜잭션을 제공하고, MEMORY와 MyISAM 스토리지엔진은 제공하지 않는다.
- 즉, MyISAM에서는 Partial Update가 발생하므로, 데이터 클렌징 코드를 따로 작성해야한다.
주의사항
· 프로그램 코드에서 트랜잭션의 범위를 최소화해야한다. 특히, 네트워크 작업 등 긴 시간이 걸리는 작업은 제외해야한다.
- 데이터베이스 커넥션 개수는 제한적이다. 긴 트랜잭션으로 커넥션을 소유하는 시간이 길어지면, 다른 고객이 대기해야할 수도 있다.
- 메일 전송, FTP 파일 전송 등 외부 서버와 통신하는 작업을 트랜잭션에 포함하면,
외부 서버에 문제가 발생할 경우 DBMS 서버 또한 문제가 발생할 수 있다.
MySQL 엔진의 잠금
· 목적: 동시성 제어
· MySQL 엔진 레벨 잠금: 모든 스토리지 엔진에 영향
- 종류: 테이블락(테이블 데이터 동기화), 메타데이터락(테이블의 구조를 잠금),
네드임드락(사용자의 필요에 맞게 사용 가능)
· 스토리지 엔진 레벨 잠금: 스로리지 엔진간 상호 영향 x
- 종류: InnoDB의 레코드 기반 잠금
글로벌 락
· 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 건다.
- 한 세션에서 글로벌 락을 획득 후 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 문자을 실행할 경우
글로벌 락 해제시 까지 대기 상태로 남는다.
· FLUSH TABLES WITH READ ROCK 명령으로 획득할 수 있다.
· 영향이 미치는 범위는 MySQL 서버 전체로, MySQL에서 제공하는 잠금 중 가장 범위가 크다.
- 즉, 작업 대상 테이블과 데이터베이스가 달라도 동일하게 영향을 미친다.
· 용도: MyISAM 또는 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때 사용한다.
백업 락
· 조금 더 가벼운 글로벌 락
· 탄생 배경: InnoDB는 트랜잭션을 지원하므로, 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다.
때문에 조금 더 가벼운 글로벌 락의 필요성이 생겼고, MySQL 8.0 부터 도입되었다.
· 기능:
- 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자 인증 관련 정보를 변경할 수 없다.
1. 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
2. REPAIR TABLE, OPTIMIZE TABLE 명령
3. 사용자 관리 및 비밀번호 변경
- 일반적인 테이블의 데이터 변경은 허용된다.
· 용도: Xtrabackup, Enterprise Backup 등 백업 툴들의 안정적인 실행
테이블 락
· 개별 테이블 단위로 설정되는 잠금이다.
· 명시적 또는 묵시적으로 특정 테이블 락을 획득할 수 있다.
· 명시적: LOCK TABLES table_name [ READ | WRITE] 명령으로 획득, UNLOCK TABLES 명령으로 반납
- MyISAM, InnoDB 스토리지 엔진을 사용하는 테이블 모두에 적용 가능하다.
- 온라인 작업에 상당한 영향을 미치므로, 거의 사용하지 않는다.
· 묵시적: MyISAM, MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.
- 즉, 데이터가 변경되는 쿼리가 실행되는 동안 자동으로 획득되고, 쿼리가 완료된 후 자동 해제된다.
- InnoDB의 경우 테이블 락이 설정되지만 스토리지 엔진 차원에서 레코드 기반 잠금을 제공하므로,
대부분의 데이터 변경(DML) 쿼리에서는 무시되고, 스키마 변경 쿼리(DDL)의 경우에만 영향을 미친다.
DML과 DDL
네임드 락
· GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정한다.
· 특징: 사용자가 지정한 문자열(String)에 대해 획즉하고 반납하는 잠금이다.
- 테이블이나, 레코드 같은 데이터베이스 객체가 대상이 아니다.
· 사용예시:
1. 여러 클라이언트의 상호 동기화 처리:
데이터베이스 서버 1대에 5대의 웹서버가 접속하는 서비스에서 5대의 웹서버가 어떤 정보를 동기화해야 하는 요건
2. 복잡한 요건으로 레코드를 변경하는 트랜잭션:
배치 프로그램 등 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 된다.
이때, 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류하여 네임드 락을 걸고 쿼리를 실행하면 쉽게 문제를 해결할 수 있다.
· 사용방법: MySQL 8.0 버전부터 네임드 락을 중첩해서 사용하고, 현재 세션에서 획득한 네임드 락을 한 번에 해제할 수 있다.
-- // 작업 실행
SELECT GET_LOCK('mylock_1',10);
SELECT GET_LOCK('mylock_2',10);
-- // 락 해제
SELECT RELEASE_LOCK('mylock_2');
SELECT RELEASE_LOCK('mylock_1');
-- // 락 한 번에 해제
SELECT RELEASE_ALL_LOCKS();
메타데이터 락
· 데이터베이스 객체(테이블,뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금
- RENAM TABLE tab_a TO tab_b 같이 테이블 이름을 변경하는 경우 자동으로 획득 (명시적으로 획득 불가)
- RENAME TABLE 명령의 경우 원복 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다.
InnoDB 스토리지 엔진 잠금
· InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과 별개로 레코드 기반 잠금 방식을 제공한다.
- 장점: 1. 레코드 기반 잠금 방식 덕분에 MyISAM보다 뛰어난 동시성 처리를 제공한다.
2. 잠금 정보가 작은 공간으로 관리되어 락 에스컬케이션(레코드락->페이지락->테이블락)이 되지 않는다.
- 단점: 이원화된 잠금 처리 탓에 InnoDB에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기 까다롭다.
(단점은 과거 버전 한정. 최근에서 InnoDB의 중요도가 높아져서 모니터링 방법도 강화 되었다.)
· 잠금/트랜잭션 모니터링 방법:
- MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODBTRX, INNODB_LOCK_WAITS 테이블을 조인해서 조회하면, 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있다.
- 장시간 장금을 가지고 있는 클라이언트를 찾아 종료할 수도 있다.
- Performance Schema 스토리지 엔진을 이용하는 InnoDB 내부 잠금(세마포어)에 대한 모니터링 방법도 추가됐다.
· InnoDB에는 레코드 락과 레코드 사이를 잠그는 갭 락이 존재한다.
레코드 락
· 레코드 자체만을 잠그는 것을 레코드 락이라고 한다.
· MySQL은 특이하게 레코드 자체가 아닌 인덱스의 레코드를 잠근다.
- 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
· InnoDB의 보조 인덱스를 이용한 변경 작업은 대부분 넥스트 키락 또는 갭 락을 사용한다.
· InnoDB의 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭에 대해서는 잠그지 않고, 레코드 자체에만 락을 건다.
갭 락
· 레코드 자체가 아닌 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.
· 역할: 레코드와 레코드 사이 간격에 새로운 레코드가 insert되는 것을 제어한다.
넥스트 키 락
· 레코드 락와 갭 락을 합쳐 놓은 형태의 잠금이다.
· 갭락과 넥스트 키 락의 목적: 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것
· 문제점: 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다.
· 해결: 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락, 갭 락을 줄인다. (<-> STATEMENT 포맷 바이너리 로그)
TODO: Real MySQL p168 더 자세히 정리
자동 증가 락
· AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 insert되는 경우,
저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.
InnoDB에서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라는 테이블 수준 잠금을 사용한다.
· 자동 증가 값이 한 번 증가하면 절대 줄어들지 않는 이유는 자동 증가 락을 최소화하기 위해서다.
- INSERT 쿼리라 실패해도 한 번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남는다.
· INSERT, REPLACE 쿼리 문장처럼 새로운 레코드를 저장하는 쿼리에서만 필요하다.
· InnoDB의 다른 잠금(레코드 락 등)과 달리 트랜잭션과 관계 없이 INSERT, REPLACE 문장에서 AUTOINCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.
· AUTO_INCREMENT 칼럼에 명시적으로 값을 설정해도 자동 증가 락을 건다.
· AUTO_INCREMENT 락은 명시적으로 획득하고 해제할 수 없다.
· MySQL 5.1 이상부터 innodb_autoinc_lock_mode라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있다.
- innodb_autoinc_lock_mode = 0
MySQL 5.0과 동일한 잠금 방식, 모든 INSERT 문장은 자동 증가 락을 사용한다.
- innodb_autoinc_lock_mode = 1
INSERT되는 레코드 건수를 정확히 예측할 수 있으면, 자동 증가 락보다 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다.
INSERT ... SELECT처럼 건수를 예측할 수 없을 때는 자동 증가 락을 사용한다.
INSERT되는 레코드는 연속된 증가 값을 갖으므로, 해당 설정을 연속 모드(Concecutive Mode)라고도 한다.
대량 INSERT가 수행될 때 InnoDB는 여러 개의 자동 증가 값을 한 번에 할당 받아 INSERT되는 레코드에 사용한다.
MySQL 5.7 버전의 기본값이다.
단점: 한 번에 할당 받은 값들이 남아서 사용되지 못하면 폐기하므로 대량 INSERT 문장의 실행 후 INSERT되는 레코드의 자동 증가 값은 연속되지 않고 누락된 값이 발생할 수 있다.
- innodb_autoinc_lock_mode = 2
절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용한다.
하나의 INSERT 문장으로 INSERT되는 레코드라도 연속된 자동 증가 값을 보장하지 않는다.
따라서 인터리브드(Interleaved) 모드라고도 부른다.
MySQL 8.0 버전의 기본값이다. 바이너리 로그 포맷의 기본값이 ROW 포맷이 되었기 때문이다.
ROW 포맷이 아니라 STATEMENT 포맷의 바이너리 로그를 사용한다면 설정 값을 1로 변경해서 사용할 것을 권장한다.
장점: INSERT ... SELECT와 같은 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시성↑
단점: 이 설정에서 작동하는 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장하므로,
STATEMENT 포맷 바이너리 로그를 사용하여 복제하는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있다.
TODO: 래치(뮤텍스) 정리
인덱스와 잠금
· InnoDB의 잠금은 인덱스를 잠그는 방식이므로, 변경할 레코드를 찾기 위해 검색한 인덱스의 레코드에 모두 락을 걸어야 한다.
· 주의사항: UPDATE를 위한 적절한 인덱스가 준비돼 있어야 한다.
- 예시:
30만개의 레코드가 있는 사원 테이블에서 first_name='Georgi', last_name='Klassen'인 레코드를 검색한다고 가정하자.
이때 first_name은 인덱스, last_name은 인덱스라고 하면 first_name='Georgi'인 모든 레코드가 잠긴다.
만약에 테이블의 인덱스가 하나도 없다면, 30만개의 모든 레코드가 잠긴다.
레코드 수준의 잠근 확인 및 해제
· 레코드 수준 잠금의 문제점: 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 있어도 잘 발견되지 않는다.
· MySQL 5.1 부터 information_schema라는 DB에 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS
테이블을 통해 레코드 잠금과 잠금 대기에 대한 조회가 가능하다.
- 쿼리 하나만 실행하면 잠금과 잠금 대기를 볼 수 있다.
- 강제로 잠금을 해제하려면 KILL 명령을 이용해 MySQL 서버의 프로세스를 강제 종료한다.
· MySQL 8.0 부터는 information_schem의 정보들이 조금씩 Deprecated되고,
performance_schema의 data_locks와 data_lock_waits 테이블로 대체되고 있다.
· 자세한 조회 방법 Real MySQL 8.0 p173
MySQL의 격리 수준
· 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭셩에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.
· 수준은 크게 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 4가지로 나뉜다.
- 뒤로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 심해지며, 동시 처리 성능은 떨어진다.
- DIRTY READ라고도 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서 거의 사용 x
- 동시성이 중요한 데이터베이스에서 SERIALIZABLE은 거의 사용 x
- SERIALIZABLE 격리 수준이 아니면, 다른 격리 수준의 처리 성능 차이는 거의 없다.
· 격리 수준에 따라 세 가지 부정합이 발생할 수도 않을 수도 있다.
- DIRTY READ: 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데 다른 트랜잭션에서 볼 수 있는 현상
- NON_REPEATABLE READ: 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 서로 다른 결과가 발생하는 현상
- PHANTOM READ: 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보여다 안 보였다 하는 현상
| DIRTY READ | NON_REPEATABLE READ | PHANTOM READ | |
| READ UNCOMMITTED | o | o | o |
| READ COMMITTED | x | o | o |
| REPEATABLE READ | x | x | o (InnoDB는 없음) |
| SERIALIZABLE | x | x | x |
- SQL-92 또는 SQL-99 표준에 따르는 REPREATABLE READ 격리 수준에서 PHANTOM READ가 발생할 수 있지만,
InnoDB에서는 독특한 특성 때문에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않는다.
READ UNCOMMITTED
· 각 트랜잭션에서 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보이는 격리 수준
· 더티 리드가 발생한다.
- 사용자 A가 데이터를 INSERT 요청 후 커밋되기 전에, 사용자 B가 동일한 테이블에 대해 SELECT하고, 사용자 A의 데이터가 롤백될 때 발생한다.
· RDBMS 표준에서는 트랜잭션 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다.
- MySQL을 사용한다면, 최소 READ COMMITTED 이상의 격리 수준을 권장한다.
READ COMMITTED
· COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있는 격리 수준
· 더티 리드가 발생하지 않는다.
· NON-REPEATABLE READ가 발생한다.
- 사용자 A가 Begin 명령으로 트랜잭션을 시작한 후 첫 번째 SELECT를 요청하고, 사용자 B가 데이터를 변경하고 커밋한 후, 사용자 A가 두 번째로 SELECT를 요청했을 때, 첫 번째와 두 번쨰 SELECT 결과가 다를 때 발생한다.
· 오라클 DBMS에서 기본으로 사용되는 격리 수준
REPEATABLE READ
· NON-REPEATABLE READ 부정합이 발생하지 않는 격리 수준
- MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.
- MVCC: InnoDB 스토리지 엔진에서 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경하는 기술이다.
· READ COMMITTED도 MVCC를 통해 COMMIT되기 전의 데이터를 보여주지만, 두 격리 수준은 '언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 보여주느냐'가 다르다.
- 언두 영역의 백업된 데이터는 불필요하다고 판단하는 시점에 주기적으로 삭제되지만, REAPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터를 삭제할 수 없다.
· InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.
· 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.
· PHANTOM READ가 발생한다.
- 사용자 A가 INSET하는 중에, 사용자 B가 SELECT ... FOR UPDATE 쿼리로 동일한 테이블을 조회했을 때 발생할 수 있다.
- SELECT ... FOR UPDATE 쿼리를 수행할 떄, 레코드에 쓰기 잠금을 걸어야 하는데 언두 레코드는 잠금을 걸 수 없다.
SERIALIZABLE
· 가장 단순하지만, 가장 엄격한 격리 수준
· 동시 처리 성능이 가장 떨어진다.
· InnoDB 테이블에서 순수한 SELECT 작업(INSERT ... SELECT ... 또는 CREATE TABLE ... AS SELECT ...가 아닌)은 아무런 레코드 잠금을 설정하지 않고 실행된다.
- InnoDB 매뉴얼에서 자주 등장하는 "Non-locking consistent read, 잠금이 필요 없는 일관된 읽기"가 이것을 의미한다.
· 하지만 SERIALIZABLE 격리 수준에서는 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 동시에 다른 트랜잭션은 해당 레코드를 변경하지 못한다.
· PHANTOM READ가 발생하지 않는다.
· InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 "PHANTOM READ"가 발생하지 않기 때문에 SERIALIZABLE을 사용할 필요는 없다.
출처
Real MySQL 8.0 1권
'데이터베이스 > Real MySQL' 카테고리의 다른 글
| [Real MySQL 8.0] 4장 - 아키텍처: MySQL 엔진과 스토리지 엔진(InnoDB, MyISAM) (0) | 2022.01.08 |
|---|---|
| [Real MySQL 8.0] 6장 - 데이터 압축(페이지 압축과 테이블 압축) (0) | 2022.01.04 |
댓글