프라이머리 키에 의한 클러스터링
InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다.
- 클러스터링: 서로 유사한 속성을 갖는 데이터를 같은 군집으로 묶어주는 작업을 의미한다.
프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.
MyISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않는다.
외래 키 지원
외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다.
InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크아는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재에 주의하는 것이 좋다.
서비스에 문제가 있어서 긴급하게 조치를 해야 하나, 외래 키가 복잡하게 얽혀있는 등 복잡한 상황인 경우 foreign_key_checks 시스템 변수를 OFF로 절정해 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.
SET foreign_key_checks=OFF; -- 외래 키 체크 일시적 해제
SET foregin_key_checks=ON; -- 외래 키 체크 주의!]
부모와 자식 테이블 간의 관계가 깨진 상태로 그대로 유지하는 것은 안된다!
반드시 일관성을 맞춰준 후 외래 키 체크 기능을 활성화 해야 한다.
foreign_key_checks가 비활성화되면 외래키 관계의 부모 테이블에 대한 작업(ON DELETE CASCADE와 ON UPDATE CASCADE옵션)도 무시하게 된다.
MVCC(Multi Version Concurrency Control)
레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이다.
MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.
InnoDB는 언두 로그를 이용해 이 기능을 구현한다.
INSERT 문이 실행되었을 때의 데이터베이스 상태
UPDATE 문이 실행되었을 때의 데이터베이스 상태
UPDATE member SET m_area='경기' WHERE m_id=12; 실행
UPDATE 문이 실행되면 커밋 실행 여부와 관계없이 InnoDB의 버퍼 풀은 새로운 값으로 업데이트 된다.
DB의 데이터가 UPDATE되고, 아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 다음 쿼리로 작업 중인 레코드를 조회하면 어디에 있는 데이터를 조회할까?
SELECT * FROM member WHERE m_id=12;
이 질문의 답은 MySQL 서버의 시스템 변수(transaction_isolation)에 설정된 격리 수준(Isolation level)에 따라 다르다.
[격리 수준]
- READ_UNCOMMITTED인 경우
InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다. - READ_COMMITTED 혹은 그 이상의 격리 수준(REPEATABLE_READ, SERIALIZABLE)인 경우
아직 커밋되지 않은 경우 InnoDB 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다.
UPDATE 쿼리가 실행되면 InnoDB 버퍼 풀은 즉시 새로운 데이터로 변경, 기존 데이터는 언두 영역으로 복사
이 상태에서 COMMIT 명령을 실행하면 InnoDB는 더 이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만들어 버린다.
하지만 롤백을 실행하면 InnoDB는 언두 영역에 있는 백업된 데이터를 InnoDB 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해 버린다.
커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는 것은 아니다. 이 언두 영역을 필요로 하는 트랜잭션이 더는 없을 때 비로소 삭제된다.
잠금 없는 일관된 읽기(Non-Locking Consistent Read)
InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업을 수행한다.
잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.
격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITTED나 READ_COMMITTED, REPEATABLE_READ 수준인 경우 INSERT와 연결되지 않은 순수한 읽기(SELECT) 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다.
특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았다 하더라도 이 변경 트랜잭션이 다른 사용자의 SELECT 작업을 방해하지 않는다.
이를 잠금 없는 일관된 읽기 라고 표현하며, InnoDB에서는 변경되기 전의 데이터를 읽기 위해 언두 로그를 사용한다.
자동 데드락 감지
InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프 형태(Wait-for List)로 관리한다.
InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제종료 한다.
이때 어느 트랜잭션을 강제 종료할 것인지 판단하는 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다. (서버 부하를 고려)
InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금은 볼 수 없어서 데드락 감지가 불확실할 수 있는데, innodb_table_locks 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지할 수 있게 된다.
일반적인 서비스에서 데드락 감지 스레드가 트랜잭션의 잠금 목록을 검사해서 데드락을 찾아내는 작업은 크게 부담되지 않는다.
하지만 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다.
innodb_deadlock_detect 시스템 변수를 OFF로 설정하면 데드락 감지 스레드는 더는 작동하지 않게 된다.
그러나 이 경우에 데드락이 발생해도 누군가가 중재를 하지 않기 때문에 무한정 대기하게 될 것이다.
따라서 데드락 감지 스레드가 부담되어 innodb_deadlock_detect를 OFF로 설정해 비활성화 하기보다는, innodb_lock_wait_timeout 을 설정하여 사용할 것을 권장한다.
innodb_lock_wait_timeout 은 초 단위로 설정할 수 있으며, 잠금을 설정한 시간동안 획득하지 못하면 쿼리는 실패하고 에러를 반환하게 된다.
언두 로그
InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다.
이렇게 백업된 데이터를 언두 로그라고 한다.
용도
- 트랜잭션 롤백 대비용
- 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공
언두 레코드 건수 확인하기
- MySQL 서버의 모든 버전에서 사용 가능한 명령
SHOW ENGINE INNODB STATUS \G - MySQL 8.0 버전만 가능
SELECT count FROM information_schema.innodb_metrics WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len';
