InnoDB
엔진만 트랜잭션 기능 제공### 이미 3 데이터가 들어간 테이블 fortice에 1, 2, 3을 추가
INSERT INTO fortice (id) VALUES (1),(2),(3);
MyISAM
: INSERT가 진행되다가 3 INSERT 시 중복 키로 종료. 3 이전의 입력인 1, 2가 추가되어 1, 2, 3 만 남아있음InnoDB
: INSERT가 Rollback되어 기존에 있던 3 데이터만 남아있음 # InnoDB 트랜잭션 코드 예시
CHECK PERMISSION;
try {
START TRANSACTION;
INSERT QUERY;
SELECT QUERY;
UPDATE QUERY;
COMMIT;
} catch(exception) {
ROLLBACK;
}
트랜잭션 시 AUTO_INCREMENT값
트랜잭션이 롤백 되어도 AUTO_INCREMENT값은 증가된 상태로 남는다. 10개 이상의 INSERT에 NOT_NULL인str
칼럼에 NULL을 입력해 의도적으로 롤백시켰다. 이후 정상적인 값을 INSERT 해보았다. AUTO_INCREMENT가 설정된 id값의 중간이 빈 것을 확인할 수 있다.
트랜잭션을 너무 큰 범위로 잡거나 외부 통신이 필요한 작업도 함께 있는 경우 문제가 발생할 수 있다. 프로그램이 커넥션을 가지고 있는 범위와 트랜잭션의 범위를 가능한 한 최소화하는 것이 좋다.
MySQL에서 사용하는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다. MySQL 엔진 레벨은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진간 상호 영향을 미치지 않는다.
잠금을 획득(설정)한다는 것이, 창고에 들어가지 못하게 외부에서 잠그는 것이 아니라, 내가 혼자 화장실을 사용하기 위해 문을 잠그는 것처럼 내가 점유를 하기위해 내부에서 잠그는 것입니다.
멀티 스레드 환경에서 공유자원에 접근할 때 순서를 보장해주기 위해 잠금을 건다. DBMS 환경에서는 위의 두 방식으로도 표현하며, 일반적으로 Read Lock, Write Lock으로 부른다.
FLUSH TABLES WITH READ LOCK
명령으로 획득할 수 있고, 가장 범위가 큰 잠금이다. 서버 전체에 영향을 미친다.
한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 대기 상태로 남는다.
백업 락
위 SQL문으로 실행된 글로벌 락은 READ LOCK을 모두 걸어버려서 모든 변경 작업을 멈추는데, InnoDB 스토리지 엔진의 트랜잭션 기능이 있기 때문에 굳이 변경을 막을 필요가 없다.이는 뒤에서 설명이 되는 Transaction ID를 이용한 읽기로 다른 수정사항에 영향을 미치지 않는 것을 의미하는 것 같다.위와 같은 상황에서 안정적인 백업을 위해 가벼운 글로벌 락인 백업 락을 도입했다.
- 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제 불가
- REPAIR TABLE과 OPTIMIZE TABLE 명령 불가
- 사용자 관리 및 비밀번호 변경 불가
- 일반적인 테이블의 데이터는 변경 가능
백업(Backup) vs 복제(Replica)
백업과 복제 비슷한 개념이지만 다르다. 백업은 복제를 하는 행위, 데이터를 복구하기 위한 목적에 의미가 있으며, 복제는 소스 서버의 데이터를 또 다른 서버인 레플리카 서버에 복사하여 이전시키는 의미이다.
백업은 백업을 참조해 복구하는 용도, 복제는 그 자체를 쓰는 것.(백업 락 도입 전)
레플리카 서버는 소스 서버에서 데이터를 복제한다. 소스 서버의 안정성을 위해 레플리카 서버에서 백업을 진행하는데, 백업이 LOCK을 걸어버려 복제(일종의 Write)를 못하게 되므로 백업 완료까지 지연된다. 백업 툴들은 글로벌 락 상황에도 쓰기가 가능해 앞의 상황 없이 복제 진행 중에도 일관된 백업이 가능하다. 그런데 이 툴들은 백업 도중 스키마 변경 시 실패한다. 따라서 이런 상황을 막기 위해 백업 락을 만들었다.
테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 방법, 묵시적 방법이 있다.
LOCK TABLES table_name [ READ | WRITE ]
명령으로 table_name
테이블을 잠글 수 있다.UNLOCK TABLES
명령으로 잠금을 하제할 수 있다.GET_LOCK()
함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다. 자주 사용되지 않는 방식이며, DB 서버 1대에 5대의 웹 서버가 접속해서 서비스 하는 상황처럼 동기화 과정이 중요한 경우 네임드 락을 이용해 쉽게 처리 가능하다.
문자열로 잠금을 건다?
처음에는 문자열로 잠금을 건다는 것이 이해가 안됐다. 해당 문자열을 잠가서 막아버리는 것일까 생각했는데, 단순하게 문자열 자체에 의미를 두어 같은 상황을 만들지 않는 용도로 생각하게 됐다.예를 들면 특정 유저가 동일 작업을 하는 것을 방지하기 위해 트랜잭션 시
GET_LOCK("user_name")
으로 LOCK을 걸어 동일한 user는 해당 작업을 잠금시켜 버린다.
SELECT GET_LOCK('lock', 2);
lock
이라는 문자열에 대해 잠금을 획득하고, 이미 잠금을 사용 중이면 2초 동안 대기한다.SELECT IS_FREE_LOCK('lock');
lock
이라는 문자열에 대해 잠금이 설정되어 있는지 확인SELECT RELEASE_LOCK('lock');
lock
이라는 문자열에 대해 잠금 해제메타데이터 락은 데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 자동으로 획득, 해제하는 잠금이다. RENAME TABLE tab_a TO tab_b
같이 테이블의 이름을 변경하는 경우 자동으로 두 테이블 모두 잠금을 설정한다.
InnoDB 스토리지 엔진은 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 훨씬 뛰어난 동시성 처리를 할 수 있다. 하지만 이원화된 잠금 처리 때문에 해당 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 까다롭다.
information_schema
데이터베이스의 INNODB_TRX
, INNODB_LOCKS
, INNODB_LOCK_WAITS
테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고, 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인 가능하다.
잠금 정보가 상당히 작은 공간으로 관리되어 레코드 락이 페이지 락, 테이블 락으로 레벨업 되는 경우(락 에스컬레이션)는 없다.
레코드 자체만을 잠그는 것이다. InnoDB 스토리지 엔진은 특이하게 레코드 자체가 아니라 인덱스의 레코드를 잠근다. 인덱스가 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 설정한다.
레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것이다. 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다. 갭 락 그 자체보다는 넥스트 키 락의 일부로 자주 사용된다.
레코드 락과 갭 락을 합쳐 놓은 형태의 잠금이다. STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 innodb_locks_unsafe_for_binlog
시스템 변수가 비활성화(0)되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.
InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어 내도록 보장하는 것이 주목적이다.
의외로 이로 인해 데드락이나 블로킹이 자주 발생한다. 가능하다면 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.
- STATEMENT
- 가장 오래된 포맷으로 데이터 변경에 사용되는 모든 쿼리를 쿼리대로 저장하는 방식 (5.7 기본)
- 한번에 많은 변경의 경우 효과적 (WHERE절로 수백개 데이터 UPDATE)
- ROW
- 변경 작업으로 변경된 모든 ROW의 정보를 기록하는 방식 (8.0 기본)
- 작은 변경이 여러번 일어나는 경우 효과적
- MIXED
- 두 방식을 혼합
AUTO_INCREMENT
가 설정된 칼럼이 사용된 테이블에 여러 레코드가 동시에 INSERT 되는 경우 저장되는 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다. 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.
트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져온느 순간만 락이 걸렸다가 즉시 해제된다.
innodb_autoinc_lock_mode
변수를 설정해 작동 방식을 설정한다. 0
값으로 모든 INSERT문에 사용, 1
값으로 서버가 레코드 건수 예측 가능 시 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(뮤택스)를 이용해 처리, 2
값으로 무조건 래치를 이용해 처리한다.
트랜잭션에서 보았듯이 쿼리가 실패해도 AUTO_INCREMENT
값은 줄어들지 않는다.
레코드 락에서 나오는 인덱스 잠금에 대해 알아본다.
UPDATE t SET c='col' WHERE A AND B
위 SQL문에서 A조건을 만족하는 레코드는 250개 B를 만족하는 조건은 1개라고 가정하자. 인덱스로 이용할 수 있는 칼럼이 A조건일 때, 해당 SQL문은 인덱스 리프 노드를 통해 250개의 레코드만 탐색하면서 B조건을 만족하는 칼럼을 찾아 총 250개의 레코드가 잠기게 된다. 만약 테이블에 인덱스가 하나도 없다면, UPDATE는 풀 스캔을 하면서 모든 레코드를 잠그게 된다. 이것이 인덱스 설계가 중요한 이유다.
레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다. MySQL 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해 보면 확인 가능하다.
잘 읽었어염 !!