[Real MySQL 8.0] 트랜잭션과 잠금
트랜잭션은 작업의 완전성을 보장해주는 것이다.
즉, 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못하는 경우 롤백시켜 작업의 일부만 적용되는 현상을 방지하기 위한 기술이다. 따라서 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이라고 볼 수 있다.
잠금(LOCK)은 트랜션과 비슷한 개념 같지만 사실 동시성을 제어하기 위한 기능이다.
잠금 없이 여러 커넥션이 하나의 데이터를 동시에 변경한다면 어떻게 될까?
그 데이터의 값은 예측할 수 없게 될 것이다. 한 시점에 하나의 커넥션만 데이터를 변경할 수 있도록 동시성을 제어해주는 역할이 잠금(LOCK)의 역할이라고 볼 수 있다.
MySQL에서의 트랜잭션
앞서 설명했듯, 트랜잭션은 작업 셋을 100%(Commit) 또는 0%(Rollback)로 만들어 작업 셋의 정합성을 보장해준다. 이는 DBMS의 아주 필수적인 기능으로 사용자로부터 데이터의 정합성을 아주 쉽게 맞출 수 있도록 해준다.
트랜잭션은 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 즉, 트랜잭션의 범위를 최소화하라는 의미다. 트랜잭션이 긴 경우 발생할 수 있는 문제점을 예제와 함께 살펴보자.
// 트랜잭션이 긴 경우
1) 처리 시작
=> 데이터베이스 커넥션 생성
=> 트랜잭션 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
5) 사용자의 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
9) 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료(COMMIT)
<= 데이터베이스 커넥션 반납
10) 처리 완료
위의 순서에서 실제 데이터 저장 작업이 일어나는 시점은 5번부터다.
일반적으로 DB 커넥션은 개수가 제한적이므로 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 커넥션의 개수는 줄어든다. 따라서 2~4번 작업이 아무리 빨리 처리된다 해도 DMBS 트랜잭션에 포함될 필요는 없다. 그리고 어느 순간 각 단위 프로그램에서 커넥션을 가지기 위해 대기해야 하는 상황이 생길 수 있어 좋지 않은 영향을 미칠 확률이 높아진다.
또한, 8번 작업에서 더 큰 위험이 존재한다.
메일 발송은 네트워크를 통해 원격 서버와 통신하므로 DBMS의 트랜잭션에서 제거하는 것이 좋다. 만약 네트워크 장애 등의 이유로 메일 서버가 통신 불가의 상태에 빠진다면 해당 트랜잭션은 실패할 것이며, 웹 서버뿐 아니라 DBMS 서버 장애로까지 이어질 수 있다.
위와 같이 긴 트랜잭션을 다음과 같이 최소화하고 나눠주면 위험도를 낮출 수 있다.
1) 처리 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 발생 여부확인
4) 첨부로업로드된 파일 확인 및 저장
=> 데이터베이스 커넥션 생성(또는 커넥션 풀에서 가져오기)
=> 트랜잭션 시작
5) 사용자의 입력 내용을 DBMS에 저장
6) 청부 파일 정보를 DBMS에 저장
<= 트랜잭션 종료(COMMIT)
7) 저장된내용 또는 기타 정보를 DBMS에서 조회
8) 게시물등록에 대한 알림 메일 발송
=> 트랜잭션 시작
9) 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료(COMMIT)
<= 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납)
10) 처리 완료
MySQL 엔진의 잠금
MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만 스토리지 엔진 레벨 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.
글로벌 락
MySQL에서 제공하는 잠금 가운데 범위가 가장 큰 잠금이다. SELECT를 제외한 대부분의 DDL이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 대기 상태로 남는다.
글로벌 락은 MySQL 서버 전체 테이블에 영향을 주는 만큼 웹 서비스에서 가급적 사용하지 않는 것이 좋다. MySQL 8.0부터는 조금 더 가벼운 글로벌 락의 필요성(InnoDB 기본 채택)이 생겨 백업 락이 도입되었다.
테이블 락
테이블 락은 개별 테이블 단위로 설정되는 잠금으로, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다. InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 테이블 락이 설정되지만, 대부분의 데이터 변경 쿼리에서는 무시되고 스키마를 변경하는 쿼리의 경우에만 영향을 미친다.
네임드 락
네임드 락은 GET_LOCK() 함수를 통해 임의의 문자열에 대해 잠금을 설정할 수 있다. 네임드 락의 특징은 테이블이나 레코드, 데이터베이스 객체가 아닌 사용자가 지정한 문자열에 대해 락을 획득하고 반납하는 잠금이라는 것이다.
예를 들어, 데이터베이스 서버 1대에 5대의 웹 서버가 접속해 서비스하는 상황에서 웹 서버가 특정 정보를 동기화해야 하는 경우처럼, 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락을 사용하면 쉽게 해결할 수 있다.
- // "mylock"이라는 문자열에 대해 잠금을 획득한다.
- // 이미장금을사용중이면2초동안만대기한다.(2초이후자동 잠금해제됨)
SELECT GET_L0CK('mylock', 2);
- // "mylock"이라는 문자열에 대해 잠금이 설정돼 있는지 확인한다.
SELECT IS_FREE_LOCK('mylock');
- // "mylock"이라는 문자열에 대해 획득했던 잠금을 반납(해제)한다.
SELECT RELEASE_LOCK('mylock' );
- // 3개 함수 모두 정상적으로 락을 획득하거나 해제한경우에는 1을,
- // 아니면 NULL이나 0을 반환한다.배치 프로그램처럼 많은 레코드를 변경하는 경우에 데드락이 발생할 확률이 높아지는데, 이때 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해 네임드 락을 걸고 쿼리를 실행하면 데드락을 아주 쉽게 해결할 수 있다.
메타데이터 락
데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금이다.
명시적으로 획득하거나 해제할 수 있는 잠금이 아니고 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.
테이블의 이름을 변경하는 경우 하나의 쿼리로 실행하는 것이 좋다.
2개의 쿼리로 나눠서 실행하는 경우 “Table not found” 오류를 발생시킬 수 있기 때문이다.
InnoDB 스토리지 엔진 잠금
InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 이 덕분에 뛰어난 동시성 처리를 제공할 수 있다.
InnoDB 스토리지 엔진은 잠금 정보가 상당히 작은 공간으로 관리 되기 때문에 락 에스컬레이션이 발생하지 않는다.
락 에스컬레이션
레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업 되는 경우처럼 많은 수의 작은 잠금을 더 적은 수의 큰 잠금으로 올리는 프로세스를 뜻한다. 락 에스컬레이션이 발생하면 동시성 경합 가능성이 올라가므로 동시성 처리 성능이 떨어진다.
다른 DBMS와 다르게 InnoDB 스토리지 엔진은 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이 존재한다.
레코드 락
- 레코드 자체만을 잠그는 락이라고 볼 수 있다.
- InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠그는 특징을 가지고 있다.
- 레코드 자체를 잠그느냐, 아니면 인덱스를 잠그느냐는 상당히 중요한 차이를 만들어 낸다. (인덱스와 잠금 참고)
갭 락
- 갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.
- 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 역할을 한다.
넥스트 키 락
- 레코드 락과 갭 락을 합쳐놓은 형태의 잠금이다.
- REAPEATABLE-READ 격리 수준을 사용해야 한다.
- 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.
- 데드락이나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생하므로 가능하면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.
자동 증가 락
- AUTO_INCREMENT가 사용된 테이블에 동시에 여러 레코드가 insert되는 경우, 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가지도록 해주는 테이블 수준의 잠금이다.
- INSERT나 REPLACE 쿼리와 같이 새로운 레코드를 저장하는 쿼리에만 사용된다.
- 자동 증가 락은 하나만 존재하기 때문에 동시에 INSERT가 일어나는 경우 하나의 쿼리는 해당 잠금을 기다려야 한다.
- 자동 증가 락은 명시적으로 명시적으로 해제하거나 획득할 수 없다.
인덱스와 잠금
InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 즉, 변경할 레코드를 찾을 때 검색한 인덱스의 레코드를 모두 잠궈야 한다. 이와 같은 특징 때문에 MySQL에서는 인덱스 설계가 굉장히 중요하다.
더 쉬운 이해를 위해 다음 예제를 살펴보자.
// 멤버로 담긴 ix_firstname이라는 인덱스가 준비돼 있다는 가정으로 진행
> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
+-----------+
| 256 |
+-----------+
> SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
+-----------+
| 1 |
+-----------+
> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';위의 쿼리를 실행하면 1개의 UPDATE 쿼리를 위해 몇 개의 레코드에 락을 걸어야 할까?
first_name에는 인덱스가 존재하지만 last_name에는 인덱스가 없기 때문에 first_name='Georgi' 인 레코드 253건이 모두 잠긴다.
만약 인덱스가 아예 존재하지 않는다면 풀 스캔이 일어나면서 1개의 UPDATE를 위해 모든 레코드가 잠기게 된다. MySQL의 InnoDB에서 인덱스 설계가 중요한 이유도 이 때문이다.
레코드 수준의 잠금 확인 및 해제
레코드 수준의 잠금은 테이블 수준 잠금보다 조금 더 복잡하고 문제의 원인을 발견하고 해결하기도 어렵다. MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능해져 쿼리 하나로 잠금과 잠금 대기를 바로 확인할 수 있게 되었다.
// 명령이 실행된 상태의 프로세스 목록을 조회
SHOW PROCESSLIST;
// performance_schema의 data_locks 테이블과 data_lock_waits 테이블을 조인해
// 잠금 대기 순서 조회
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER OOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_engine_transaction_id;
만약 특정 스레드가 어떤 잠금을 가지고 있는지 더 상세히 확인하고 싶다면 performance_schema의 data_locks 테이블이 가진 컬럼을 모두 살펴보면 된다.
SELECT * FROM performance_schema.data_locks\G
만약 특정 스레드가 잠금을 가진 상태에서 오랜 시간 멈춰있다면, 다음과 같이 특정 스레드를 강제 종료하여 잠금 경합을 끝낼 수 있다.
// KILL {특정 스레드번호}
KILL 17