트랜잭션은 작업의 완전성을 보장해 주는 것이다.
•
즉 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 기능
•
잠금과 트랜잭션은 서로 비슷한 개념 같지만 사실 잠금은 동시성을 제어하기 위한 기능이고 트 랜잭션은 데이터의 정합성을 보장하기 위한 기능
•
잠금이 없다면 하나의 데이터를 여러 커넥션에서 동시에 변경할 수 있게 된다
•
결과적으로 해당 레코드의 값은 예측할 수 없는 상태가 됨
•
잠금은 자원을 순서대로 한 커넥션만 변경이 가능하도록 보장
•
격리 수준은 하나의 트랜잭션 내에서 또는 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미
5.1 트랜잭션
5.1.1 MySQL에서의 트랜잭션
트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념은 아니다.
•
트랜잭션은 논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야 함을 보장해 주는 것
•
MyISAM은 트랜잭션을 지원하지 않는다.
◦
MEMORY 스토리지 엔진을 사용하는 테이블도 MyISAM 테이블과 동일하게 작동한다.
◦
MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트(Partial Update)라고 표현
◦
이런 현상은 테이블 데이터의 정합성을 맞추는 데 상당히 어려운 문제를 만들어 냄
•
부분 업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요할 수 있음
•
비즈니스 로직 처리로 복잡한 로직에 데이터 클렌징 코드까지 넣어야 한다는 것은 정말 암담함
5.1.2 주의사항
트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.
•
이는 코드에서 트랜잭션의 범위를 최소화하라는 의미
1) 처리 시작
=> 데이터베이스 커넥션 생성
=> 트랜잭션 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 여부 확인
4) 첨부로 업로드 된 파일 확인 및 저장
5) 사용자의 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
9) 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
<= 데이터베이스 커넥션 반납
10) 처리 완료
YAML
복사
문제가 있는 트랜잭션 슈도코드
문제점
•
2/3/4번의 절차가 아무리 빨리 처리된다고 하더라도 트랜잭션에 포함시킬 필요는 없다.
◦
데이터베이스 커넥션은 개수가 제한적 때문에 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 개수는 줄어들 것
•
더 큰 위험 작업은 8번
◦
메일 전송/파일 전송 작업/네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 어떻게 해서든 트랜잭션 내에서 제거하는 것이 좋음
◦
프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버뿐 아니라 DBMS 서버까지 위험해지는 상황이 발생할 것
•
5/6번 작업은 반드시 하나의 트랜잭션으로 묶어야 함
◦
7번 작업은 저장된 데이터의 단순 확인 및 조회 때문에 트랜잭션 포함 X
◦
9번 작업은 조금 성격이 다르기 때문에 함께 묶지 않아도 무방
1) 처리 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용의 오류 여부 확인
4) 첨부로 업로드 된 파일 확인 및 저장
=> 데이터베이스 커넥션 생성
=> 트랜잭션 시작
5) 사용자의 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
=> 트랜잭션 시작
9) 알림 메일 발송 이력을 DBMS에 저장
<= 트랜잭션 종료 (COMMIT)
<= 데이터베이스 커넥션 반납
10) 처리 완료
YAML
복사
최적화된 슈도코드
5.2 MySQL 엔진의 잠금
MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.
•
MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분
◦
MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미침
◦
스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않음
•
MySQL은 여러 락을 지원함
◦
테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블의 구조를 잠그는 메타데이터 락
◦
사용자의 필요에 맞게 사용할 수 있는 네임드 락
5.2.1 글로벌 락
글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
•
한 세션에서 글로벌 락을 획득하면 다른 세션에서 조회를 제외한 대부분의 DDL 문장이나 DML 문장 실행이 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남음
•
글로벌 락의 영향은 MySQL 서버 전체
•
MyISAM/MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용
InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다.
•
InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겼다.
•
Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행 을 위해 백업 락이 도입
특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같은 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없음
•
데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
•
REPAIR TABLE과 OPTIMIZE TABLE 명령
•
사용자 관리 및 비밀번호 변경
•
하지만 백업 락은 일반적인 테이블의 데이터 변경은 허용된다.
•
주로 백업은 레플리카 서버에서 실행 됨
◦
하지만 FLUSH TABLES WITH READ LOCK 명령을 이용해 글로벌 락을 획득하면 복 제는 백업 시간만큼 지연될 수밖에 없다.
◦
XtraBackup이나 Enterprise Backup 툴이 실행되는 도중에 스키마 변경이 실행되면 백업은 실패하게 됨
◦
정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일 시 중지하는 역할을 한다.
5.2.2 테이블 락
테이블 락은 개별 테이블 단위로 설정되는 잠금
•
명시적 또는 묵시적으로 특정 테이 블의 락을 획득할 수 있다
•
LOCK TABLES table_name [ READ | WRITE ] 명령으로 특정 테이블의 락을 획득 가능
◦
MyISAM/InnoDB 스토리지 엔진 모두 사용 가능
◦
명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 반납 가능
•
테이블 락도 특별한 상황이 아니면 애플리케이션에 사용할 필요가 거의 없다.
◦
테이블을 잠그는 작업은 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치기 때문
•
묵시적인 테이블 락은 MyISAM/MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생
5.2.3 네임드 락
네임드 락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.
•
네임드 락은 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금 이다.
◦
네임드 락은 자주 사용되지 않는다.
◦
DB 서버 1대에 5대의 웹 서버가 접속해 서비스하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 여러 클라이언트가 상호 동기화를 처리해야 할 때 이용
◦
많은 레코드에 대해서 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용
◦
배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 함
◦
실행 시간을 분산하거나 프로그램의 코드를 수정해 데드락을 최소화 가능하지만 간단하지 않고 완전한 해결책이 아님
▪
이런 경우 네임드락을 걸고 쿼리를 실행하면 아주 간단히 해결 가능
5.2.4 메타데이터 락
메타데이터 락은 데이터베이스 객체의 이름이나 구조 를 변경하는 경우에 획득하는 잠금이다.
•
대표적으로 테이블이나 뷰 등
•
메타데이터 락은 명시적으로 획득하거나 해제할 수 있는 것이 아님
•
RENAME TABLE tab_a TO tab_b 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금
•
RENAME TABLE 명령의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정
•
실시간으로 테이블을 바꿔야 하는 요건이 배치 프로그램에서 자주 발생
mysql> RENAME TABLE rank TO rank_backup, rank_new TO rank;
YAML
복사
•
하나의 RENAME TABLE 명령문에 두 개의 RENAME 작업을 한꺼번에 실행하면 실제 애플리케이션 에서는 Table not found ‘rank’ 같은 상황을 발생시키지 않고 적용하는 것이 가능
◦
하나씩 하면 오류 발생
5.3 InnoDB 스토리지 엔진 잠금
•
InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기 반의 잠금 방식을 탑재하고 있다.
•
InnoDB는 레코드 기반의 잠금 방식 때문에 MyISAM보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다.
◦
But. 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭다.
◦
과거 버전은 lock_monitor/SHOW ENGINE INNODB STATUS 명령이 전부였음
▪
nodb_lock_monitor라는 이름의 InnoDB 테이블을 생성해서 InnoDB의 잠금 정보를 덤프하는 방법
◦
최근 버전은 information_schema DB에 존재하는 INNODB_ TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인
▪
현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인
5.3.1 InnoDB 스토리지 엔진
•
InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공
•
잠금 정보가 상당히 작은 공간으로 관 리되기 때문에 레코드 락이 페이지/테이블 락으로 레벨업되는 경우(락 에스컬레이션) X
•
InnoDB 스토리지 엔진에서는 레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재
5.3.1.1 레코드 락
•
레코드 자체만을 잠그는 것을 레코드 락이라고 함
•
InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다
•
인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정
•
InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 이어서 설명할 넥스트 키/갭 락을 사용
◦
PK/유니크 인덱스에 의한 변경 작업에서는 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 검
5.3.1.2 갭 락
•
다른 DBMS와의 또 다른 차이가 바로 갭 락이다.
•
갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미
•
갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것
•
넥스트 키 락의 일부로 자주 사용
5.3.1.3 넥스트 키 락
•
레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라 함
•
STATEMENT 포맷의 바이너리 로그를 사용하는 서버에서는 REPEATABLE READ 격리 수준을 사용
•
InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적
•
그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생
•
가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋음
5.3.1.4 자동 증가 락
•
MySQL에서는 자동 증가하는 숫자 값을 추출(채번)하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공 함
•
AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 함
•
InnoDB 스토리지 엔진에 서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용
•
AUTO_INCREMENT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요
•
다른 잠금과는 달리 AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값 을 가져오는 순간만 락이 걸렸다가 즉시 해제 됨
◦
두 개의 삽입 쿼리가 동시에 실행되는 경우 하나의 쿼리가 자동 증가락을 걸면 나머지 쿼리는 자동 증가 락을 기다려야 함
5.3.2 인덱스와 잠금
InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리됨
•
변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 함
mysql> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';
YAML
복사
만약 253개의 ROW를 가진 employees테이블에서 성과 이름이 일치하는 사람의 입사날짜를 바꾸는 쿼리를 실행하면 어떤 레코드에 락을 걸어야 하는가?
•
해당 조건에서 인덱스를 이용할 수 있는 조건은 first_ name='Georgi'이며, last_name 칼럼은 인덱스에 없기 때문에 first_name='Georgi'인 레코드 253건의 레 코드가 모두 잠김
업데이트를 위해 잠긴 레코드와 실제 업데이트된 레코드
•
이 테이블에 인덱스가 없을 경우 풀 스캔을 진행하며 테이블의 모든 ROW가 잠기게 된다.
•
그러니까 인덱스 잘 걸자!
5.3.3 레코드 수준의 잠금 확인 및 해제
InnoDB 스토리지 엔진을 사용하는 테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 조금 더 복잡
•
테이블 잠금에서는 문제 원인을 쉽게 발견하고 해결 가능
•
레코드 잠금의 경우 자주 사용되지 않는 레코드는 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않음
5.4 MySQL의 격리 수준
트랜잭션의 격리 수준이란?
•
여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것
•
격리 수준이 높아질 수록 각 트랜잭션 간의 데이터 격리 정도가 높아지며, 동시 처리 성능도 떨어지는 것이 일반적이라고 볼 수 있다.
•
사실 SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.
DIRTY READ | NON-REPEARABLE READ | PHANTOM READ | |
READ UNCOMMITTED | 발생 | 발생 | 발생 |
READ COMMITTED | 없음 | 발생 | 발생 |
REPEARABLE READ | 없음 | 없음 | 발생 (InnoDB는 없음) |
SERIALIZABLE | 없음 | 없음 | 없음 |
5.4.1 READ UNCOMMITTED
해당 격리 수준은 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.
•
어떤 트랜잭션에 COMMIT 하지 않은 작업을 다른 트랜잭션에서 보는 현상을 더티 리드라 함
•
더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED다.
•
더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래, 애플리케이션 개발자와 사용자 를 상당히 혼란스럽게 만들 것
•
트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다.
◦
MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장
5.4.1 READ COMMITTED
온라인 서비스에서 가장 많이 선택되는 격리 수준이다.
•
해당 레벨은 더티 리드같은 현상은 발생하지 않는다.
•
어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문
•
다른 트랜잭션에 의해 변경된 데이터를 읽어올 시 언두 영역에 백업된 레코드에서 가져온다.
•
NON-REPEATABLE READ라는 부정합의 문제가 있다.
◦
같은 트랜잭션 내에선 몇번을 호출해도 동일한 결과를 전달해야한다!
5.4.1 REPEARABLE READ
•
InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.
•
NON-REPEATABLE READ라는 부정합의 문제가 발생하지 않음
•
언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.
•
PHANTOM READ 현상이 발생한다.
◦
다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상
5.4.1 SERIALIZABLE
가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다.
•
그만큼 동시 처리 성능도 다른 트랜 잭션 격리 수준보다 떨어진다.
•
트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 업도 공유 잠금을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못 하게 된다.
•
InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않음
◦
때문에 굳이 SERIALIZABLE을 사용할 필요성은 없어 보인다.
